GET A FREE TEMPLATE OF EXCEL DASHBOARD AT THE END OF THIS POST!
Dashboards are reporting tools meant to help managers make business decisions. They provide an overview of a situation or activity to understand the key results, trends and attention points. Dashboards are usually updated on a regular basis. 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.
What does a good dashboard look like?
Rule #1: make it visually appealing. Managers and decision-makers need to be able to understand the key messages almost instantly.
Rule #2: make it highly dynamic. This will allow managers to easily play with it and dig in your data. You don't want to have to say "I don't know" when your manager asks you for the statistics for a specific country or product.
Rule #3: make it very easy to update. You don't 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. conversion table to change 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 (that's where people are going to say "whaaa, amazing dashboard").
These 4 types of sheets have really different roles. 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 let's see how to build each of these worksheets to create a great Excel dashboard.
Data sources sheets host the data from which the dashboard is created. Usually, the data comes from raw Excel extracts from your corporate CRMs or other business tools. You can of course have multiple data sources sheets if your dashboard requires multiple extracts.
Data sources should be structured as a database.
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.
Data sources should contain both the raw data and the calculated columns.
The raw data is the data you are using for your dashboard. 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 that's how it needs to be displayed for managers eventually.
Data sources should have the unique ID in the first column.
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.
Calculated columns should be all on the very right.
Don't put them in the middle of your raw data. 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. 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 haven't made it a table). If calculated columns are spread all over your database, it will take much more time and you risk forgetting one column, leading to wrong statistics in your dashboard! And in any case, it will be a mess if future extracts have more columns, so keep your calculated columns aside.
Use a color scheme in the headers to differentiate raw columns from calculated columns.
That way you will be able to quickly see where are your formulas and to extend them down to the bottom of the database when you need to update your dashboard (if you haven't used a table). It will also help others understand the logic in your dashboard, in particular if you have to share it.
Format your database as an Excel table.
Select your database, go to the Insert tab and click Table. Rename your table as "MyData" for instance, from the Design tab. Why use a table? Reason 1 is that you are going to create PivotTables based on this database. If your PivotTables refer to actual tables, their data source will automatically adjust to any number of rows or columns you add in the future. That way, you won't have to manually update every PivotTable's data source just because you have added a new column. Reason 2 is that tables automatically extend your formulas down to the last row, so you won't have to do it manually when you update the data extract. Click here for 10 more reasons to use Excel Tables.
Alternatively, you can use a named range to allow future references to your data source.
On Excel, go to the Formula tab and click Define Name. In the Name field type "MyData" and in the "Refers to" field type: =OFFSET('Data Source'!A1;;;COUNTA('Data Source'!A:A);COUNTA('Data Source'!1:1)). You now have a range named "MyData" that allows you to create Pivot Tables without having to update their data source every time you add a new row or column.
This worksheet is here to keep the referential for your raw data extracts.
Create actual tables for your data in the Reference tables.
Just like for your data source, once you have added a referential in your Reference tables worksheet, select it and go to Insert / Table. That way, the lookup formulas in the calculated columns of your Data source worksheet will automatically adjust if you add new elements to the referential.
Use lookup functions towards your reference tables to convert your raw data into calculated columns.
It might be obvious, but it's much simpler to just change or add a value into a reference table than to update multiple formulas in your calculated columns. So if your raw data is in EUR and you need multiple calculated columns converting them to USD... referring to a reference table for currencies will allow you to just update the exchange rate rather than change all your formulas.
Pivot tables sheets are where your data is organized. They act as a necessary intermediary between your data sources and the data visualization charts.
Create a PivotTable from the Table containing your data source.
Make sure your PivotTable refers to the name of the Table you have created for your data source, so you have the benefice of flexibility for future updates. When choosing the data source for the Pivot Table, type "MyData" to refer to the table you have created earlier. That way, you won't have to change the data source if you add new columns in the future.
For every other PivotTable you need, create it by copy / pasting from an existing PivotTable.
Creating from scratch a new PivotTable based on your data source makes your file much bigger (and slower) than if you create if from an existing PivotTable (Excel duplicates the cache version of your data source).
Create as many PivotTables as you will have charts in your dashboard.
PivotTables are amazing: they can all be updated instantaneously. That's why you need to use them to make your dashboard very easy to update. PivotTables are the engine under the hood: your managers won't look at them but they are the essential piece that will make the whole dashboard automated.
Keep a few blank rows between each PivotTables.
Your PivotTables may take more space when you update them later on, and you will get an error message if a PivotTable doesn't have the space it needs to update. Also, we will be adding Slicers (as explained later) and Slicers will required additional rows for each PivotTable just like filters.
Now it's time to create the visible part of the dashboard, and the only one that people will be looking at. In this tab you will create your charts and display the KPIs you have previously defined.
Use PivotCharts only.
The whole benefit of having created PivotTables in the previous section is that you can now create PivotCharts. Go in your PivotTables worksheet, click on a PivotTable and then go to the Analyze tab and click PivotChart. PivotCharts have exactly the same behavior as the PivotTable they are related to. They will in particular automatically update. So when you update your dashboard next month, you won't have to extend the data source of every chart to take into account the new month, that will be done automatically.
Format your chart as you would with any "classical" chart.
By default, PivotCharts are created with buttons. Right click on them and click "Hide all field buttons on chart". Then format your PivotChart as you want it to look, just like you would do with any other chart.
Save chart as templates to avoid recreating the desired format for every new chart.
For every new chart type you create, save it as a template to make it available for later use. Right-click on the chart and choose "Save as Template". This will let you save the chart format to avoid recreating the format every time.
So for every other chart you create later on, you can now just use the template instead of entirely recreating the format. To reuse the template, go in the Design tab, click "Change chart type" and click the "Templates" button on the left (see screenshot below).
Build your dashboard!
Now you can just create as many chart as you need, format them using the saved templates and step by step create your dashboard. Make it visually appealing and self-explanatory.
Use Slicers to make your dashboard simple and dynamic.
Slicers are just amazing. Slicers are basically like filters, but much easier to use. For each chart you create, ask yourself if your audience may want to zoom on a sub-category. If you have a chart showing the sales of your company, then probably each product manager will want to zoom on his product or geography for instance. With Slicers, charts update automatically to the filters you click on. That way all of the information is available for everyone, in any combination they want, and you also avoid creating a separate dashboard for each product or geography.
Select your chart, go to the Analyze tab, and click Slicer. Choose the related field, e.g. Product or Greography. Now people can click on a value on the slicer, such as Product A, and the chart will adjust to show only the values for Product A. You can also right-click on a Slicer and click on Report Connections to link a Slicer to multiple charts and see all of them adjust to the values selected by the user.
One of the key benefits of slicers is that you avoid the multiplication of charts. Without it, you would have to create a different chart for each Product and each Geography... or for any combination of them that might interest your manager! Slicers gives your dashboard an amazing simplicity. You don't have to guess what people want to see, you let them decide and play with the dashboard. A dynamic dashboard is also a much simpler dashboard to use, with less charts but more information available!
If you are familiar with VBA, create buttons activating Custom Views.
For people familiar with VBA, you can give an extra boost to the dynamic of your dashboard by creating custom views. This is interesting only if you have quite a number of graphs and you want to spare users the pain of scrolling up/down and left/right looking for charts.
First manually organize your charts in different views. A view will be the content of 1 screen and you will want to display on it a coherent group of charts. Then for each view, create a macro with the following code Application.Goto Range("B1"), True where B1 in this example is the cell you will want at the top left corner of your view.
Now you can assign this macro to a shape. When the user clicks this shape, it will change the view to match your custom view.
You can create a kind of "menu" on the left, with shapes linking to different views. Freeze panes to make the menu always visible. People can now just change views as if they were slides by just clicking on the menu.
Prepare your charts so they can easily be exported to PowerPoint.
Very often, your dashboard will feed a PowerPoint reporting to be shared with management, sent to clients, etc. You probably have experienced how copy / pasting charts into PowerPoint sometimes forces you to waste a lot of time resizing them again and again.
In PowerPoint, insert a rectangle shape that is just as big as your slide. Now in Excel, insert a similar rectangle shape and give it the same dimensions as the one in PowerPoint. You can consider this shape as a preview of the PowerPoint chart. Place your charts inside this frame as you want them to be in PowerPoint. And when you copy them in PowerPoint, they will have the exact same dimensions as they had inside that frame. Cool, right?
You can find more detailed explanations on this trick in this dedicated tutorial.
Paste charts to PowerPoint with links.
If you have a PowerPoint presentation that you need to update every time you refresh your dashboard, you should definitely consider pasting your charts from Excel to PowerPoint with links. Copy a chart in Excel, go to PowerPoint, click Paste / Paste Special and select "Link" on the left of the window.
Now refreshing charts can be done from PowerPoint with a simple right-click.
Updating the dashboard
Now let's see how long it takes to update the dashboard. What you just need to do:
Paste the new extract in place of the old one in the data source
If needed, update the Reference tables
Select a PivotTable, right-click and choose Refresh
Right-click on a chart in PowerPoint and refresh all linked charts
That's it. Your entire Excel dashboard and PowerPoint presentation have been updated in less that a minute. You now have a cool dashboard. People can easily play with it, looking into different values by using the Slicers. You get the benefit of the "wow effect" of a great, flexible dashboard. And you can update it in just a few seconds. Well done!
Want to be an Excel & PowerPoint ROCK STAR? Download the Power-user add-in: it contains templates, diagrams, charts, maps, icons and many tools that will make you a super hero with PowerPoint and Excel!