Excel Skills: Pivot Tables 1. Download and read the article "Make Excel an Instant Know-It-All, 2. Create a Spreadsheet containing the data in Exhibit 1 and name it Input" a. To create a Pivot Table in Excel 2010 and later. Order Country Salesperson Date Order Amount USA Osaki 14-Jul $ 4,725.00 USA Jones 23-Jul $ 9,921.30 UK Brown 25-Jul $ 6,475.40 USA David 1-Aug $ 6,375.00 USA David 5-Aug $ 4,806.99 UK Smith 7-Aug $ 4,960.90 USA Smith 9-Oct $ 19,000.00 USA Osaki 22-Oct $ 10,164.80 UK Brown 29-Oct $ 4,451.70 UK Jones 27-Nov $ 4,529.80 USA David 18-Dec $ 6,635.27 b. Put your curser in the middle of the data, click on the insert tab, then PivotTable and then OK 3. Create a pivot table that shows sales by month for each salesperson. a. Put Salesperson in the Column Labels b. Put Order Date in the Row labels c. Put Order Amount in the Values and Sum the Order Amount d. Format the sales amounts as currency and no decimal places in your spreadsheet e. If you are using EXCELL 2007 group by month by right clicking the Order Date column, then on the Group and then month f. Name the spreadsheet this pivot table is on Sales by Month 4. Go back to the original spreadsheet containing the data in Exhibit 1 a. Add a column to the right of the Order Amount and Name it Group b. Add a one in this column for Brown and David and a 2 for all others salesperson c. Create a pivot table (see 2 above) that shows Order Amount for each group d. Put Group as a Row Labels e. Sum the Order Amount as the value in the center of the table f. Format the sales amounts as currency and two decimal places in your spreadsheet g. Name the spreadsheet this pivot table is on Sales by Group" 5. Go back to the original data and create a pivot table that shows the COUNT of sales by salesperson (column labels) and country (row labels). Name this spreadsheet Count of Sales a. To do count, click on the drop down box in the Values box, Value Field Settings and Count