The whole idea of a Dashboard is to provide a graphical interpretation of the most relevant data, such as a balanced scorecard. Moreover, the updating of new data into the dashboard every week or month should be automated as much as possible.

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.

Pivot tables form the basis of a data model in excel. They are perfect for the job. They can handle a lot of data and can be auto refreshed via VBA. The problem here is how they capture new data from an excel list or database.

By dividing your data and analysis processes, the updating of new data within the pivots should subsequently appear on all dashboards. This is done by linking your pivot data to other cells rather than direct linking from tables to dashboard component.

This dynamic linking to dashboard charts and tables can be done using specific formulas in excel. Some of these are unheard of and not used very often.

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.