Don’t forget to share this post

Dashboards are a great way to keep track of important metrics without having to search through mountains of data. Microsoft Excel features many tools and features that allow you to quickly and easily create simple interactive dashboards, as well as highly complex and automated dashboards. You can get and transform data from multiple sources of different types and analyze it using a variety of tools like built-in functions, pivot tables, and charts. Slicers and timelines allow the user to interact with the dashboard and choose what they want to focus on. Additionally, you can record macros and use VBA scripts to automate tasks and create more advanced interactions between the dashboard and its users.

In this guide, you will learn how to create a simple, interactive Excel dashboard in four main steps. First, spend as much time as you need planning the dashboard. Second, import the data you need to your spreadsheet, and don’t forget to clean it up. Third, choose the type of visualization that best illustrates your chosen metrics. Finally, put it all together in your dashboard.

How to Create a Dashboard in Excel?

Follow the steps below to create a simple interactive dashboard in Microsoft Excel.

1. Plan Your Excel Dashboard

The first and most important step is to plan the dashboard carefully. Time invested in this step will help you avoid mistakes and wasted time when putting your dashboard together. The key is to focus on the purpose of your dashboard and its intended audience. What kind of KPIs is your audience interested in? What data can you use to visualize your chosen KPIs, and where is it located? There are so many cool tools and features in Excel that it’s tempting to overload the dashboard. However, remember that your dashboard needs to be uncluttered and easy to understand.

For this example, I will create a simple, high-level sales dashboard. The focus will be on total sales for the first quarter of this year. However, stakeholders also want to see total sales by month, by product, and by sales team.

2. Import Data to Your Excel Dashboard

One of the things you need to decide is whether you want to import the data or connect to it. By creating a connection, you ensure that updates to the source are reflected in your dashboard. Fortunately, connecting data to Excel is very easy. With Power Query, you can query and import data from practically any source you like, including different document types, folders, databases, websites, and apps. The ‘Data from Picture’ feature even extracts data directly from images.

For this example, the data I need is already in an Excel workbook. If it isn’t already, remember to convert your data to an Excel table, as this greatly facilitates a dynamic dashboard that changes as soon as data is updated in the table. Additionally, ensure that the data is clean and correctly formatted to avoid unpleasant surprises later on.

To learn more about importing data to Excel, check out these guides:

3. Visualize Data in Excel

As mentioned above, Microsoft Excel offers a lot of options for data visualization, but this doesn’t mean you should use all of them in your dashboard. Remember to focus on what the user wants to see and find the clearest way to visualize that. Pivot tables are a great way to summarise data, and it’s very easy to connect slicers and charts so that users can focus on different aspects. To learn more about this topic, check out our guide on How to Create a Pivot Table in Excel.

Excel also features a great variety of charts to visualize different types of data, including line & area charts, bar & column charts, pie charts, histograms, scatter charts, sunbursts & treemaps, waterfall charts, funnel charts, map charts, and many more.

Given the metrics I want to include in the sales dashboard, I will use pivot tables and charts to illustrate the following items for my dashboard.

  • Total Sales - Pivot Table to Summarise
  • Monthly Sales - Pivot Table & Column Chart
  • Sales by Product/Team - Pivot Table, Column Chart, & Slicers

4. Set Up Your Dashboard

Now that you know what data you want to show and how you want to visualize it, it’s time to put it all together. In order to avoid users accidentally moving or deleting parts of your dashboard, make sure you add the necessary precautions by protecting or locking cells and other objects. To learn more about this, check out these guides on:

Add Pivot Table with Total Sales

Let’s start by creating a pivot table to summarise the data and get the total sales amount.

  1. 1. Click anywhere in the table and go to the ‘Table’ tab. Click ‘Summarise with Pivot Table’.
How to Create an Excel Dashboard A Beginners Guide Summarise with Pivot Table
How to Create an Excel Dashboard: A Beginner’s Guide - Summarise with Pivot Table
  1. 2. The table should be selected automatically.
How to Create an Excel Dashboard A Beginners Guide Select Table
How to Create an Excel Dashboard: A Beginner’s Guide - Select Table
  1. 3. Select the destination for the pivot table. In this case, the ‘Dashboard’ sheet.
How to Create an Excel Dashboard A Beginners Guide Select Destination
How to Create an Excel Dashboard: A Beginner’s Guide - Select Destination
  1. 4. Add the ‘sales_amount’ field to the ‘Values’ area. Right-click and select ‘Field Settings’.
How to Create an Excel Dashboard A Beginners Guide Field Settings
How to Create an Excel Dashboard: A Beginner’s Guide - Field Settings
  1. 5. Change the field name to whatever you want and click ‘Number…’ to change the display. Since I will be copying this pivot table to create the others, I want to ensure that the amounts are displayed as currency.
How to Create an Excel Dashboard A Beginners Guide Rename Field
How to Create an Excel Dashboard: A Beginner’s Guide - Rename Field
  1. 6. Select the appropriate format and click ‘OK’ in both windows to apply.
How to Create an Excel Dashboard A Beginners Guide Currency Format
How to Create an Excel Dashboard: A Beginner’s Guide - Currency Format
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Add Pivot Table & Slicers with Sales by Product & Team

