Excel Dashboards and Reports: The 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.
A pivot table is a robust tool in Excel that allows you to create a n interactive view of your dataset, commonly referred to as a pivot table report. With a pivot table report, you can quickly and easily categorize your data into groups, summarize large amounts of data into meaningful analyses, and interactively perform a wide variety of calculations.
Pivot tables get their name from the way they allow you to drag and drop fields within the pivot table report to dynamically change (or pivot) perspective and give you an entirely new analysis using the same data source.
Think of a pivot table as an object you can point at your dataset. When you look at your dataset through a pivot table, you can see your data from different perspectives. The dataset itself doesn’t change, and it’s not connected to the pivot table. The pivot table is simply a tool you’re using to dynamically change analyses, apply varying calculations, and interactively drill down to the detail records.
The reason a pivot table is so well suited for dashboarding and reporting is that you can refresh the analyses shown through your pivot table simply by updating the dataset it’s pointed to. This allows you to set up your analysis and presentation layers only one time; then, to refresh your reporting mechanism, all you have to do is press a button.