Most of us have a love-hate relationship with Excel. Creating them can feel like a never-ending process that you won’t find any takers for, but when you look at a well-made, formatted Excel sheet or tracker, you can’t help but think - “Wow, I wish I could do that!”
Well, you can!
Creating an Excel dashboard isn’t rocket science even though its complicated formulas may make you feel otherwise. All it takes is some navigation and logic, and you’ll know how to create extensive dashboards on Excel in minutes!
Once you open an Excel worksheet and familiarise yourself with all its tools, say goodbye to your fear of spreadsheets. For instance, if you have a lot of data to work with, and are trying to make it appear more visual for others to understand, you can always rely on the good old Excel dashboards.
Kool Kanya PowerPass’ Excel program takes you through everything you need to know about creating spreadsheets - for instance, formatting data to create elaborate trackers and dashboards. Here’s a sneak peek into how to do that!
Table of Contents
- Introduction
- So, what are Excel dashboards?
- The difference between dashboards and reports
- Steps to create the perfect Excel dashboard
- Conclusion
So, what are Excel dashboards?
Dashboards are powerful tools that help you visualise data. Over the years, these have become quite popular with businesses as they make it easier to analyse data. In layman’s terms, dashboards are visualisation tools that consist of charts and graphs that immediately draw the viewer’s attention.
This is what an Excel dashboard looks like. It’s super-visual and super-informative, and you cannot help but pay attention to everything that’s going on there.
But before we move on to understanding these dashboards better, it’s vital that you don’t mistake an Excel report for a dashboard. These are two completely different things.
The difference between dashboards and reports
- Dashboards: The primary aim of using a dashboard is to display the key performance indicators that are crucial for making important decisions, on a single page. Basically, dashboards are used to show only essential information.
- Reports: These, on the other hand, consist of more pages as they include background data as well. Moreover, a report is a table- or text-based tool and does not usually contain visual elements.
Now that you know the difference between these two terms, let’s look at the best practices to create an Excel dashboard.
Steps to create the perfect Excel dashboard
Step 1: Define the purpose of the dashboard
Before you build an Excel dashboard, you have to establish the purpose. Ask yourself these questions before you start the process:
- What is the purpose of building this dashboard?
- What dataset is to be used?
- Who is the target audience?
- What are you tracking?
Once you have a clearer vision of what you need and have outlined the purpose, it gives you more clarity to design a dashboard that ticks all the boxes.
Step 2: Import data
First things first, you cannot have a dashboard without data. So, you have to import data into your Excel sheet by copying and pasting it. Go to the Data tab > Get & Transform Data Section > Get Data Option and click on it. Here, you’ll find a list of options to import data from different sources.
Step 3: Tidy up your data
In this step, you will have to filter and clean up your data. You need to identify which part is important and remove the unnecessary ones. You will have to check for duplicate values, and if you wish to work on a particular set of data within the data set, use the filter option.
You will have to filter this data and clean it up. Once you get the results, move the selected data to another tab in your worksheet and label it as “chart data.” You will be pulling all the data for your dashboard from this tab.
Step 4: Build the dashboard
- Now, you go to the Insert > Dashboard and select the type of chart you want from the menu.
- Once you select the type of dashboard you want, it will open up a blank window on your excel sheet. Right-click on this blank window and you will find the “select data” option.
- Now, click Add in Legend Entries (Series).
- In the Series name field, click the title of the column you want to add to the Raw Datasheet and Hit enter.
- In the Series values field, select all the data in that corresponding column. Hit enter and then click Ok.
- You’ll notice that your X-axis is not correctly labelled. To fix this, click Edit in the Horizontal (Category) Axis Labels and in the Raw Data Sheet, select what you’d like to display on the X-axis.
- To add a title to your chart, select your chart and click the Design tab.
- Click Add Chart Element > Chart Title > Above Chart.
- Add a title to the Excel chart
- Type your title in the text field on the chart.
- Repeat this process for any other charts you want to create.
And this is how your Excel dashboard will look!
Excel dashboards are excellent tools that help you visualise your data and present it in a manner that makes it easier for the audience to understand. So, now that you know how to make an excellent dashboard, you will definitely WOW your workmates with impressive dashboards.
While you can use Excel to make dashboards, you will still need a full-fledged presentation to get a client on board or for other purposes. Learn more about the ingredients of a good PPT here.
Disclaimer: You may be reading this blog after the current learning course has lapsed. Head over to our all courses page to check for the latest courses and upskill yourself!
