My Kiva

Skydrive




Contact
Ed Ferrero

Pivot Table Tutorial - Part 2

Back

b) Adding Row Fields

Now lets try to make this a bit more complicated. What if you wanted to see what type of product was sold in each State? Select any cell within the existing Pivot Table, then select the menu item "Data - Pivot Tables…" you will go back to the Wizard at step 3. Click "Layout&qupt; and you will see the layout dialog again&H133;

Drag the Type button to the Row area under the State button. The Wizard will look like this;

Pivot Table Wizard - Step3

Now Click Ok, then click Finish. Your Pivot Table now contains both State and Type Fields, and the data is nicely subtotaled.

Altered Pivot Table

Save the workbook now. You can practice with the Pivot Table Wizard and then open the workbook again to continue this lesson.

Note that from Excel 2000 onwards, it is possible to drag pivot fields on the worksheet. Excel 2003 has a "Show Field List" button on the Pivot Table Toolbar - you can drag fields from this list onto the Pivot Table without going into the wizard.

There are limits on how many fields you can drag to the Row and Column areas. Experiment with the Pivot Table and see if you can find these limits.

c) Once you have a table, pivot it!

Select the Pivot Table you have created in EG1.XLS. You can Click-and-Drag Fields to another part of the Pivot Table without entering the Wizard. Try dragging the Type Field to the left of the State Field.

Drag Field Example

This is what you should get;

Drag Field Result

d) Creating another Pivot Table based on an existing Table

Now, let say we want to see both Sales and Margin side by side so that we can compare them. We could create another pivot table. It is better to base the new Pivot Table on the existing Pivot Table, because this way Excel uses the same memory cache for both Pivot Tables thus making sure you do not run out of memory on large Tables.

Select a blank cell on your worksheet and start the Pivot Table Wizard. On the first screen, check the option button that says the data resides in Another Pivot Table;

Pivot Table Wizard - Step3

Then Click Next. The Wizard asks you to select an existing Pivot Table, select PivotTable1 and Click the Next button;

Pivot Table Wizard - Step3

You should now see Step 3 of the Wizard. Click "Layout" and create a Pivot Table as shown below. Click Ok and Finish to exit the wizard.

Pivot Table Wizard - Step3

Now you should have two Pivot Tables on your worksheet. Using formulas, it should be a simple matter to create a table showing Price per Unit = Sales / Quantity.

The completed workbook is given in EG2.XLS.

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.


Continue...

Back