Excel Dashboards created using a pivot table have the advantage of being low maintenance. The excel dashboard created using this method will typically have three layers – the raw data, the pivot table and the final dashboard presentation. This loosely follows the Model–View–Controller pattern found in software programming with the pivot table acting as the controller.
Creating an Excel Dashboard using pivot tables
As we mentioned earlier, to create a complete dashboard, the following three pieces need to be put together. Let’s take a simple example of a company which sells products from three product categories – Electronics, Home Décor and Industrial. The company operates in 12 regions with each region employing the services of nine sales manager – three each representing each product category. In our case, we want to build a dashboard which presents the following points (for each region, for the most recent month) to the reader:
1. Sales target
2. Volume of Sales generated
3. % of sales target met
4. Total Profit generated for all the regions
5. The average sales volume per sales manager AND
6. The average profit generated per sales manager
Assume that the reader is interested in looking at this information so that he/she can compare the performance of a particular product category in a particular region viz-aviz other regions.
Raw Data for the Excel Dashboard
The raw data for the scenario highlighted above looks something like this.
Creating a Pivot Table for the Dashboard
Now let’s create a pivot table using this underlying raw data. (Refer to this article to know how to create a pivot table.)
The pivot table shown above has been created using Excel 2007. If you create the same in Excel 2003 or earlier versions, the orientation would be slightly different.
Presentation of Excel Dashboard using tables and charts
The pivot table is useful but it is not something that we would like to show to the user. So let’s build an intermediate table that can show pretty much the same information but is easier to work with. (Agreed that the pivot table can be augmented using calculated fields but this technique is simpler and is very useful for versions earlier than Excel 2007)
In the example shown below one such intermediate table is shown.
In the first column of this table, we insert the list of regions in each row. To make the values in the list of regions dynamic, we can have them refer to the cells within the pivot table so that whenever the user makes changes to the pivot table, these fields acquire new values. Once we have the first column in place, all we need to do is to use the GETPIVOTDATA formula to fetch values pertaining to each of these items. The GETPIVOTDATA formula can provide us with different values based on the values specified to the parameters. Hence , we can use a single formula to provide us different values. (Ex. GETPIVOTDATA(“Region”, B26) can provide values pertaining to the region mentioned in cell B26. We can now change the values in cell B26 to “Alabama”, “Ohio” or any other valid value). As we move to the columns towards the right, all we need to do is to keep changing the first argument (Ex. Replace “Sales” by “Target” so that while in the first column for formula is GETPIVOTDATA(“Sales”, B26) in the second, it is GETPIVOTDATA(“Target”, B26)).
To avoid showing the pivot table to the user, we can hide the relevant rows. So in our case, barring the drop-down for the division, all the other rows have been hidden (or grouped). We now have a 2-level cascade – the first one where the list of regions updates itself as the product drop-down is changed and the second where the change in the list regions triggers the GETPIVOTDATA to fetch updated values from the pivot table. The drop-down list that we use for this purpose need not be a part of the pivot table – a simple drop-down using data validation or a combo box can also serve the purpose equally well. If you feel like going the extra step, feel free to insert a few charts as well. Read this article to know how to create drop down lists using data validation in Excel.
This post highlighted a simple process for creating an excel dashboard using pivot tables. In practice, you would likely need to work with much larger and complex data sets. However the basic rules covered in this article will likely remain the same.
Excel 2007 and Later versions : Download a sample worksheet with a example of Excel Dashboard in 2007 and Later versions here or click on the button below:
Excel 2003 and Earlier versions : Download a sample worksheet with a example of Excel Dashboard in 2003 and Earlier versions here or click on the button below: