Working with Pivot Tables - Exercise

Working with Pivot Tables - Exercise

Working with Pivot Tables

Duration: 15 to 25 minutes.

In this exercise, you will practice using pivot tables, pivot charts, and Slicers.

1. Open PivotTables.xlsx from your Excel2013.3/Exercises folder.
2. Insert a pivot table in a new worksheet showing total sales by company. Format the numbers as Currency and show zero decimal places. The pivot table should look like the following image:
3. Insert a pivot chart that looks like the chart below:
4. Insert a pivot table in a new worksheet showing total sales in each calendar year by industry. Format the numbers as Currency and show zero decimal places. The pivot table should look like the following image:
5. Insert Slicers to analyze sales by salesperson and product in addition to industry.

Solution:

1. To insert a pivot table showing total sales by company and to format the numbers as Currency and show zero decimal places:
1. Select any cell in the table (cells A1:H62).
2. On the INSERT tab, in the Tables group, click the PivotTable command (and then select PivotTable in Excel 2007/2010):
3. In the Create PivotTable dialog box, verify that Excel has selected the correct range, select where you want the pivot table to show up (select New Worksheet), and then click OK:
4. In the PivotTable Field List, drag and drop Company into the Row Labels box and Sale Amount into the Values box:
5. Click Sum of Sale Amount and select Value Field Settings:
6. Click Number Format:
7. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:
8. In the Value Field Settings dialog box, click OK:
2. To insert the pivot chart showing total sales by company:
1. Select any cell in the pivot table.
2. On the PIVOTTABLE TOOLS ANALYZE tab (PivotTable Tools Options tab in Excel 2007/2010), in the Tools group, click the PivotChart command:
3. In the Insert Chart dialog box, select Pie charts, select the first pie chart, and then click OK:
3. To insert a pivot table showing total sales in each calendar year by industry and to format the numbers as Currency and show zero decimal places:
1. Select the sheet named "Data" and then select any cell in the table (cells A1:H62).
2. On the INSERT tab, in the Tables group, click the PivotTable command (and then select PivotTable in Excel 2007/2010):
3. In the Create PivotTable dialog box, verify that Excel has selected the correct range, select where you want the pivot table to show up (select New Worksheet), and click OK:
4. In the PivotTable Field List, drag and drop Industry into the ROW box (Row Labels box in Excel 2007/2010), Calendar Year into the COLUMN box (Column Labels box in Excel 2007/2010), and Sale Amount into the VALUES box:
5. Click Sum of Sale Amount and select Value Field Settings:
6. Click Number Format:
7. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:
8. In the Value Field Settings dialog box, click OK:
4. To insert Slicers to analyze sales by salesperson and product in addition to industry:
1. Select any cell in the pivot table.
2. On the PIVOTTABLE TOOLS ANALYZE tab (PivotTable Tools Options tab in Excel 2007/2010), in the Filter group (Sort & Filter group in Excel 2007/2010), click the Insert Slicer command (and select Insert Slicer in Excel 2007/2010):
3. In the Insert Slicers dialog box, check the Product and Salesperson check boxes and click OK:
4. To use the Slicer, simply select one of the fields in either Slicer to filter your data by that field.