How to create a Dashboard in Excel

Learn how to create a dashboard in Excel, a powerful decision-making tool that combines graphs, charts, and widgets to visualize data.

Today’s tutorial is an in-depth guide. We are happy if you read on. But if you are in a hurry, download our templates. You will learn how to create a dashboard in Excel from the ground up. In addition, you’ll get a complete dashboard framework.

Table of contents:

1. What is a dashboard? Differences from Reports
2. Before Building a Dashboard: Questions and Guidelines
3. How to create a Dashboard in Excel?
   • Create a layout
   • Get your Data into Excel
   • Clean raw data
   • Use an Excel Table and Filter the data
   • Choose the right chart type for your Excel Dashboard
   • Select Data and build your chart
   • Add a KPI widget
4. Best practices
5. Excel Dashboards Do’s and Don’ts

What is a Dashboard? Differences from Reports

It is time to clear up the differences between dashboards and reports.

• The report can be a more page layout of the task, which makes it necessary. In summary, the report comprised background data. Above all, a report is a text or table-based tool. It supports the work of employees within an organization or a company. It seldom contains visual parts. Usually, you share them by regular scheduling (daily, weekly, or monthly).
• Dashboards are the opposite of reports. Its main goal is to display the key performance indicators on one page, which is crucial for making important decisions. It does not show details by default, but you use the drill-down method sometimes. All dashboards should answer a question.

The ideal scenario is when you have a dashboard showing only the essentials. Reports are yours to explore the details and look behind the scenes. We can now make decisions on an Excel dashboard while the report supplies the background information. The biggest mistake you can make is to use reports and dashboards as synonyms! No, they are not at all alike.

Which one should I choose? You can find out from the reports if you want to know where the data comes from. The correctly chosen KPI makes it easily visible whether things are on the right course. We recommend creating and publishing them in pairs if you want to utilize both. Then, whoever wants to see the essence looks at the dashboard, and if one wants to know the source of the data, they can read through the longer reports.

Before Building a Dashboard: Questions and Guidelines

Before you take a deep dive, wait a moment! Spend time on the planning and researching phase. Let’s see a few questions to ask yourself before you start building your dashboard.

1. What is the purpose of using a dashboard? A dashboard summarizes business events in an easy-to-understand format, and visuals provide real-time results. In addition, it helps us to aggregate and extract the collected values using KPIs. So you will see what you are doing right and where you need to improve.
2. What are the types of dashboards? The operational dashboard shows what is happening now. Strategic dashboards track KPIs, and analytical dashboards help us quickly identify trends.
3. How many KPIs does a dashboard have? Focus on business goals and use fewer than 10 KPIs. Show KPIs only that represent values. This is not the place for less useful metrics; get rid of them.
4. What is the dashboard used for? A co-worker, a manager, or a stakeholder has different information needs. The result must be helpful for all levels. Let us think this through carefully.

How to create a Dashboard in Excel?

Before creating a dashboard in Excel, keep in mind your main objective. This tutorial will help you create an Excel dashboard to track HR activities. Your goal is to show the monthly data on your main charts. Then, build a scorecard to compare the selected and past months. The core of every dashboard is a one-page layout. Why? Remember: a CEO isn’t always interested in the details.

#1. Create a layout

Create a proper draft! You can use paper and pencil, but we prefer Microsoft Excel to create mockups. We have used simple, grouped shapes.

Tip: Let us review the effect of the Excel Dashboard UI mockup. In the figure below, we are showing a layout. First, select the type of grid dashboard layout that you will use. Then, assign a color scheme and font type to the report. Finally, make a wireframe that contains the following style, color codes, and font types. You can prevent most issues using structured data and data tables.

Open an Excel workbook and create three sheets. You mostly use three worksheets for an Excel dashboard:

1. Data Tab: You can store the raw data tables here
2. Dashboard Tab: The main dashboard Worksheet
3. Calculation Tab: Make the calculations on this Worksheet

Your wireframe and structure are ready; it’s time to create a dashboard!

#2. Get your Data into Excel

To create a dashboard, you need to choose data sources. If the data is in Excel, you are lucky and can jump to the next step. If not, you have to use external data sources.

Go to the Data tab and pick one of the import options. It’s easy to import data into an Excel workbook. In the example, you are using a CSV file to create the initial dataset for our dashboard.

#3. Clean Raw Data

