A Microsoft Excel dashboard provides an overview of key performance indicators (KPIs) and other important metrics. Particularly, it features various graphs, charts, and tables to present these KPIs in a visual manner. They’re commonly used in business settings to monitor and analyze data trends, track performance metrics, and make informed decisions. This tutorial walks you through creating an effective dashboard in Excel.
Good to know: learn how to create a custom style in Word and Excel.
Creating a Dashboard in Excel
There’s no single way to create a dashboard in Excel, as it depends on the information that you want to present. Yet, to provide further help, this tutorial outlines a few key steps that will help create an effective dashboard.
1. Plan Your Dashboard Structure
Before creating your dashboard visuals, plan the specific pieces of information that you want to include. Start by identifying the key performance indicators that are crucial to your business, as well as what your intended audience cares about.
For example, let’s say that you want to create a dashboard for the month’s sales data. From your raw data, determine several key metrics (as demonstrated below).
| Category in Raw Data | KPI Category |
|---|---|
| Order quantity | Average weekly orders and best-selling items |
| Unit price, quantity, and subtotals | Total weekly or monthly sales |
| Country/origin | Orders per region, country, or area |
There are multiple ways to convert your raw data into key metrics, so we’re leaving that up to you to configure, depending on what you need to include in your dashboard.
2. Import Your Data Into Excel
Once you have planned your dashboard structure, you can proceed with importing your data into Excel by following these steps:
- Launch the Excel app on your computer.
- In the “Home” menu, choose “Blank workbook” to start a new spreadsheet or open an existing spreadsheet file.
- Go to the “Data” tab, and select an import option from the “Get & Transform Data” menu.
There are numerous ways to import your data, depending on the source. For example, choose the “From Text/CSV” option if you have a CSV file at hand. Select your preferred source, and proceed with the provided import instructions.
FYI: want to protect your work? Learn how to add a watermark in Excel.
3. Create Different Tabs
Create your dashboard on a separate worksheet (or tab). This way, you can process your raw data without worrying about layout mistakes. We recommend that you create at least three different tabs when setting up a dashboard in Excel.
- Raw: import your raw, unprocessed data here. You can also do the data cleaning process in this tab.
- Computations: this is where you should analyze and audit your data to derive the key metrics and calculations you want to include in your dashboard.
- Dashboard: this tab will be the final output, where you can design and insert visual elements, such as charts, graphs, and tables.
Feel free to name the tabs however you want. Those listed above in our example are for clarity only.
4. Transform Your Data Into Tables
Creating tables in Excel is incredibly helpful for calculations and analysis later on. Follow these steps to transform your data into table(s):
- Go to the “Raw” tab, and select all the cells containing your data.
- Open the “Quick Analysis” tool by clicking the icon located in the bottom-right corner of your data.
- Choose “Tables” from the options in the Quick Analysis tool.
- If the icon for the Quick Analysis tool isn’t visible:
- Press Ctrl + T on Windows or Command + T on Mac to directly create your table.
- Click “OK” in the dialog box.
Some may argue that creating tables just adds another non-essential step to the process, but there are three reasons why you should.
- Data validation: choose to add data validation to your tables, which would help keep your data free from inaccuracies and errors.
- Referencing: Microsoft Excel allows you to reference tabular data. Compared to repeatedly highlighting entire rows or columns, tables make referencing much easier.
- Filters: Excel tables have built-in filters on the headers, which helps you navigate your data more quickly.
Good to know: if you’re looking to fully analyze your data, learn how to use the What-If Analysis Tools.
5. Clean Your Data
To ensure that your table will be effective for your data analysis, it’s also important that you clean your data. Try one of the following methods:
- Eliminate duplicate records
- Trim extra spaces and remove blank rows
- Sort your data based on low-to-high values
- Alter the letter case of the text
6. Analyze Your Data
You will probably use several functions to process your data. However, which function you need depends on the key metrics you want to gather. For example, you can use the SUM function to learn the total sales of your business in a specific month. More functions that are commonly used to build an Excel dashboard are explained below:
- IF: used for conditional statements and calculations.
- IFERROR: helps handle errors in formulas by providing an alternative value or action.
- ROW/COLUMNS: returns the row or column number of a reference.
- VLOOKUP: searches for a value in the leftmost column of a table and retrieves a corresponding value from a specified column within that table.
- INDEX/MATCH: used together to search for a value in a range and return a corresponding value from another column or row.
FYI: you may find these advanced Excel formulas useful.
You’ll likely deal with column-related calculations, such as getting the total quantity or price of products. To reference columns more easily, follow these steps:
- Select any cell on your table.
- Go to the “Table Design” tab.
- Replace the generic name provided in the “Table Name” field.
- Switch to the “Computations” tab, and start a function (e.g., the SUM function).
- Use the syntax
=FUNCTION(TableName [Column Name])as illustrated below.
=SUM(Table1[Quantity])
FYI: read on to learn about using the COUNT functions.
7. Insert a PivotTable
One of the best features in Excel that let you organize your data beautifully is the PivotTable. It can help you sum up values in different categories and sort data with just a few clicks. In our sales dashboard sample, you can use a PivotTable to showcase the best-selling products for a business. Follow this step-by-step guide to create a PivotTable in Excel:
- Go to the worksheet where your table is located.
- Highlight every cell of your table.
- Navigate to the “Insert” tab, and click “PivotTable.”
- Select your preferred destination worksheet (e.g., “Existing Worksheet”).
- In the PivotTable fields side panel, select the fields that will provide valuable insights for your dashboard (e.g., “Product,” “Total Sales,” and “Quantity”).
- Copy your PivotTable to the Dashboard tab, and adjust its layout as necessary.
8. Recommended Charts
While selecting your data in a table, Excel is smart enough to recommend different chart types. For instance, if you want to compare the total sales across different regions, Excel may suggest a clustered column chart. Follow these steps to access this feature:
- Highlight the columns and rows that you want in your chart.
- Go to the “Insert” tab.
- Select “Recommended Charts,” and choose your preferred option.
The choice of chart types for your dashboard is entirely up to you. Excel provides various options, such as pie charts, bar graphs, sparklines, scatter plots, and more. Whichever you want to use, access them in the “Insert” tab.
Tip: learn all about conditional formatting in Excel in this guide.
Frequently Asked Questions
What is the purpose of an Excel dashboard?
Dashboards in Excel serve to summarize datasets using charts and graphs. In a business context, dashboards help investors and teams visualize key performance indicators more clearly. This way, you can determine areas for improvement and aspects to maintain.
What is the difference between a dashboard and a report?
Dashboards and reports have distinct purposes and characteristics. Dashboards provide broader data insights compared to reports, which focus on specifics. It aims to address key metrics and offer a high-level data snapshot. Reports, on the other hand, may include historical data, text explanations, and other detailed information.
How frequently does the Excel dashboard need to be updated?
There’s no iron rule to the frequency of dashboard updates; it all depends on your needs. However, we recommend that you refresh your Excel dashboard monthly to ensure timely and relevant data.
Is an Excel dashboard free?
The cost of creating your dashboard generally depends on your access to Microsoft Excel. Using the online version is typically free, while the full computer app requires you to pay a subscription or one-time purchase.
All images and screenshots by Princess Angolluan.
Our latest tutorials delivered straight to your inbox
