Table of Contents
What is a Dashboard in Excel?
The dashboard in Excel is an Enhanced form of Data Visualization. It provides insight & analysis which helps out in decision making. It helps out to plan & achieve business targets across the organization. Also, A data source for creating excel dashboards can be Spreadsheets, Text Files, Web Pages, Organizational Database.
Tools Used to Create Excel Dashboard
- Excel tables, Charts, Pivot table & Charts, Slicers, timelines, Conditional formatting (Data bars, color scale, Icon sets), Named ranges, Data validation, Sparklines, Macros, Auto-shapes & dashboard widgets.
- Interactive Controls – Scrollbar, radio button, checkbox, Drop Down List.
- Excel formulas like SUMIF, OFFSET, COUNT, VLOOKUP, INDEX MATCH & other formulas.
Note: Prior to working on excel dashboards, you need to find and remove duplicates, delete leading, trailing or double spaces & remove blanks and errors in the raw data file
How to Create a Dashboard in Excel?
Some of the examples of creating Dashboards in Excel are as follows:
Example #1 – Comparative Excel Dashboard – Sales Performance Of Two Companies
In raw data. I have a two-pharma company with monthly & quarterly sales data. I need to present it & compare the performance of 2 pharma company sales on a month & quarterly basis in Comparative Excel Dashboard.
With the help of Data Bars option under Conditional formatting, we can create comparative performance excel Dashboard.
Let’s create a Comparative excel Dashboard in another sheet with the help of raw data.
Copy Mankind sales data range in the first column, Month in the second column & Ajantha pharma sales data range in the third column.
Select a whole data range and increase the row height & length with the help of alt + HOH key i.e. from 15 to 25, for a better appearance of colored data bars.
Now select the mankind sales data range from Jan to Dec, now go to conditional formatting in the home tab, under that select data bars, and in the data bars, select more data bar rules.
New formatting rule window appears, our sales data range is from 0 to 9000, therefore under the “edit the rule description”, select the type as a number, and enter maximum value i.e. 9000.
In the Bar appearance, select the color of your choice under color options, since the data is on the right-hand side, therefore under the bar direction, select from right to left.
If you don’t want the appearance of sales data on the colored data bars, then select or click on show bar only. Click ok. Now you can see colored data bars appear in each row of sales data on a monthly basis.
Similar steps are followed for Ajantha pharma sales data, only the bar direction, the option needs to be changed i.e. from left to right (Since the data is on the left-hand side)
Note: Simultaneously, these steps are applied for quarterly sales data also, only the maximum number option needs to be changed to 25000 under edit rule description with a different color bar.
Here in sales data, you can check out sales progress, the company has made on a monthly & quarterly basis
Apart from data bars, another type of below-mentioned comparison indicators can also be used based on the dashboard requirement.
Example #2 – Create Sales Performance Excel Dashboard Using Pivotchart & Slicer Object
With the help of PivotChart and Slicer objects, let’s create an excel dashboard where you can easily summarize sales data for each representative.
Below mentioned data contains a compilation of sales information by date, salesperson, and region.
Step 1: Create Table Object
Initially, Data set is converted to a table object, it can be done by, clicking inside the data set then click on the Insert tab then, Select Table.
A create table popup appears, where it shows data range & headers, and click OK.
Once the table object is created, it appears as below mentioned.
Step 2: Pivot Table
In the Excel dashboard, we need to summarize sales data for each representative by region wise & quarterly. Therefore, we need to create the two PivotTables.
- To create a pivot table, for the salesperson by region wise.
In the Tables object, click inside the data set, click on the Insert tab, then select the Pivot table and click ok. PivotTable Fields pane appears in another sheet.
In the PivotTable Fields pane, drag sales person to the Rows section, Region to the Columns section, and sales to the Values section.
A Pivot Table is created for Salesperson by Region wise.
Similarly, create second PivotTable in the same way
- To create a pivot table, for the salesperson by date wise or quarterly.
Drag date to the Rows section, sales person to the Columns section & sales to the Values section
A Pivot Table is created for Salesperson by Date wise.
Here We want to summarize data on a quarterly basis, their dates need to be grouped as “Quarter”. To do that, right-click on any cell in the Row Labels column and choose Group.
Grouping tab appears, with the start date & end date, In the BY list, unselect Months (default value) and others, Now only select Quarters, it appears in blue color after selection, then click OK.
After grouping to quarter, data appears as below mentioned.
Step 3: Pivot Chart
Here, we need to base a PivotChart on each of the created pivot tables in both the sheets.
- Go to the Pivot Table that is created for “Sales by Region” sheet, click inside the PivotTable, under the Analyze tab in a home, select PivotChart.
Insert chart popup window appears, in that Select Bar, under that select Clustered Bar chart.
Pivot chart appears for “Sales by Region”.
Here you can hide the region, sales person & sum of sales in pivot chart by right click, and select hide “legend field buttons on the chart” so that those three fields will not appear on a chart.
- Similarly, pivot chart is applied in “Sales by Quarter” sheet also, where you can choose a Pie chart for a quarterly sales data. Here also you can hide those 3 fields.
A Pie Chart is created for “Sales by Quarter”.
Step 4: Slicers
To check the performance of individual salesperson by region wise & quarterly data, Slicers will help you out
- Go to “Sales by Region” sheet, under the analyze tab. Click Insert Slicer in the Filter group.
Insert slicers window appears, in that select Region field & click OK.
Region wise Slicer will appear.
Similarly, you can add slicers in “Sales by Quarter” sheet also, under the analyze tab. Click Insert Slicer in the Filter group. Insert slicers window appears, in that select salesperson. click OK.
Sales_Person Slicer will appear
Step 5: Dashboard
Create a new sheet, with the name “Sales_DashBoard”, Uncheck or remove gridlines in that sheet in the view option of the home tab for a better appearance of excel DASHBOARD.
Copy these objects i.e. pivot chart & slicer from both the sheet to “Sales_DashBoard” sheet. Now you can analyze sales data by region wise & by individual sales person.
If you want the sales values to appear on data bars or pie chart. You can add it by click on data bars or pie chart and select “Add data labels”.
Types of DashBoards in Excel
Dashboards in excel can be categorized based on their usage & utility as follows –
- Strategic Dashboards – To track KPI (key performance indicators). Used in organizations for key decision making, for business opportunities, focus on future company growth & forecasting e.g. (monthly, quarterly and annual sales)
- Analytical Dashboards – Helps in or to identify current or future trends.
- Operational Dashboards – What is happening in an organization? To Monitor company operations, activities and events.
- Informational Dashboards – It is based on facts, figures, and statistics. E.g. Player profile & performance dashboard, flights arrival/departure information dashboard at airports.
Things to Remember About Creating Dashboard in Excel
- Key Metrics for monitoring company Sales, finance, productivity and other criteria’s
- Excel Dashboard helps out to set up organizational goals
- With the help of an Excel dashboard, you can identify negative trends & correct it immediately
- It helps out in a measurement of Efficiency of an employee based on work productivity & performance
You can also download this Creating Dashboard in Excel Template here – Dashboard Excel Template
This has been a guide to Dashboard in Excel. Here we discuss how to Create a Dashboard in Excel using Conditional Formatting, PivotCharts and Slicers along with practical examples and downloadable excel template. You may learn more about excel from the following articles –