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.
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.
Breaking up the data model into parts
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.
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.
Alternatively, if we only use pivot tables at the analysis stage, then you are not locked into the same table structure. The advantage of using pivot tables for the analysis is that you can change it depending on the requirements of your dashboard.
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.
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.
Breaking up the data model into parts
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.
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.
Alternatively, if we only use pivot tables at the analysis stage, then you are not locked into the same table structure. The advantage of using pivot tables for the analysis is that you can change it depending on the requirements of your dashboard.
About the Author:
Forget static reports. excel dashboards using VBA and pivottables offer so much more power. You could have your executive dashboard created for you. Read the new guide.
No comments:
Post a Comment