Excel Dashboards and Reports: Create Your First Pivot Table
A pivot table in Excel allows you to spend less time maintaining your dashboards and reports and more time doing other useful things. No utility in the whole of Excel allows you to achieve this efficient data model better than a pivot table.
Use the following steps to walk through the creation of your first pivot table:
Click any single cell inside yourdata source — the table you’ll use to feed the pivot table.
Select the Insert tab on the Ribbon. Here, find the PivotTable icon, as shown in this figure. Choose PivotTable from the drop-down list beneath the icon.
This activates the Create PivotTable dialog box, as shown in the this figure. As you can see, this dialog box asks you to specify the location of your source data and the place you want to put the pivot table.
Notice that in the Create PivotTable dialog box, Excel makes an attempt to fill in the range of your data for you. In most cases, Excel gets this right. However, always make sure the correct range is selected.
You will also note in the figure that the default location for a new pivot table is New Worksheet. This means your pivot table will be placed in a new worksheet within the current workbook. You can change this by selecting the Existing Worksheet option and specifying the worksheet where you want the pivot table placed.
At this point, you have an empty pivot table report on a new worksheet. Next to the empty pivot table, you see the PivotTable Fields dialog box, shown in this figure.
The idea here is to add the fields you need into the pivot table by using the four drop zones found in the PivotTable Field List — Filters, Columns, Rows and Values. Pleasantly enough, these drop zones correspond to the four areas of the pivot table you review at the beginning of this chapter.
If clicking the pivot table doesn’t activate the PivotTable Fields dialog box, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Field List.
Now, before you go wild and start dropping fields into the various drop zones, it’s important that you ask yourself two questions; “What am I measuring?” and “How do I want to see it?” The answers to these questions give you some guidance when determining which fields go where.
For your first pivot table report, you want to measure the dollar sales by market. This automatically tells you that you will need to work with the Sales Amount field and the Market field.
How do you want to see that? You want markets to go down the left side of the report and sales amount to be calculated next to each market. Remembering the four areas of the pivot table, you’ll need to add the Market field to the Rows drop zone, and the Sales Amount field to the Values drop zone.
Select the Market check box in the list, as demonstrated in this figure.
Now that you have regions in your pivot table, it’s time to add the dollar sales.
Select the Sales Amount check box in the list, as demonstrated in this figure.
Selecting a check box that is non-numeric (text or date) automatically places that field into the row area of the pivot table. Selecting a check box that is numeric automatically places that field in the values area of the pivot table.
What happens if you need fields in the other areas of the pivot table? Well, instead of selecting the field’s check box, you can drag any field directly to the different drop zones.
One more thing: When you add fields to the drop zones, you may find it difficult to see all the fields in each drop zone. You can expand the PivotTable Fields dialog box by clicking and dragging the borders of the dialog box.