How to Create Impressive Excel Dashboards

Dashboards are powerful reporting tools designed to help managers make informed business decisions.

They provide a comprehensive overview of a situation or activity, enabling users to understand key results, trends, and areas that require attention.

This guide will equip you with valuable design principles, along with numerous tips and tricks to create impressive dashboards that can be updated in less than a minute.

Introduction

What Does a Good Excel Dashboard Look Like?

👉 It’s Beautiful! Most managers will take just one look at your dashboard and decide in 10 seconds or less if they want to explore it further. You need to make it visually appealing to entice them to delve deeper, and the data should be instantly comprehensible.

👉 It’s Dynamic! We all approach data from different perspectives. A dynamic dashboard should allow managers to easily interact with the data and drill down into specific KPIs by country, product, segment, and more.

👉 It Takes 1 Minute to Update! Don’t spend hours or days updating your dashboard. Make your dashboards super easy to refresh. This approach offers several benefits:

• The dashboard will always contain fresh, relevant data.
• Your colleagues can update the dashboard for you when you’re away.
• You can save time and improve your work-life balance.

Define Your KPIs and Data Sources

Before creating a dashboard, it’s crucial to understand its purpose and scope. Ask yourself these key questions:

1. What do you want to show?
2. Who will be the recipients?
3. What are your Key Performance Indicators (KPIs)?
4. Where will the data come from?

Discuss expectations with all stakeholders to agree on the dashboard’s content before you begin creating it. Additionally, investigate where to find the necessary data.

This step may seem obvious but is often neglected, leading to considerable time waste.

Design Your Dashboard Around Four Types of Sheets

The foundation of a good dashboard lies in its structure. You will need four different types of sheets:

1. Data Source Sheets: Contain the raw data and calculated columns.
2. Reference Tables Sheets: Provide conversion rules between raw data and calculated columns (e.g., EUR to USD conversion, Zip codes to city names).
3. PivotTables Sheets: Aggregate your database to prepare it for visualization.
4. The Dashboard Sheet: The only sheet needed for visualization, where your charts will be displayed (this is where people will say “Wow, amazing dashboard!”).

These four types of sheets serve distinct roles. You may sometimes have multiple sheets of each type, but each sheet will fall into one of these categories.

Pro Tip: Use a color scheme to quickly differentiate between sheet types, especially if you have multiple data source sheets or PivotTable sheets.

Now, let’s explore how to build each of these worksheets to create a great Excel dashboard.

The Data Source Sheet

Data source sheets host the data from which the dashboard is created. Usually, this data comes from raw Excel extracts from your corporate CRMs or other business tools. You can have multiple data source sheets if your dashboard requires multiple extracts.

Structure Your Data Sources Like a Database

This is the most important rule of this entire guide!

Data sources need to be structured as databases, meaning:

• Each row represents a unique entry (for instance, a sales transaction).
• Each column contains a unique field which are attributes of these entries.

Examples:

✅ Transaction amount, age of the customer, or gender of the customer are all distinct fields.

❌ The sales amount in May and the sales amount in June are NOT distinct fields! The “Sales amount” should be in one column, and the “Month” in another column.

When data is extracted from a corporate system, it’s usually already structured as a database.

💡 If the data is not structured properly, you can use Power-user’s UnPivot feature to flatten tables, or watch this UnPivot video.

Format Your Database as an Excel Table

Select your database, go to the “Insert” tab, and click “Table”. Rename your Table as “MyData” from the “Design” tab.

Why Should You Use a Table?

✅ When you create PivotTables on this database, their data source will automatically adjust to any number of rows or columns you add in the future, instead of having to manually update every PivotTable’s data source for each new column or row you add later.

✅ Tables automatically extend your formulas down to the last row. So when you want to update the dashboard, you won’t have to do this manually. It will save you time and significantly reduce the risk of errors.

💡 Want to learn more about Tables? See 12 reasons to use Excel Tables.

Alternatively, and only if you really, really dislike tables, you could use a named range instead.