Our raw data is in Excel. Now, you can start the data cleansing process. There are many tricks to clean and consolidate data.

• Sort data to see extremes and peaks
• Remove duplicates to avoid errors
• Change the text to lower, upper, or proper case
• Remove leading and trailing spaces

You can find various tools for processing data here.

#4. Use an Excel Table and Filter Data

First, you must check if the required information is in a tabular format. A tabular format means every data point, such as the city’s name, address, or phone number, lives in one cell. If it is, you should convert it into an Excel table and select the data range.

Choose a table or use the insert table shortcut, Ctrl + T, on the Insert Tab.

#5. Choose the right chart type

Now you have an organized, cleaned, and error-free data set, it’s time to choose the proper chart.

Data is useless without the ability to visualize it. Strike a balance between a great-looking Excel Dashboard and its function. First, you can choose what graphs are best for different goals.

• Compare Values: Their characteristic is that they merely show high or low values. Recommended types for charting are a Column, Mekko, Bar, Line, Panel Chart, and Bullet chart. Don’t forget to check how the radial bar chart works.
• Composition: How can you display different sales results in different regions? Pie, Stacked Bar, Mekko, Stacked Column, Area, and Waterfall charts are the most fitting. We also prefer geographical maps.
• Analyzing Trends: To analyze the result of a data set in a given period, use the following charts: Line, Dual-Axis Line, and Column charts. Check this example if you want to create a quick forecast in Excel.
• Show the differences between budget and actual values: use variance charts.
• Performance measurement: Use gauge charts to see how far you are from reaching a goal. It displays a single value.
• Sparklines are tiny graphs in a worksheet cell that visually represent your data set. Use sparklines to show trends in a series of values. Another helpful thing is that you can highlight maximum and minimum values easily. So, the most significant impact of sparklines is that you can position the chart near its data source.
• Dynamic charts are essential for creating interactive charts to refresh the chart based on user choice.

Tip: Pie charts are the most overused graphs in Excel. It’s one of the worst ways to present data. In other words, eliminate the pie charts to create a better dashboard!

#6. Select Data and build your chart

In this phase, we cleaned and grouped data and picked the chart or graph. Now, it’s time to select the data! As you learned, the combo chart requires two doughnut charts and a simple formula.

Select the ‘Calculation‘ tab (which contains filtered data and calculated fields). Highlight the range of what you want to display. In the example, you use two values to show the Acceptance Ratio.

The actual value comes from the Data tab. After that, then calculate the reminder value using this simple formula. In this case, 75%. Next, select the ‘Calculation’ tab. Cell E23 will show the actual value. The second cell, E24, contains a simple formula and displays the remainder value as 100%.

Make sure that the value in the source cell is in percentage format! Okay, now we select the ‘Actual Value’ and ‘Reminder Value’ data. Next, open the ‘Insert Chart’ dialog to create a custom combo chart to preview and choose different chart types. Furthermore, you can move the data series to the secondary axis. Select the inserted chart and press Control + C to duplicate the chart.

#7. Format charts

Now, you have a chart that fits your data. It looks great, but you can improve your Excel dashboard to the next level! First, clean up the chart by removing the background, title, and borders from the chart area. Next, select the reminder value section of the outer ring.

Right-click, then choose Format Data Point. Use the ‘No fill’ option. See the inner ring, select the actual value section, and apply the ‘No fill’ option. Adjust the doughnut hole size if you want. Insert a Text Box and remove the background and border.

Link the actual value to the text box.

To do that, select the Text Box. Next, go to the formula bar and press “=.” Next, select the actual value and click enter. Once the Text Box is linked with the actual value, format it.

Repeat the process for the other data! For example, a typical Excel dashboard contains various charts to display data. Next, repeat the chart insertion and data validation steps for other essential metrics, like the quit rate.

Keep your source data in the Data tab, and do not remove or hide it. If further calculations are necessary, use the Calculation Worksheet. If you want to replace the source data, use the Calculation sheet, not the Data Worksheet.

Tip: If you are uncertain which charts are good for you, don’t hesitate to choose ‘Recommended Charts.‘ In this case, you will get a custom set that Excel thinks best fits your data.

#8. Add a KPI widget

Your Excel dashboard is almost ready. You need only a few components to create a scorecard:

• Label,
• Actual value,
• Annual trendline,
• Variance (between the selected and the last month)

