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

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:

• How To Use Excel Power Query for Beginners
• Excel Connections: Get Data from External Sources
• Import And Export From SharePoint To Excel (& Vice Versa)

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:

• How to Lock Cells in Excel? (Cells, Sheets & Formulas)
• How to Lock & Protect Excel Workbook From Editing
• How to Unhide Excel Sheets and How to Hide Sheets in Excel?
• Protect and Unprotect a Sheet in Excel

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

1. 2. The table should be selected automatically.

1. 3. Select the destination for the pivot table. In this case, the ‘Dashboard’ sheet.

1. 4. Add the ‘sales_amount’ field to the ‘Values’ area. Right-click and select ‘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.

1. 6. Select the appropriate format and click ‘OK’ in both windows to apply.

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.

1. 2. Once you’ve pasted the pivot table, add the ‘sales_team’ field to the ‘Rows’ area.

1. 3. Next, add ‘product’ to the ‘Columns’ area.

1. 4. Below, the row totals are now in bold, just like the column totals.

1. 5. You can also rename or delete the row and column labels by double-clicking on them.

1. 6. Alternatively, you can hide the row and column labels altogether. Click ‘Insert Slicer’ to make this pivot table interactive.

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.

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.

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.

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.

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

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.

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.

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.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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.

• The Ultimate Guide to Tableau Dashboards (+ Examples)
• How to Create a Google Sheets Dashboard Step-by-Step
• Best 15 Free Dashboard Software and Tools in 2023
• Top 11 Excel Finance Templates and Financial Statements for 2023
• Top 10 Free Excel Business Budget Templates for 2023
• What is a Key Performance Indicator (KPI)? Guide and Examples
• The Guide to SMART KPIs: Definition, Tools, Best Practices