Excel dashboards are a great visual way to report performance results or any other kind of data. The main problem occurs when there is a need to regularly update data. The aim here is to automate as much as possible. This is were pivots have the greatest use in excel.
A data model is how data is added and updated every week or month and how it links to your dashboard reports. It is the design of how you capture data to finally reporting it without it causing problems. The more automated this is the better. In effect, you are designing out manual tasks in the development of an excel data model.
PivotTable reporting is a useful feature for working with data in list form. It allows you to summarize large amounts of information and to manipulate row and column headings so that you can view the data in different ways. They allow interactive analysis of data. Hence the name \’pivot\’ table. This means the view of your data changes.
When tables update either manual by clicking the refresh button or via VBA, the dynamic link to the data source should update dynamically and then update the various charts and tables they are linked to. Once again, this updating should be automatic.
Using the \’getpivotdata\’ formula is a great function for capturing specific data dynamically into your dashboards. If it is turned off then all you get is a cell link instead.
The tables can be organized into fields and items. Fields are rows or columns of data. Data items exist within a field. For example, if you want to summarize data by month, each month becomes a data field. Data items are the numbers you want to summarize or analyze. This is a better way to view data as it prevents hard coding of formulas that might have to be edited or changed. To reduce errors you can let pivot tables do any calculations rather than having calculated figures within your list.
If you are looking for a great guide on how to use Excel to create excel dashboards and visual performance reports, then take a look at this product on executive dashboards. You will never use excel the same way again!
