Excel Dashboards and Reports: Rearrange Your 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. Now here’s the wonderful thing about pivot tables: You can add as many layers of analysis as made possible by the fields in your source data table.
Say that you want to show the dollar sales each market earned by business segment. Because your pivot table already contains the Market and Sales Amount fields, all you have to add is the Business Segment field.
So, simply click anywhere on your pivot table to reactivate the PivotTable Fields dialog box and then select the Business Segment check box. This figure illustrates what your pivot table should look like now.
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.
Imagine that your manager says that this layout doesn’t work for him. He wants to see business segments going across the top of the pivot table report. No problem. Simply drag the Business Segment field from the Rows drop zone to the Columns drop zone. As you can see in this figure, this instantly restructures the pivot table to his specifications.
Often, you’re asked to produce reports for one particular region, market, product, and so on. Instead of working hours and hours building separate reports for every possible analysis scenario, you can leverage pivot tables to help create multiple views of the same data. For example, you can do so by creating a region filter in your pivot table.
Click anywhere on your pivot table to reactivate the PivotTable Fields dialog box and then drag the Region field to the Filters drop zone. This adds a drop-down selector to your pivot table, shown in this figure. You can then use this selector to analyze one particular region at a time.