Dashboards are a powerful way to visualize data. They have become a popular business tool over the years. Analyzing data is easier than ever with the help of an Excel Dashboard. The Excel Dashboard can be very captivating and helps users get an insight on the data just by taking a glance at it. Whether you want to customize your reports or keep track of metrics, these dashboards get the job done effortlessly.
Prerequisite Knowledge
The first and foremost step in building an Excel Dashboard is to be clear about why it is needed. You need to brainstorm and fabricate a design that fulfills the purpose of creating a dashboard.
Here are a few questions you can ask yourself, which will help you design a better dashboard.
- What is the purpose of this Dashboard?
- Who is your target audience?
- What dataset will you be using?
- What do you need to track?
After you understand your requirements, you can design a dashboard that best suits your needs. Let's move ahead and understand more characteristics of an Excel Dashboard.
What Is an Excel Dashboard?
The Excel Dashboard provides an overview of metrics and other data points in one place.
In simple terms, dashboards are visual representations of data.
They mostly consist of charts and graphs, thereby grabbing the user’s attention. Looking at raw Excel data can be boring. Creating a Dashboard in Excel can help you interpret the data by giving an advanced level overview of the same.
- To provide you with a better understanding, look at the following dataset that we will be using in this article to demonstrate how to create Excel Dashboards. The dataset comprises the latest country-wise Coronavirus cases. It has records of
- Confirmed cases
- Recovered cases
- Active cases
- Deaths
- WHO Region of every country.
You can download the dataset by clicking on this link.
Take a look at the gist of the dataset below.
Now, look at the bar graph below that depicts the statistics of COVID cases in the US.
Isn’t it easier to analyze and much better to look at?
In the next section, you will learn how to create an Excel Dashboard by following the step-by-step tutorial below.
Steps to Build an Excel Dashboard
Listed below are the steps that need to be followed to help you understand how to build an Excel Dashboard easily.
Import Data
To create dashboards, we first need to import data in our Excel workbook.
- One way you can import data into excel is by copying and pasting your data into Excel.
- There are numerous other ways to import data into your Excel workbook, depending on your file format. To do this, go to the Data tab → Get & Transform Data section → Get Data option and click on it. You will find a list of various options to import data from different sources.
- You can also make use of connectors so that real-time data is entered into Excel directly.
Filter and Clean Your Data
Before you start working on your data, it is important to make sure the imported data has no errors.
- You should see to it that there are no duplicate values in the dataset. To ensure that the dataset is free from duplicates, select the entire dataset and go to the Data tab and select the Remove Duplicates option in the Data Tools section.
- When you want to work on a particular set of data within the dataset, we can use the Filter option. This option filters out the data to your requirements. To do this, select the whole dataset and go to the Data tab and locate a Filter option.
Once you click on this, you can see an arrow pointing down appearing on the column headers of the dataset. Clicking on this arrow will open up options from which we can select the required data.
In this example, let's filter the data based on a particular WHO Region.
- We can do so by selecting the Filter option on the WHO Region column header.
- You will see a drop-down box with various filters that you can add to the data.
- You can filter the data based on multiple parameters, and also sort it by ascending or descending order.
Here, we select the South-East Asia region. By clicking on OK, you will now view data only from the South-East Asian regions.
Build a Dashboard
Once we are done cleaning and filtering the data to our requirements, we need to copy it and store it in a new Excel sheet. This is done to keep the data organized. If you make any errors, you can retrieve the data from your original dataset. We can create two to three sheets to organize our data by using one sheet for our Dashboard and the other to store our filtered data, which is named here as Dashboard Table.
Select the Data
Once we have the data that is required, we can now start making charts. As you can see, we locate the INSERT TAB → Charts section and select the desired chart.
Once we do that, we can see a blank window open up on our Excel sheet. If you right-click on this blank window, you will find an option to Select Data.
Clicking on this will open up a window called Select Data Source.
- Here, you can add the chart data range by simply dragging your mouse and selecting the required data.
- Now, select the Legend entries (or Vertical axis). In this example, we select the confirmed cases, deaths, recovered cases, and active cases for our Y axis values.
- For the horizontal axis, we select all the countries that we have filtered from the WHO region, i.e., South-East Asia.
After specifying the appropriate values, click on OK. A graph will now be displayed on your worksheet. You can cut and paste it on the Dashboard Excel sheet that was created for organizing the dashboards.
Customize your Chart Accordingly
- To make your charts appealing and attractive, we can change the graph's colors, add text or give more information about it, etc.
- To do this, double click on the chart and use the different customization options available in the toolbar.
- You can also select the option right next to the chart.
- This will open a drop-down box consisting of various Chart Elements.
For this example, let's select the Legend checkbox to display the confirmed cases, deaths, recovered, and active cases on the graph. You can also select the Chart Title box to add a title.
Repeats Steps for the Rest of the Data
An Excel Dashboard includes numerous charts and graphs. So, go ahead and add more visual elements to your Dashboard, as per your choice.
You can use the filter option to filter out the data in your database and form appropriate charts. In the following Dashboard, we have a clustered bar chart depicting the US COVID statistics. You can also find the active cases count based on every WHO Region.
As you can see, these elements together help us track various metrics and make complex datasets easier to analyze. With this, you have reached the end of the process of building your personalized Dashboard from a dataset.
Conclusion
In this article, you would have learned how to create an Excel Dashboard with a step-by-step tutorial. We hope that you are now confident in building one, which will help you visualize your data and customize it based on your needs.
In case of any queries, please mention them in the comments section of “The Best Guide to Build an Excel Dashboard” article, and our experts will get back to you at the earliest!