An interactive excel dashboard can add so much more data to be displayed on a dashboard. With a sound data model structure, data can be added and then updated on the excel dashboard without needing too much time and energy. Any executive dashboard, whether it be static or interactive should not require excessive maintenance. You key aim in setting up a dashboard is to reduce manual tasks not create more.
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.
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.
Data, analysis and dashboard
The data model needs to be broken up in order to build in flexibility into the way you present your dashboard reports and how the data is updated or added to it. Source data should be structured appropriately and presented as a flat data file.
Pivot table reports are powerful Excel tools in analysing and presenting data and are used as the main mechanism for summarising data list and dynamic updating of dashboard reports. Once updated, results are calculated automatically and presented in the dashboards. Calculated fields are usually done in the pivot table rather than in the data list. Pivot tables are a good starting point for both analysis and presentation in the development of a dashboard, but they do have formatting limitations. For example, when you refresh a pivot table, any formatting of column widths are usually lost, which means they expand to fit the field headings. This is not good if you have an interactive dashboard. There are ways of maintaining formatting within the pivot table's toolbar menu under "select", but it is not the easiest to use. The easiest way is to either to use a macro or use a separate presentation worksheet for your excel dashboard instead.
Documenting your model is critical for business continuity and as a note to the analyst as well. There can be a lot of things to remember when working with a lot of data, worksheets and dashboard components, including the processes involved.
About the Author:
Better performance reports with excel dashboard using VBA and pivottables offer so much more power. You could have your executive dashboard created for you. Read the new guide.
Get all the information and photos:: http://coringa.info/education/data-model-for-excel-executive-dashboard
0 comments:
Post a Comment