Learn how to create a dashboard in Excel from the ground up. Our definitive guide might be just what you need! Above all, we will show you how to build from a clean white spreadsheet to a dynamic dashboard.
No, it’s not a usual tutorial. We not only going to learn about dashboards but will detail every step that connects with the main workflow. You’ll get a complete framework using Microsoft Excel. An easy learning curve, isn’t it? Creating dashboards in Excel is not rocket science.
Follow our step-by-step tutorial, we’ll show you how to
- import and filter data,
- prepare and clean data,
- build and modify your charts,
- improve your dashboard
What is an Excel Dashboard?
For sure that they do not only represent visual data! When you look at a dashboard, you not only interested in real-time performances. Under the hood, there are complex data. You can dig further down in them by a drill-down method. It happens quickly! We can call the dashboard the engine of the company.
The goal is to show the large data collected by the company. Furthermore, we have to provide the data in an easily understandable format. It is important to display the key performance indicators in real-time.
You have to contemplate a few things. Like, who will use it? Where will data come from? What will we need exactly? Let’s examine these core premises in detail!
Before building the Dashboard: Questions, Guidelines, and Research
Before taking a deep dive, wait a moment, and spend time on planning and researching phase. Here are a few questions to ask yourself before you start building your dashboard.
- What is the reason you creating the dashboard?
- Will the dashboard serve your business goals?
- What story do you like to tell with your dashboard?
- Where do I find data files to import?
If you have further questions regarding dashboards, check the frequently asked questions.
Everybody knows that data is useless without the ability to visualize it. Let us see the fundamentals!
1. Import your data into Excel
Okay, here we go! To create a dashboard, we need to choose data sources. If the data present in Excel, you are lucky, and you can jump to the next step.
If not, we 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, we are using a CSV file to create the initial dataset for our dashboard.
2. Clean Raw Data
OK, our 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
Want a quick solution to remove leading and trailing spaces? Go to the Formula bar and apply TRIM function. Now copy the formula down.
If you are using data cleansing tools, the time-consuming procedure will be faster and easier.
Tip: Apply simple sorting in Excel to find errors! Sorting just makes life easier. Just sort the data growing or decreasing order. Why? If you are using sorted data, you’ll identify the peaks in a range (highest and lowest data). To do that right-click on the first cell and select the ‘Sort Largest to Smallest’ option from the context menu.
3. Create the ideal Workbook and Worksheet structure
How can you create a consistent workbook structure? What is this mean exactly? Open a workbook and create three sheets. Let us see the parts of the workbook structure:
The first one is the dashboard. This worksheet contains the final visualization. The name of the second one is data. Also, the third one is called calculation. This structure is logical.
From the raw data throughout Excel formulas and functions, you reach the result, your dashboard.
4. Understanding the link between Data and Users
As first, you have to clear up the differences between dashboards and reports. Start with users, not the data. You need to know the user’s requirements. It is under these conditions, and these conditions only will be the dashboard useful.
However, dashboards provide information that poses our questions.
- How are things going?
- How will you explain to your boss the causes of increased profit?
Try to put himself in a user’s mind!
How to create an Excel Dashboard
1. Create an Excel Table and Filter Data
In this phase, you don’t have cleaned input, but you already have data on a worksheet.
What will be the next step?
First, you must check if the necessary information is in a tabular format. The tabular format means that every data, data point lives in one cell, for example, the name of the city, address, or phone number. If the data is in a tabular format, you should convert it into an Excel table.
If you want to convert tabular data into an Excel table, select the range which contains data.
On the Insert Tab, choose Table or press Ctrl + T.
In case your data doesn’t have headers, Excel will automatically insert headers into the first row. Use Excel Table to avoid trouble. If you need more data than you can only expand the table and will not lose the formulas. A very useful tool indeed to use!
2. Analyse and Organize Data
We took you through the procedure that converts unprocessed data into a data structure capable of creating a dashboard.
- Do you have to display all the data at once?
- Is it necessary to remove some data?
From now on, you can use Excel formulas and various methods that help us move forward. To make a useful dashboard, you preferably need creativity than the knowledge of all the formulas.
We’ll use these functions and tools to build the dashboard in Excel: IF, SUMIF, COUNTIF, ROW, NAME MANAGER.
3. Validate, Audit, and check your data
Sometimes nothing works properly in Excel; we are facing missing data points on the charts and unknown formula errors. A little bit scary situation! How to figure out what went wrong? Do not panic! Excel provides useful auditing tools that can help you find and fix Workbook or Worksheet issues.
Use Microsoft Inquiry to get a visual representation of which cells in your Worksheet contribute to a formula error. This step should cut down the time spent on the usual validation procedures.
The Workbook Analysis function creates an interactive report with a single click. You’ll get a detailed overview of the workbook and its structure, include formulas, cells, ranges, and warnings. Find the errors, fix them, finally recheck the given Worksheet.
So, before we start creating a chart, you have to validate the data.
4. Choose the right chart type for your dashboard
Now we have a well-organized, cleaned and error-free data set, it’s time to choose the right chart type. Strike a balance between design and function.
Selecting the right chart type is critical.
Let’s see some useful charting tips. We will show you what kind of graphs is the best for different purposes.
- Do you want to compare values? Charts and graphs are perfect for comparing values. With its help, we can easily show the highest and lowest values in the data sets. Use the following chart types: column, mekko, bar, pie, and line.
- Composition helps us to compare individual parts. The recommended chart types are pie, stacked column, area, or waterfall chart.
- Distribution displays how many times your data falls into each category. The recommended chart type is the histogram.
- Analyze trends to show how performance during a period. You should use a line or column chart or sparklines.
- How to understand the relationships between values. Scatter plot, Bubble, and line charts.
Our goal is to show the percentage of candidates who accepted a job offer each month on a chart. In the example, we’ll use custom combination charts using doughnut charts for displaying key performance indicators. If you want to display a single value use a gauge chart.
Okay, let us select the data for the chart!
5. Select the data and build your Chart
In this phase, we have cleaned and organized data, and we’ve just picked the best chart type for the data. It’s time to select the data! As we mentioned before, 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, we are using two values to show our main KPI, 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%. Select the “Calculation” tab. The cell E23 will show the actual value. The second cell, E24, contains a simple formula and displays the remainder value to 100%.
Make sure that the value in the source cell is in percentage format! Okay, now we are selecting the “Actual Value” and “Reminder Value” data. To create a custom combo chart, open the “Insert Chart” dialog to see a 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.
6. Improve your chart
Now we have a chart that’s fit your data. It looks great, but we can improve it!
Clean up the Chart! Remove the background, title, and borders from the chart area. Select the reminder value section of the outer ring.
Right-click, then choose Format Data Point. Use the ‘No fill’ option. Let’s see the inner ring. Select the actual value section. 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 Text Box. Go to the formula bar and press “=”. Select the actual value and click enter. Once Text Box is connected with actual value, format the text box.
Tip: If you are uncertain about which types of charts are suitable for you, don’t hesitate to choose “Recommended Charts.” In this case, you will get a customized set that Excel thinks will fit best with your data.
7. Repeat the process for other data
A regular Excel dashboard contains various charts and widgets to display data. Repeat the chart insertion and data validation steps for other important metrics, for example, Training effectiveness and 90-day quit rate.
We suggest that you keep your source data in the Data tab and do not remove or hide it. If additional calculations are necessary, use the Calculation Worksheet.
If you want to replace the source data, use the Calculation sheet, not the Data Worksheet!
8. Drill-down your data
At this point, we can create custom views for users using drill-down.
For example, a dashboard that displays sales revenue by year. Drill-down allows the user to select a month. Click on the names of the months to display sales performance by the selected month.
For example, drill-down answers questions such as: which products are performing better in the selected month? If you are working dashboards in Excel, the fastest way of using drill-down is to create a drop-down list.
Select the ranges which contain months that you want to name. Enter a name into the Name Box, for example, “months” and press enter to create a named range.
To insert a drop-down list, select cell W2. Go to the Data tab and click Data Validation. The data validation dialog box will appear. On the “Settings” tab under the “Validation criteria”, select “List”. Make sure that the “ignore blank” is checked.
Finally, link the cell to the values.
9. Additional tips for creating Dashboards
There are several ways in Excel to improve your dashboard. Check out the list below:
- Build a navigation menu and control your dashboard from the ribbon!
- Add a contextual help using tooltips to improve user experience
Use our add-in to rock your Excel Dashboard!
This guide gives you a lot of stuff you can do on your Excel dashboards. The truth is that creating a dashboard in Excel is more than these 7+1 steps.
If you already feel comfortable in the basics of Excel Dashboard, then have a go at it. Try to learn the more advanced formulas and build custom charts. We recommend that you go step by step, and success will follow.
Sometimes we are in a hurry. If you want to build a stunning Excel dashboard in minutes, check out our article about effective data visualization. We hope that you enjoyed our article.