My Kiva


Ed Ferrero

Pivot Table Tutorial - Part 3


2) A more complex example

a) Using Page Fields

Open the workbook EG3.XLS. Select a cell anywhere in the Pivot Table. Then bring up the Pivot Table Wizard by using menu item "Data - Pivot Table…" or by Clicking the Pivot Table Wizard toolbar button. Click the "Layout" button, then drag the Type Field button from the Row area to the Page area;

Pivot Table Wizard - Step3

Click the ok button and then the Finish button. Your Pivot Table now looks like this;

Pivot Results

Notice that sales for all product types are shown, and that a small arrow is placed beside the (All) label next to the Type field. Click on this small arrow, a dropdown box appears that allows you to select tne of the Type values (either Red or White). Try this and watch the data change to show just White or Red products.

b) Hiding data

In the preceding example, the State WA does not have a good sales record. We would like to show what total sales would look like without this State. Let's do this now.

Click on the small arrow next to the State field. A drop-down list will appear which looks like the one below. Uncheck WA from the list items and Click the Ok button. The Pivot Field now shows all States except WA.

Pivot Table - Field Options

c) Grouping data

It is a little difficult to pick trends over the twelve month data period. Select the Month field by Clicking on the Month label on the Pivot Table. Then use menu item Data-Group and Outline-Group… (or Click on the Group toolbar button). A dialog will ask how you wish to group the Month Field, select the Quarters option and Click Finish.

Pivot Table - Grouping

Now your Pivot Table should look like this;

Pivot Results

d) Field calculations

Select a cell anywhere on the Pivot Table and call the Pivot Table Wizard. Then go to the Layout dialog. Sum of Sales is shown in the Data area, double-click this. Notice that you can show the sum of sales or the count of the number of sales during a period, or several other calculations. For now, leave the selection at Sum. Click on the Options button, then select % of row from the Show data as: dropdown list.

Pivot Table - Field Calcs

Now your Pivot Table shows sales in each quarter as a percentage of the full year. You can easily see, for example, that sales of SA blends have steadily improved over the year. (See figure below).

Pivot Results

The EG4.XLS file contains the completed Pivot Table at the end of this lesson (in Sheet2). Sheet3 also contains a Pivot Table with multiple Page Fields. Open this to see how you might use Pivot Tables to home in on only the data you are interested in.

Back to top