Pivot tables can be used to summarize data in interesting ways. In this example, I start with selected data from the Invoices table of Northwind.mdb. The table contains 2,156 rows, each representing one invoice. The selected data is the Customer ID, City, and Extended Price. I want to see the sales by City.
Select a cell in the table and choose PivotTable and PivotChart Report… from the Data menu
Step1 allows you to select where to get the data and what kind of report to produce. We’ll be using an Excel table and producing a Pivot Table.
With a cell from the table selected, Excel will guess what the whole table is. You need to have a header row for your data, the value of which you will see in later steps. If Excel doesn’t guess correctly, you can change the range in this step. You can also select the entire range before starting the wizard to help Excel guess correctly.
The final step of the wizard allows you to define where the table will be created, either on an existing sheet or a new sheet. For this example, I’ll choose New Sheet.
This step also provides a Layout button and an Options button. You can define the rows and columns of your table using the Layout button or just click finish and define them on the worksheet. I’ll choose to define them directly on the new table as opposed to using the Layout button. By clicking finish, Excel creates the table and displays the skeleton.
The Pivot Table toolbar shows all the fields from the Excel list, from which you can define the layout of the table. The three fields Customer ID, City, and Extended Price are shown as buttons on the toolbar.
To define the layout, drag the City button to the area that says “Drop Row Fields Here” and drag the Extended Price button to the “Drop Data Items Here” area.
Excel guesses what you want to do with the Data Items. In this case, since Extended Price is a number, it guesses that you want to Sum them and creates the Sum of Extended Price data item. If you don’t like Excel’s guess, you can change it by right clicking on Sum of Extended Price and choosing Field Settings.
Change the Summarize By listbox to Count
to see the number of invoices by city instead of the total sales.
That’s the basics of creating a simple pivot table. There are a lot more options and much more that you can do with pivot tables, which I will cover in future posts.