Excel Dashboards

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

  1. 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)
  2. Analytical Dashboards – Helps in or to identify current or future trends.
  3. Operational Dashboards – What is happening in an organization? Monitor company operations, activities, and events.
  4. 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

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)

You can download this Dashboard Excel Template here – Dashboard Excel Template

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.

Dashboards Example 1

  • 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.

Dashboards Example 1-1

  • 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.

Dashboards Example 1-2

  • 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.

Dashboards Example 1-3

  • 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 a maximum value, i.e., 9000.

Dashboards Example 1-4

  • 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.

Dashboards Example 1-5

  • Step 6: If you don’t want the appearance of sales data on the colored data bars, then select or click on the show bar only. Click ok. Now you can see colored data bars appear in each row of sales data on a monthly basis.

Dashboards Example 1-6

  • 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)

Dashboards Example 1-7

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.

Dashboards Example 1-8

Apart from data bars, another type of below-mentioned comparison indicators can also be used based on the dashboard requirement.

Dashboard indicators

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.

Dashboards Example 2

  • 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.

Dashboards Example 2-1

A create table popup appears, where it shows data range & headers, and click OK.

Dashboards Example 2-2

Once the table object is created, it appears as below mentioned.

Dashboards Example 2-3

  • 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.

Dashboards Example 2-4

 

In the PivotTable Fields pane, drag salesperson to the Rows section, Region to the Columns section, and sales to the Values section.

Dashboards Example 2-5

A Pivot Table is created for Salesperson by Region wise.

Dashboards Example 2-6

Similarly, create the 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

Dashboards Example 2-7

A Pivot Table is created for Salesperson by Date wise.

Dashboards Example 2-8

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.

Dashboards Example 2-9

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.

Dashboards Example 2-10

After grouping to quarter, data appears as below mentioned.

Dashboards Example 2-11

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.

Dashboards Example 2-12

Insert chart popup window appears, in that Select Bar, under that select Clustered Bar chart.

Dashboards Example 2-13

The pivot chart appears for “Sales by Region.”

Dashboards Example 2-14

Here you can hide the region, salesperson & sum of sales in the 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.

Example 2-15

Example 2-16

  • 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.

Example 2-17

An Excel Pie Chart is created for “Sales by Quarter.”

Example 2-18

  • Step 4: Slicers

To check the performance of individual salesperson by region wise & quarterly data, Slicers will help you out

  1. Go to the “Sales by Region” sheet, under the analysis tab. Click Insert Slicer in the Filter group.

Example 2-19

Insert slicers window appears, in that select Region field & click OK.

Example 2-20

Region-wise, Slicer will appear.

Example 2-21

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.

Example 2-22

Sales_Person Slicer will appear

Example 2-23

  • 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.

Example 2-24

Copy these objects, i.e., pivot chart & slicer from both the sheet to the “Sales_DashBoard” sheet. Now you can analyze sales data by region wise & by an individual salesperson.

Example 2-24

Suppose 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.”

Dashboard Example 2-25

Dashboard Example 2-26

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

Recommended Articles

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 a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>