How to Create a Dashboard in Excel - Step-by-Step (2021)
How to Create a Dashboard in Excel – Step-by-Step (2021)
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Imagine how much time you would be able to save if you could look up and track all your important indicators at a glance…
Fortunately, that’s what dashboards are all about.
In this article, you will learn step-by-step on how to create a dashboard in Excel.
Kasper Langmann, Co-founder of Spreadsheeto
Let’s get started!
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.
Table of Content
What are you?
Beginner
Intermediate
Expert
What is an Excel dashboard?
An Excel dashboard is a place where you could track all your business’s important indicators, metrics, and data points using visuals and charts.
Dashboards are often confused with reports. Simply, a dashboard can be a report but not all reports are dashboards.
Dashboards give you a high-level view of how your business is doing and help you make snap decisions based on data.
Kasper Langmann, Co-founder of Spreadsheeto
Like this…
There are three common types of dashboards:
- Strategic dashboards
- Analytical dashboards
- Operational dashboards
All these types of dashboards will help you save time in the analysis and presentation of your data.
Things to consider…
Before you proceed in making an Excel dashboard, you have to pause first and consider some things that will simplify your dashboard.
There are questions you have to ask yourself first such as…
- What is your purpose in creating the dashboard? Clarify your reason for creating a dashboard. Is it to prove a point? Show the overall status of the operation? Or measure the performance of the business?
- What key performance indicators or metrics should you track to fulfill the purpose of your dashboard? To make your dashboard more effective, you have to highlight only the data that are necessary. Including data that’s not important will only service to distract anyone who’s using the dashboard.
- Who will use the Excel dashboard? Is the dashboard for yourself, manager, vendor, executive, or for an analyst? You need to structure the data and tailor the dashboard for the person who will be using the dashboard.
After that, you would also benefit from outlining your dashboard structure. Think of this as creating a skeleton map of your dashboard and identifying what KPI chart or visual to put and where.
Lastly, consider converting your raw data into an Excel table. This allows you to instantly update the table with new information and even filter or reformat them in a few clicks.
If you’re not sure how to do it, check out this awesome guide that will walk you through on how to make an Excel table in 60 seconds.
Kasper Langmann, Co-founder of Spreadsheeto
Excel dashboard templates
Although this article talks about how to create a dashboard in Excel, you should know that you don’t always have to reinvent the wheel.
There are hundreds of templates you can use that can be found online, both free and paid. Some look simplistic with minimal functions while others can be as complicated as it can get.
Whatever your needs are, chances are, someone somewhere also needed a similar dashboard. There’s probably an existing template for that already and all you need to do is to personalize it.
Kasper Langmann, Co-founder of Spreadsheeto
If you need one, check out this article where we identified 7 of the best Excel dashboard templates you can find.
How to create an Excel dashboard?
Of course, if you can’t find a template that fits your needs, you can always create your own from scratch.
This will take more time than simply editing an existing template, but you’re surely more satisfied with a dashboard of your own creation.
Kasper Langmann, Co-founder of Spreadsheeto
To simplify this process, let’s break it down into four steps.
1. Get your data into Excel
For you to be able to create your dashboard in Excel, you have to import your data first. You can either copy and paste the data directly or use an external app to pass the data in real-time.
Then, set up an Excel table with the raw data on another tab. With an Excel table, you will be able to name your rows, columns, and cells however you like. This makes referencing easier later on.
2. Set up your dashboard tab
On this next step, you need to structure your workbook and place the dashboard as the first tab for easy access.
Following the instructions on the first step, you could end up with about three tabs all in all.
Here’s how it looks like:
This makes your Excel file look clean and neat.
3. Analyze your data
One of the things you have to consider is the purpose of your dashboard. What do you want to highlight or show on your dashboard?
It’s important since you have to make an analysis of your data to reflect your purpose.
For that, you will need to use different Excel features and functions such as:
This may seem intimidating at first since you’re looking at a list of features and functions. However, you have to remember that whatever tool you will be using depends on your purpose.
Figure out the purpose of your dashboard first and see what tools (features and functions) you need to use to analyze the data.
4. Create the dashboard
This step could be summarized by simply inserting the charts and visuals you need on your dashboard.
As complicated as it sounds, the final result is a spreadsheet with different visuals that would tell a story about your data and fulfill the purpose of your dashboard.
Like this:
Consider using the new charts in Excel that was released as part of Excel 2016. You could also use a Gantt chart if there’s a timeline involved. Or maybe you can use a chart as simple as a bar graph.
Don’t forget to add customization to your visuals to reflect your brand. You can always change the chart colors, fonts, and even a little bit of Macro to add flair to your dashboard.
That’s it! All you have to do now is to continue refining your dashboard especially when you need to add new data into it.
General dashboard advice and tips
Although some of the recommendations below vary from project to project, these usually apply to most types of dashboards.
Here are things to remember about creating an Excel dashboard:
- Avoid using volatile functions on your workbook such as ‘NOW’, ‘TODAY’ AND ‘OFFSET’ as these will slow down the calculations.
- Keep your dashboard simple and easy-to-understand. It may be tempting to add grandiose effects and graphics, but doing so might make your dashboard difficult to understand.
- Avoid inserting unnecessary visuals on your dashboard. They will add space and will only make your dashboard look complicated.
- Use freeze panes so the user of your dashboard will be able to navigate it more easily. You can free the rows or even the headings so they will always be visible to the user.
- Create a user guide for your dashboard or add shapes or numbers so the user will know which visual to look at first.
Wrapping things up…
Creating an Excel dashboard is one way to increase your productivity with Excel. You will be able to see all the important information about your data at a glance.
If you want to save time, you don’t have to start from scratch. There are a lot of ready-made templates online you can use. You can always modify a template to fit your needs.
But if you must really build a dashboard from scratch, make sure to follow the general advice and tips we included earlier. This will make your dashboard more user-friendly and easier to understand.
Kasper Langmann, Co-founder of Spreadsheeto
"By far this is the best training on the internet."
I've just released a free Excel course that I think fits you perfectly.
It's 3 lessons, got free exercise-files... and you learn about IF, SUMIF, and VLOOKUP (+more).
+50.000 have already tried my training. Now it's your turn!
NEXT
