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.
Types of DashBoards in Excel
- 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? Monitor company operations, activities, and events.
- Informational Dashboards – It is based on facts, figures, and statistics. E.g. Player profile & performance dashboard, flight arrival/departure information dashboard at airports.
Tools Used to Create Excel Dashboard
- Excel tables, Charts, Pivot table & Charts, Slicers in Excel, timelines, Conditional formatting (Data bars, color scale, Icon sets), Named ranges in excel, Data validation in excel, Sparklines in Excel, Macros, Auto-shapes & dashboard widgets.
- Interactive Controls – Scroll bar in excel, radio button, checkbox, Drop Down List in Excel.
- Excel formulas like SUMIF in Excel, OFFSET Excel, COUNT, VLOOKUP, INDEX MATCH & other formulas.
Note: Prior to working on 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? (with Examples)
Example #1 – Comparative 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 Dashboard.
- Step 1: Copy Mankind sales data range in the first column, Month in the second column & Ajantha pharma sales data range in the third column.
- Step 2: 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.
- Step 3: 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.
- Step 4: The 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.
- Step 5: 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.
- Step 6: 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.
- Step 7: 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 a 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 dashboard, we need to summarize sales data for each representative by region wise & quarterly. Therefore, we need to create 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 in excel and click ok. PivotTable Fields pane appears in another sheet.
In the PivotTable Fields pane, drag salesperson 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, salesperson 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 the “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.
The pivot chart appears for “Sales by Region”.
Here you can hide the region, salesperson & sum of sales in pivot chart by right-clicking, and select hide “legend field buttons on the chart” so that those three fields will not appear on a chart.
- Similarly, a pivot chart is applied in the “Sales by Quarter” sheet also, where you can choose a Pie chart for quarterly sales data. Here also you can hide those 3 fields.
An Excel 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 the “Sales by Region” sheet, under the analysis 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 the “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 an individual salesperson.
If you want the sales values to appear on data bars or pie charts. You can add it by click on data bars or pie charts and select “Add data labels”.
Things to Remember
- Key Metrics for monitoring company Sales, finance, productivity and other criteria’s
- The 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
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 –