Using Pivot Tables for Excel Dashboard Analysis
Print View | Html View
Written by: Gary Stewart
Total views: 0 |
Word Count: 364 |
Date: Tue, 30 Jun 2009 |
43 comments
Many excel dashboards are designed to be static. A data model is about providing a structure to how data is added and then updated on the excel dashboard without requiring excessive amount of time and energy. Any executive dashboard, whether it be static or interactive should not require excessive maintenance. If your data model is on track, then you should be doing less manual tasks in excel, not more. That is the main goal when developing your excel dashboards performance report.
Dashboards can be resource intensive. At the start you need to identify information needs of those who will receive your dashboard. Then you will need to create a mock-up and a list of processes starting from the data source to the finished report.
key questions
Adding extra interactivity to your excel dashboard, such as being able to select a certain region or being able to drill down can make a huge difference to your dashboards perceived usability. The possibilities are endless and will depend on the level of aggregation within your source data.
Breaking up the data model into parts
One important concept in data modelling is the separation of data, analysis and dashboard presentation. There should be at least three worksheets in your data model. Sometimes there is the formatting stage, which involves adding extra fields to your data list in order to get it ready for analysis.
Most reports usually have all three parts built into one spreadsheet. The first is the data itself which you would need to convert into an excel list for use in pivottables. The second is the analysis of the data that will be used for the dashboard report which is the final part. Using this type of structure should allow much more flexibility.
The analysis phase of an excel dashboard consists of pivot tables and formulas that pull data from the data list in order to create staging tables for charts and other dashboard components. However, fixing your table structure in your analysis phase, will lock you into specific analysis/calculations.
About the Author
.gif)
.gif)
