Dashboards are completely different from your conventional spreadsheet report. They require a different way of thinking about Excel and the communication of data. This includes the set-up of an excel dashboard. The design of the data model should build in flexibility and design out as many manual excel tasks as possible. This is your main goal when developing your Excel dashboard reports.

As part of the initial development of any dashboard, the performance indicators that are to be used should be defined, including how they are collected and the calculations involved.

Interactivity

Other questions should emerge, such as how will the data be fed into the dashboard? What analysis needs to be performed? Do I have the technical knowledge? Some of the answers will emerge when investigating user requirements and in creating a mock-up of your dashboard. There are a lot of cool features in excel and techniques to help develop a sound data model that will reduce the number of excel tasks involved.

Data, analysis and dashboard

The data model should involve three phases and at least three worksheets. In other words, you are breaking the whole thing down to separate components of data, analysis and dashboard presentation. Each component could take up several worksheets.

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.