Creating a pivot table is a great start. Even more power comes from changing your view. Pivot tables allow you to "pivot" the results quickly and see things from a number of different views.
You can follow along with the Pivot Tables Pivots table to do the exercises.
Here was the pivot table that we finished up with last time.
Let's shift to look at results by rep by month. We want to swap the rep and product fields. One way is to do it in two steps. First drag the rep field to the right of the product field. While we could not show it here, (in later versions of Excel) you will see a little icon of a pivot table and the column bar highlighted in the icon. That tells you it will be placed as a row field down a column.
The second step is to drag the product up to the page field section at the top. Now you can see results by rep by month.
Here is an alternate way to change your view. This time, we will use the pivot table wizard, which you access from the menu after selecting Data.
This pulls up the final dialog box in the wizard which we saw when setting up the table. Click the Layout button. Below you see the layout dialog box with the four pieces of the pivot table- data fields, row fields, column fields and page fields.
The nice thing about using the layout dialog box is that you can make multiple changes before you update the table. If you have any other calculations going on in your worksheet, this could save you from having some formulas or cells written over. We prefer working with the layout view.
To make our change, we would do it in two parts. First drag the product field to the column field.
The drag the month field to the page field section.
Then click OK to return to the pivot table wizard.
Click Finish to get back to the pivot table, now with the results by product by rep.
Now you have seen two ways to modify your pivot table by using the Pivot Table Wizard or by moving fields within the pivot table. Use whichever way you prefer. To switch between the different methods, click on a data cell in the pivot table and then right click to pull up the following menu of choices (Excel XP version shown):
Click Wizard then if you prefer to use the layout. Click Show Field_List if you want to show the list of fields to drag other fields in.
Use the sample pivot table and play around with different looks. Image how difficult it would be to calculate these numbers with formulas. You can get great insights with pivot tables.