Now it’s time to add the second pivot table, by copying and pasting the first one.

  1. 1. Select the pivot table and copy it.
How to Create an Excel Dashboard A Beginners Guide Copy Paste Pivot Table
How to Create an Excel Dashboard: A Beginner’s Guide - Copy & Paste Pivot Table
  1. 2. Once you’ve pasted the pivot table, add the ‘sales_team’ field to the ‘Rows’ area.
How to Create an Excel Dashboard A Beginners Guide Add Sales Team to Rows
How to Create an Excel Dashboard: A Beginner’s Guide - Add Sales Team to Rows
  1. 3. Next, add ‘product’ to the ‘Columns’ area.
How to Create an Excel Dashboard A Beginners Guide Add Product to Columns
How to Create an Excel Dashboard: A Beginner’s Guide - Add Product to Columns
  1. 4. Below, the row totals are now in bold, just like the column totals.
How to Create an Excel Dashboard A Beginners Guide Bold Row Totals
How to Create an Excel Dashboard: A Beginner’s Guide - Bold Row Totals
  1. 5. You can also rename or delete the row and column labels by double-clicking on them.
How to Create an Excel Dashboard A Beginners Guide Edit or Delete Labels
How to Create an Excel Dashboard: A Beginner’s Guide - Edit or Delete Labels
  1. 6. Alternatively, you can hide the row and column labels altogether. Click ‘Insert Slicer’ to make this pivot table interactive.
How to Create an Excel Dashboard A Beginners Guide Insert Slicer
How to Create an Excel Dashboard: A Beginner’s Guide - Insert Slicer
  1. 7. Select the fields that you want to show in the slicers. Users will be able to choose which products or teams they want to focus on.
How to Create an Excel Dashboard A Beginners Guide Select Slicer Fields
How to Create an Excel Dashboard: A Beginner’s Guide - Select Slicer Fields
  1. 8. The slicers contain buttons with all the options. Users can select individual or multiple options from each by using the multiple selection button.
How to Create an Excel Dashboard A Beginners Guide Slicers Added
How to Create an Excel Dashboard: A Beginner’s Guide - Slicers Added
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

Add Pivot Table with Monthly Sales

Let’s add the last pivot table to show the total sales per month.

Copy and paste the original pivot table - total sales - and add ‘month’ to the rows.

How to Create an Excel Dashboard A Beginners Guide Add Pivot Table with Months
How to Create an Excel Dashboard: A Beginner’s Guide - Add Pivot Table with Months

Organize Dashboard & Add Pivot Charts

Now that the pivot tables are ready, you can move them around your dashboard to find the best place for them. Remember to select the pivot table and click ‘Move Pivot Table’, as shown below.

How to Create an Excel Dashboard A Beginners Guide Move Pivot Tables
How to Create an Excel Dashboard: A Beginner’s Guide - Move Pivot Tables

Since I want to resize and highlight ‘Total Sales’, I can select the cells containing the pivot table and increase the font size, as well as center the contents. Instead of merging cells, simply select the cell with the contents and as many cells as you want to the right. Go to ‘Format Cells’ and under ‘Alignment’, check that ‘Horizontal Alignment’ is ‘Centre Across Selection’.

How to Create an Excel Dashboard A Beginners Guide Center Across Selection
How to Create an Excel Dashboard: A Beginner’s Guide - Center Across Selection

You can change the background color for your dashboard by selecting the cells and applying your chosen color fill. Below, I have selected white background for the dashboard.

How to Create an Excel Dashboard A Beginners Guide Basic Dashboard
How to Create an Excel Dashboard: A Beginner’s Guide - Basic Dashboard

As you can see below, the slicers for ‘sales_team’ and ‘product’ affect both the pivot table and the chart that is connected to it.

How to Create an Excel Dashboard A Beginners Guide Slicers with Multiple Selections
How to Create an Excel Dashboard: A Beginner’s Guide - Slicers with Multiple Selections

If you want to connect the slicers to another pivot table, like ‘Total Sales’, simply right-click on each slicer and choose ‘Report Connections’. Choose the pivot tables to which you want to connect each slicer.

How to Create an Excel Dashboard A Beginners Guide Connect Slicers
How to Create an Excel Dashboard: A Beginner’s Guide - Connect Slicers

Conclusion

It’s easy to create a simple interactive dashboard in Microsoft Excel. First, spend as much time as needed planning the dashboard. You need a clear purpose and an understanding of what your users need to see in the dashboard. Of course, the options are limited to the data you actually have available, so think carefully about what metrics or KPIs you want to show and where the required data is located.

Using Power Query, you can connect to practically any data source and import the data you need. In fact, you can easily transform the data and calculate important metrics before importing it into your spreadsheet. Excel also offers a lot of variety in terms of data visualization, including tools like slicers and timelines to allow the user to interact with the dashboard. By using Excel tables, you also ensure that the dashboard is dynamic and dashboard data is automatically updated when the table changes.

You have step-by-step instructions on how to create a simple interactive dashboard in Microsoft Excel, as well as links to resources that can help you create more advanced dashboards and templates.

Check out the guides below to learn more about KPIs, as well as dashboards and templates in Microsoft Excel, Google Sheets, and Tableau.

Maria Del Olmo
Originally published Apr 5 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets