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.
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.
Things to consider
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 process
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.
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.
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.
Things to consider
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 process
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.
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 dashboards using VBA and pivottables offer so much more power. You could have your executive dashboard created for you. New 50 page guide and template to create your own.
0 commentaires:
Enregistrer un commentaire