Working with Pivot Tables - Exercise

Contact Us or call 1-877-932-8228
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:PivotTable
  3. Insert a pivot chart that looks like the chart below:Chart
  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:PicotTable
  5. Insert Slicers to analyze sales by salesperson and product in addition to industry.Insert Slicers

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):PivotTable Command
    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:Create PivotTable Dialog Box
    4. In the PivotTable Field List, drag and drop Company into the Row Labels box and Sale Amount into the Values box:PivotTable Field List
    5. Click Sum of Sale Amount and select Value Field Settings:Sum of Sale Amount
    6. Click Number Format:Number Format
    7. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:Format Cells Dialog Box
    8. In the Value Field Settings dialog box, click OK:Value Field Settings
  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:PivotChart Command
    3. In the Insert Chart dialog box, select Pie charts, select the first pie chart, and then click OK:Pie Chart
  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):PivotTable Command
    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:Create PivotTable Dialog Box
    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:VALUES Box
    5. Click Sum of Sale Amount and select Value Field Settings:Value Field Settings
    6. Click Number Format:Number Format
    7. In the Format Cells dialog box, select Currency, change the number of decimal places to "0", and click OK:Format Cells Dialog Box
    8. In the Value Field Settings dialog box, click OK:Value Field Settings Dialog Box
  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):Insert Slicer Command
    3. In the Insert Slicers dialog box, check the Product and Salesperson check boxes and click OK:Insert Slicers Dialog Box
    4. To use the Slicer, simply select one of the fields in either Slicer to filter your data by that field.
Next