For this, go to the “Formula” tab and click “Define Name”. In the “Name” field, type “MyData” and in the “Refers to” field, you can use a formula like this:

=OFFSET(‘Data Source’!A1,,,COUNTA(‘Data Source’!A:A),COUNTA(‘Data Source’!1:1))

This creates a range named “MyData” which you can reference in your PivotTables, ensuring you won’t have to update the data source of your PivotTables when rows or columns are added.

Important Practices for a Data Source Sheet

✅ Data sources should contain both the raw data and the calculated columns, with a color scheme to differentiate them.

✅ Don’t modify the raw data (beyond formatting). This is what calculated columns are for. If you make changes, you’ll have to repeat them with every update, which we want to avoid.

✅ Data sources should have the unique ID in the first column. This will make it easier to use lookup formulas in your calculated columns.

✅ Calculated columns should be all at the very right. This will make future updates of raw data easy.

Example:

In our extraction, there is a “Gender” field which contains 0 for males and 1 for females. We want to change this into actual “Male” and “Female” text because that’s how it will appear in the dashboard eventually.

To do this, we will add a new calculated column at the very right of the table, which will have the role of converting the numeric value into legible text.

The Reference Tables Sheet

This sheet will host the referential for your 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” then “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

It’s much simpler to 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.

💡 We recommend using the XLOOKUP Excel function instead of the old-fashioned VLOOKUP and INDEX/MATCH, as this new function performs better in many aspects.

The PivotTables Sheet

PivotTables sheets are where your data is organized and analyzed. They act as a necessary intermediary between your data sources and the data visualization charts.

Create PivotTables from the Table Containing Your Data Source

When creating PivotTables, make sure to use the Table name (“MyData” in this case) as the data source, not the address itself (not something that looks like “Data Source!A1:G5000”).

If you select the entire Table and insert a PivotTable, the Table name should already be used by default as the source, so it’s easy.

Prepare PivotTables for Each Chart

PivotTables are the engine under the hood: the people who see your dashboard won’t look at them, but they are the essential piece that will make the whole dashboard automated.

All PivotTables in the workbook can be updated in one click. That’s how we will be able to refresh the dashboard instantly.

Keep Blank Rows Between Each PivotTable

Your PivotTables may take more space when you update them later. To avoid getting error messages when a PivotTable doesn’t have the space it needs to update, you will need some buffer rows and columns.

The Dashboard Sheet

Now it’s finally time for the fun part! Let’s create the beautiful dashboard itself, which will be the only sheet people will be looking at.

Use PivotCharts Only

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 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.

Format Your Chart

Right-click on the PivotChart and click “Hide all field buttons on chart”.

Some advice in terms of chart formatting:

✅ Pick the Right Chart Type: Using different chart types will better highlight key information and make each KPI easier to remember.

💡 Check this infographic on how to pick the best chart type for your data.

✅ Less is More: Remove unnecessary clutter such as axis, legends, and gridlines to enhance readability.

✅ Use Consistent and Meaningful Colors: Apply meaningful colors to create contrast when needed.

Save Chart as Templates

Don’t reinvent the wheel every time: save your charts as templates to reuse the same format later.

👉 To save a chart as a template, right-click it and choose “Save as Template”.

You will need to create a new template for each chart type. Then when you add a chart, you can just apply the template instead of redoing the entire format manually.

Build the Excel Dashboard!

Now you can create as many charts as you need, format them using the saved templates, and assemble them to create your dashboard step by step.

Make it visually appealing and self-explanatory!

Leverage Slicers and Timelines

Slicers are like filters but much easier to use.

An excellent dashboard can be read and analyzed by different people in the organization, avoiding the need for multiple dashboards with partial data.

Ask yourself how the data could be analyzed in sub-segments:

• C-Levels will want to look at aggregated data to see the overall key trends.
• Each product manager will want to see the sales on their own product.
• Each area manager will want to look at the data for their own geography.

Most people replicate all the charts for each product and geography, but with Slicers, you can have one chart that allows users to filter based on what they want to look at. This way, all of the information is available for everyone, in any combination they choose.