Dashboard in Excel (Table of Contents)
Introduction to Dashboard in Excel
To create a dashboard in Excel first we have to create a pivot table using the data. For each visual, we must have one single pivot table. After that drag and place the pivot table and create a number of sheets as per need. Then insert create different visuals using different chart types from the Chart section of the Insert menu tab. Once we have created and named each Chart. Once the charts are created then, cut all the charts from the respective sheet and place it in the sheet for the final dashboard. We can even insert the slicers as well for the final dashboard.
What is Dashboard?
A dashboard is a visual representation of data. It is a process in which you take all the efforts to make your complex data look easier to understand and manage through some visual techniques. There are different Excel tools can be used to create a dashboard. Some of those are:
Bar Chart, Histograms, Pie Chart, Line Chart, Combo Charts, Pivot Table, Slicer, KPIs, etc. These are the tools used in which we can create a dashboard and make the usually complex looking data simpler for understanding.
How to Create a Dashboard in Excel?
Let’s understand how to create the Dashboard in Excel with some examples.
Example #1 – Create a Dashboard using Data
Suppose we have sales data for the past two years (2017 and 2018) spread across the months. We need to create a dashboard using this data.
We are going to add Data Bars for this data and see the comparison across the sales for the past two years. For that, follow the steps below:
Step 1: Cut the column named 2017 Sales and paste it before Month column so that we will have a comparative view on both sided of Month column. On the left-hand side, we will have 2017 Sales and on the right-hand side, we will have 2018 Sales data.
Step 2: Select all the cells in column A and go to Conditional Formatting dropdown under the Home tab and click on Data Bars navigation option. There, you’ll see a series of options for data bars. Out of all those, select More Rules and click on it.
4.9 (3,745 ratings)
View Course
Step 3: As soon as you click on More Rules… a window named “New Formatting Rule” will pop-up. Where you can define new rules for data bars or can edit the ones already created.
Step 4: You can see different rules available. Choose the one with the name “Format all cells based on their values” as a rule (It is selected by default as it is the first rule in the list). Under Edit the Rule Description: change the minimum and maximum values as shown in the screenshot below and also change the bar colors under Bar Appearance.
Step 5: Under Bar Direction change the direction as Right-to-Left and press OK key. You’ll see data bars added for 2017 Sales as below.
You’ll see data bars added for 2017 Sales as below.
Step 6: Do the same for 2018. Just make a change for Bar Direction: as Left-to-Right. It should look like the screenshot below.
Now, we are going to use an Excel Camera tool to add the snap of this chart under the dashboard tab. Camera tool can be activated by clicking on File – Options – Quick Access Toolbar – choose a command from a tab -select All Commands – Camera option- Add and OK. Once enabled/added on the main ribbon, you can see a Camera button at Quick Access Menu bar as shown below.
Step 7: Select the data across cell A1 to C13 in Data tab from your excel and click on the Camera button to take a screenshot of this selected data.
Paste it under the Dashboard tab.
Example #2 – Using Pivot Table in Excel Dashboard
Let’s take an example for dashboard using Pivot Table.
Now, in this example, we are going to see how to add the pivot charts along with slicers in the dashboard. Please see below partial screenshot of data we are going to use for this example.
Step 1: Select all the data (A1:E93) and click on the Insert tab. Choose PivotTable option in the list of options available to insert. It will open up Create PivotTable window. Select the New Worksheet for generating the Pivot table and click on the OK button.
Step 2: Now, mold the pivot as per your requirement. I will add the Sales Person on rows, Country on columns and Sales Value under values section. See the layout of the pivot as below.
We are now going to add the pivot chart under our dashboard using this pivot table as a data source.
Step 3: Click on Analyze tab on excel ribbon and click the PivotChart option present under the Tools section to see the variety of chart options to be added under.
Step 4: As soon as you click on PivotChart option, you’ll see a series of chart options available under a new window Insert Chart. Now, inside Insert Chart option, click on Bar button and select the 3-D Stacked Bar option to have a stacked bar chart. Press OK key once selected.
You’ll be able to see the 3-D Stacked Bar chart as below.
The best thing about the pivot charts is, you can apply filters to the different column values and get the graph modified in real-time. For Ex. you can filter the country as well as Sales Person names in the graph itself and the graph will update as per your selections. This makes your dashboard more compact in nature.
Step 6: Select the Chart area and click on the Camera button to capture it.
Step 7: Navigate to the Dashboard tab and put the snap at the desired location. Your dashboard should look like the screenshot below.
Step 8: Give the names to each screenshot for a better understanding of the user.
This is the way we can create decent dashboards of our own for management and have said that, this is the end of this article. Let’s wrap the things with some points to be remembered.
Things to Remember About Dashboard in Excel
- Dashboard is a great way to represent the data in a simpler manner so that the user can understand the key parameters without having a look into actual messy data.
- Using Excel’s Camera tool for creating dashboard is a great way. The reason is, the camera tool uses the reference of all the cells and is not only a snap. Therefore, whatever changes you make in the data and graphs, will auto reflect under the dashboard and you don’t need to copy and paste graphs on multiple times. This means it adds flexibility to the dashboard.
Recommended Articles
This is a guide to Dashboard in Excel. Here we discuss What is Dashboard and How to create a Dashboard in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
