Get the fundamental model building principles right and designing your dashboard becomes much easier.
Dashboards are made up of cells and objects, all of which have different purposes. Some of those cells contain raw data taken from other systems, some cells are purely for presentation and other cells are for calculations used in charts and summaries. From my experience, it is best we don’t mix these purposes together. Actually, any time we create a model in Excel we want to separate these purposes.
Separating data, calculation and presentation
The is one of the most important factors in creating a good dashboard. It keeps the workbook tidy and most of all easy to update.
Try to think of your dashboard as 3 layers.
The bottom layer is the data layer, this contains data from other systems and sources. You do not want to make any changes or adjustments to the data in this layer. If necessary you can append columns to the data layer to create additional data which doesn’t already exist (for example, if you need to put each product into a category using a basic VLOOKUP.
All the inputs for your dashboard go into the data layer.
One of the key things is to not bloat the data layer with unnecessary data. Just because there are 5 years of data doesn’t mean you have use it all. Just use the data you need. The more data you have the bigger the file size, and the slower the calculation time.
Try to get the data in a data layout (i.e. without unnecessary formatting and spacing), rather than a taking a presentation/report as the source, this will make the calculations in the next layer much easier.
The calculation layer sits on top of the data layer. In this layer you manipulate, analyze and summarize the data. This is where you take the data and turn it into information.
There is no new data entered here, instead everything is linked to the data layer.
This layer consists of the formulas, pivot tables or any other method necessary to get the data into a format where it is ready for presentation.
In this layer the information is shown in all its glory. There is no new data inserted in this layer and there are no complex calculations. It purely displays the information which has been prepared on the calculation layer or linked directly to the data layer.
There may be some basic formulas or linking to pull the information from the calculation layer.
Where to put these layers?
These layers can be within the same worksheet or across multiple worksheets. And you’re not limited to a single sheet for each layer. If we have 4 different inputs then we can have 4 different worksheets. The concept of the layer is simply a way to group the different purposes.
Document the model
When returning to a dashboard it’s not always easy to remember what the purpose of each section was, especially within the calculation layer. It’s a good idea to insert comments into your worksheet, or even have an additional sheet detailing what each area is for.
Here are some other tips to help you:
- Include a worksheet of constant values to be used in e.g. tax rates, discount rates, period end dates (this is part of the data layer).
- Never include a value inside a formula, always link to other cells. The only exception is information which never changes (e.g. days in the week is 7, it is always 7).
- Never enter the same information twice – link the cells to the original data sheets or constants.
- Consider formula speed to optimize calculations and data layout.
- Use absolute referencing (the $ symbols) correctly so that consistent formulas can be copied across and down.
- Use Named Ranges to make models easier to understand.