How to Create an Excel Dashboard in Steps

Published 18 days ago
How to Create an Excel Dashboard in Steps

What is an Excel Dashboard?

First, let’s begin with the basics. The easy style to think of a dashboard is a visual representation of data.
Raw data can be hard to look at. Sure, the need-to-know digits are there. But, all of those rows and columns are generally impossible to process and understand.
This is where dashboards come into play. They turn data into information by creating various charts, tables, and other visual elements that give you a high-level overview of that data.
A dashboard clarifies that otherwise complex data you have in your spreadsheet and transforms it into something visual that is far simpler for you to grasp and, thus, utilize.
Needless to say, dashboards have a wide array of uses—from budgeting or project management to marketing or sales reporting.
Read More : Microsoft Excel Interview Question Answers

Before building the Dashboard: what you should know :

Data visualization sounds like a real party, does not it? We can tell that you are ready and raring to get started building your individual dashboards.
However, before doing so, it is important that you lay the appropriate groundwork first. Here are a few things you will want to do before getting started on a dashboard:
1. Import your data into Excel :
In order to create a dashboard, your data first requires to exist in Excel. If it is already there? Great—there is nothing more you require to do with this step.
But, if not? You will require to import it into an Excel workbook.
There are various styles to do this with ranging complexities—confide on where your data exists presently. So, your best bet is to research how to import your definite data format.
2. Clean your data :
When working with data within Excel, it is important that every piece of information lives within its individual cell.
If your existing spreadsheet is a bit of a jumbled mess, take some time to clean it up and ensure that things are organized into their appropriate rows and columns. It is also wise to briefly consider your data and make sure that no glaring typos or errors jump out at you.
Now is also an excellent time to search for any duplicate information that needs to be deleted, because each row of data needs to be unique in order to utilize the dashboard feature—otherwise you’ll be double counting.
Want a quick style to search for duplicates? Highlight your entire dataset and then click the “Remove Duplicates” button.
3. Set up your workbook :
To create a dashboard, you are going to require three separate sheets (or tabs) within your Excel workbook.
Name your first tab with something you will immediately recognize—like “Data” or “Raw Data.”
Then, create a second tab labeled “Chart Data.” That tab is where you will store only the data that needs to be fed into different charts for the dashboard. certainly, create a tab labeled “Dashboard” where your various charts will appear.
You can leave those last two tabs absolutely blank for now! The important part is just to get your workbook set up and ready to work with.
4. Understand your requirements :
When you start familiarizing yourself with dashboards in Excel, you will rapidly realize that there are tons of options. absolutely, that can be overwhelming—which is why it is significant to get clear on the why of your dashboard first.
What is your target with this dashboard? Do you require to track progress? Analyze a budget? Identify trends?
By considering that first and foremost—as well as things like who you’ll need to share this with and what format it will need to be in—you will be empowered to design a dashboard that fits your needs.

Now we discuss here how to create an Excel Dashboard :

1. Figure out which charts best represent your data :
You know how we just said you’ll be faced with tons of options to represent your data? We weren’t kidding—there are bar charts, column charts, pie charts, line charts, scatter plots, waterfall charts, and so many more.
However, not all of them will be the best fit for the data that you want to represent. For example, a line chart is excellent for analyzing trends while a pie chart is effective for looking at a snapshot in time.
2. Filter your data :
The simplest way to do this is by using the “Filter” option within Excel. For example, we want to filter by item type and only see numbers related to our electricity expenses.
To do that, we will highlight the entire data set, click the “Data” ribbon in the toolbar, and then click the “Filter” button. When doing so, you will see that little arrows appear next to your column headers. If you click one of those arrows, you will be presented with a drop-down menu that you can use to filter your data.
3. Build your chart :
Now that you have only the data that you need, you are ready to begin building your chart.
Click on the “Dashboard” tab of your worksheet, click the “Insert” button in the toolbar, and then select the type of chart you want from the menu. In this case, we are going to use a clustered column chart.
When you insert the chart, you will see a blank box. Don’t worry—you have not screwed anything up. We will cover how to get your data to appear there in the next step.
4. Select your data :
Now that you have that blank box inserted in your “Dashboard” tab of your workbook, it is time to pull some data in.
To do so, right-click on that box and then choose “Select Data.” After that, navigate over to your “Chart Data” tab and highlight all of the data that you want to display—minus the column headers. In this case, we are selecting the “Item” and “Cost” data, since the aim of my chart is to show how much we spend on electricity each month.
That data that you just selected is for the vertical axis. But, you still require to select your data for the horizontal axis of your chart.
To do that, click the button within the “Horizontal Axis” field of the “Select Data” popup and then highlight the information you need for the horizontal axis—in this case, the months.
After doing so, hit your “Enter” key, click “OK,” and then head over to your “Dashboard” tab to ensure that your chart populated with that data.
5. Double-check your data :
At this point, it is wise to take a quick look at your chart and make sure that nothing looks off. Mistakes can happen. So, it is worth it to take the time to assure that your chart is pulling in your data correctly.
6. Polish your chart :
Here is the fun part—when you get to put the finishing touches on your chart.
From changing colors to match your brand to adding labels, titles, units, or any other information that is required, you can polish up your chart by double-clicking on the chart area and then using the options in the toolbar to make your changes.
7. Repeat the process for other data :
A true Excel dashboard consists of various different charts and gauges to display data. So, to complete our dashboard in this example scenario, we will go back and repeat all of these steps for our other budget line items—like phone, vehicle, gas, etc.
One important thing to note: You do not want to delete any data in your “Chart Data” tab—since the data there, is what is feeding your charts.
So, when going through the procedure of making your other charts, make sure to paste the new data sets below each other, rather than deleting and replacing them within that tab :
Ready to get rolling with Excel Dashboards?
We have covered a lot about Excel dashboards here, and we are hopeful that you have some newfound confidence to leverage Excel so that you can visualize and utilize that data of yours.If you want to learn more about How to create Dashboard then Advance Excel Training in Chandigarh is the right place for you.
Pratibha Sharma
English Speaking Course in Chandigarh - Marketer