Microsoft Excel – Lesson 10

Activity Overview

Pivot tables provide you the ability to to extract significance from a large detailed data set. The following activity illustrates how to drill into data to summarize, analyze, explore, and present your data.

New Skill

In this activity, you will practice how to:

1. Create a pivot table using a large data set.
2. Drag PivotTable fields.
3. Sort PivotTable data.
4. Filter PivotTable  data.
5. Change PivotTable Summary calculations.

Lesson

1. Open the PivotData.xls data file.
2. Insert a Pivot Table:
   a. Click on any single cell inside of the data set.
   b. Open the Insert tab, click PivotTable.
   c. A new dialog box will appear automatically selecting the data for you. The default location for a new pivot table is New Worksheet.
   d. Click OK.
   e. A new worksheet will open with the newly created Pivot Table.
3. The new worksheet will display the PivotTable field list. To get the total amount exported of each product, drag the following fields into the different areas below:
   a. Product Field to the Row Labels area.
   b. Amount Field to the Values area.
   c. Country Field to the Report Filter area.
4. Sorting your data – to get “Beans” at the top of the list, sort the pivot table:
   a. Click any cell inside the Total column.
   b. Notice PivotTable Tools contextual tab has been activated in the Ribbon.
   c. On the Options tab, click the Sort Largest to Smallest button (ZA).
   d. Your data has been sorted with “Beans” displayed at the top of your list.
5. You can also filter your data to find details about specific fields. In this example, a Country field has been included in the Report Filter area. This will allow you to filter this pivot table by Country. For example, which products do we export the most to Belgium?
   a. Click the filter drop-down labeled Country and select Belgium from the list.
   b. Results – Blackberries are the main export product to Belgium.
   c. Note: you can use the standard filter (triangle next to ‘Row Labels’) to only show the totals of specific products or other data in the Row Label field.
6. By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps to change the summary calculation:
   a. Click any cell inside the Total column.
   b. Right click and click on Value Field Settings…
   c. Choose the type of calculation you want to use. For example, click Count.
   d. Click OK.
   e. Results – 4 of the orders to Belgium were ‘Orange’ orders.
7. Save your document.
8. Optional: Print a copy for review.