Excel Dashboard Templates Update Your Dashboard

Excel Dashboard Templates Update Your Dashboard

When you update your dashboard, you may have to extend the formulas in your calculated columns down to the last row of your database (if you havent made it a table).

They provide an overview of a situation or activity to understand the key results, trends and attention points.

If you happen to build Excel dashboard, this post will provide you with valuable design principles and many tips and tricks to make absolutely impressive dashboards.

Managers and decision-makers need to be able to understand the key messages almost instantly.

This will allow managers to easily play with it and dig in your data.

You dont want to have to say I dont know when your manager asks you for the statistics for a specific country or product.

You dont want to spend hours or days every week or month to update your dashboard, you want it to be almost instantaneous, right Preliminary step: define your KPIs and data sources Quite obviously, the first thing you need to know before creating a dashboard is to know why.

You have to ask yourself the key questions: what do you want to show who will be the recipients what are your KPIs where will the data come from Discuss the expectations with every stakeholder to agree on the content of the dashboard before you create it.

Also, you will have to investigate where to find the data for it.

The structure: design your dashboard around 4 types of sheets There are 4 different types of sheets you will need for a dashboard: Data source sheets contains in a database format the raw data and your calculated columns Reference tables sheets give you the conversion rules between raw data and calculated columns (e.g.

EUR to USD, conversion table to change Zip codes into city names, etc.) PivotTables sheets aggregate your database to prepare them for visualization The Dashboard sheet is the only sheet needed for visualization and it is where you will have your charts (thats where people are going to say whaaa, amazing dashboard).

You can use a color scheme to quickly show the function for each sheet, in particular if you have multiple data source sheets or PivotTable sheets.

You might indeed have several sheets of each type depending on your dashboard, but each sheet will fall into one of these four categories.

Now lets see how to build each of these worksheets to create a great Excel dashboard.

Data sources Data sources sheets host the data from which the dashboard is created.

You can of course have multiple data sources sheets if your dashboard requires multiple extracts.

That means each line represents a unique entry (for instance a sales transaction), and each column represents a field describing this entry (such as the amount of the transaction, the day it was made, the age or gender of the client, etc.).

If your raw data is an extract from a corporate system, it should already be structured as a database.

The calculated column is an additional field to compute a new value.

For example, if you have a Gender field in your database, it might come with a 0 for males and 1 for females.

In that case, you want to have a computed column that rewrite it as Male and Female, because thats how it needs to be displayed for managers eventually.

In your Data sources sheet, you should paste your raw database on the left, ideally with the unique ID in the very left column.

This will make it simpler for you to use VLOOKUP or INDEX MATCH formulas in your calculated columns.

It might be tempting to create your Explicit Gender column close to the raw column which had only 0 and 1, but it is not a good idea.