Because you need a little bit more space, merge the cells. Select the cells to place the components and click the ‘Merge cells‘ button. Now, link the label name from the ‘Data’ sheet. If you change the name of the value on the ‘Data’ sheet, the widget label will reflect it. Now link the data from the ‘Data’ sheet to a ‘Dashboard’ sheet.

Go to the formula tab, enter an equal sign, and select the ‘Data’ sheet value. Next, use yearly Data on the ‘Data’ sheet and insert a line chart to create a trendline. To highlight the variance, use a little trick. Go to the ‘Calculation’ sheet and create a helper table.

Create three new conditional formatting rules.

Select the cell that contains variance and copy it. Then, navigate to the ‘Dashboard’ sheet and apply the ‘Paste Special’ option. Next, choose the ‘Paste as linked Picture’ option. Working with linked pictures is easy.

Check the steps in the picture below:

We want to add dynamic text to the main sheet to indicate the changes in key metrics. You link a text to the object inserted into the main Excel dashboard. Then, if you change the value on the source sheet, the target cell will show the refreshed value. What a nice feature! You can apply this trick to textboxes or charts, like sparklines.

Your dashboard is ready!

Download the practice file here.

Best practices for creating visually effective Dashboards

1. A drop-down list is a space-saving solution of great value when you create one-page dashboards. You can use data validation to control the data type or the values users type into a cell. To build the list of options, type them on a worksheet. You can do this method on the sheet with the drop-down menus or on a different worksheet.
2. Conditional formatting is the right choice to highlight cells based on any condition or rule. But, of course, you can use other methods besides colors. For example, you can achieve splendid results using icons, bars, shapes, color scales, indicators, and ratings.
3. Named ranges: You can call selected cells with any given name. First, highlight a range that contains data. Then, in the name box, write the chosen name: ‘sales.’ From this point, you can save time working with cells or ranges.
4. Use a scroll bar to save space on your Excel dashboard.
5. Data Validation: Restrict what users can write in a single cell. Just imagine that ten users in 10 Excel workbooks write phone numbers. There might be mistakes if you do not restrict the format of the phone numbers with the help of data validation when summing up the spreadsheets.
6. Pivot Tables are Excel’s most potent weapon for large data sets. They are easy to use, with only a few clicks, and we can summarize data and drill it into any structure we choose.

Excel Dashboards Do’s and Don’ts

First, review some best practices. Then, consider several ways to boost your dashboard. You need to know the user’s requirements. Under these conditions, these conditions will only be the dashboard useful.

A well-structured dashboard will answer these questions and much more! It can also decrease the timeframe and costs of development.

1. Once you’ve defined the purpose, it’s important to identify which metrics to include. Focus on the metrics that directly align with key business goals and consider the level of detail most appropriate for your audience. This is critical; you may not get it right the first time, so keep it in mind as you build your dashboard.
2. Start with users, not the data; try to understand end-users goals. If you can realize this, you will create a most useful dashboard. What does this all mean in practice? Try to understand the user’s scope. Build an Excel dashboard that is not in constant need of updates. So you can cut development costs.
3. Don’t flood the user with unwanted information. Instead, you should seek that the dashboard is useful for them. Then, create custom views, filter data, and display the relevant information.
4. Provide an overview and allow users to check the details. A well-planned dashboard should be like a quality newspaper. The front page provides a clear overview of the key information and leading news. However, to look at the data in detail, one must know where to navigate.
5. Use visualization and create a clean dashboard. Charting prospects is endless, and that’s where data visualization comes into play.
6. Improve Dashboard UI and UX: Build a menu and control your Excel dashboard from the ribbon. Add tooltips to improve user experience.
7. Use grid layout and consistent color schemes.

Now, let’s see the most common mistakes.

1. Using too many colors: I don’t tell you often enough about the importance of colors. Do you know the game “Where is Waldo?” It’s an excellent game for kids! But please don’t follow this method to create a stunning dashboard. Try to minimize the number of applied colors and use flat color schemes. Keep the visual content as simple as possible.
2. You are cluttering the screen with a useless design: What do you want to see? A clean dashboard or a traffic jam? Get rid of borders and frames!
3. You are using pie charts. Remember that nothing stands out when all charts are in the spotlight. All the data displayed on a dashboard is important, but not all are equally important.

Wrapping things up:

The truth is that creating a dashboard in Excel involves more than these eight steps. If you feel comfortable with the basics, you can start storytelling using your data.