EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Charts Dashboard in Excel
Secondary Sidebar
Excel Functions
  • Excel Charts
    • Excel Data Visualization
    • Dashboard in Excel
    • Bullet Chart in Excel
    • Excel Animation Chart
    • Plots in Excel  
    • Interactive Chart in Excel
    • Surface Charts in Excel
    • Excel Calendar Template
    • Thermometer Chart in Excel
    • Box Plot in Excel
    • S CURVE in Excel
    • Venn Diagram in Excel
    • Project Management with Gantt Chart
    • Change Chart Style in Excel
    • 3D Scatter Plot in Excel
    • Box and Whisker Plot in Excel
    • Radar Chart in Excel
    • Control Charts in Excel
    • Dot Plots in Excel
    • Marimekko Chart Excel
    • Combo Chart in Excel
    • Contour Plots in Excel
    • Candlestick Chart in Excel
    • Map Chart in Excel
    • Pie Chart Examples
    • Excel Stacked Bar Chart
    • Excel Flowchart
    • Excel Types of Graphs
    • Excel Normal Distribution Graph
    • Excel Bell Curve
    • Legends in Excel Chart
    • Excel Chart Templates
    • Excel Organization Chart
    • Excel Doughnut chart
    • Excel 3D Plot
    • Excel Scatter Chart
    • Excel Line Chart
    • Excel Combination Charts
    • Excel Gauge Chart
    • Excel Pivot Chart
    • Excel Clustered Column Chart
    • Excel Dynamic Chart
    • Excel Stacked Column Chart
    • Excel Bubble Chart
    • Histogram Chart Excel
    • Excel Gantt Chart
    • Excel Stacked Area Chart
    • Excel Waterfall Chart
    • Excel Area Chart
    • Excel Clustered Bar Chart
    • Excel Column Chart
    • Excel Pie Chart
    • Comparison Chart in Excel
    • Pareto Analysis in Excel
    • Add a Secondary Axis in Excel
    • Grouped Bar Chart
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED COURSE
  • Excel Data Analysis Course
  • Excel for Marketing Course

Dashboard in Excel

By Madhuri ThakurMadhuri Thakur

Dashboard in Excel

Dashboard in Excel (Table of Contents)

  • What is Dashboard?
  • How to Create a Dashboard in Excel?

Introduction to Dashboard in Excel

To create a dashboard in Excel, 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, 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 them 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 that 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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Create a Dashboard in Excel?

Let’s understand how to create the Dashboard in Excel with some examples.

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

Example #1 – Create a Dashboard using Data

Suppose we have sales data spread across the months for the past two years (2017 and 2018). Next, we need to create a dashboard using this data.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,768 ratings)

Dashboard in Excel 1-1

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 the Month column to have a comparative view on both sides of the Month column. We will have 2017 Sales on the left-hand side, and on the right-hand side, we will have 2018 Sales data.

Dashboard in Excel 1-2

Step 2: Select all the cells in column A, go to the Conditional Formatting dropdown under the Home tab, and click on the 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.

Dashboard in Excel 1-3

Step 3: As soon as you click on More Rules, a “New Formatting Rule” window will pop up. Where you can define new rules for data bars or can edit the ones already created.

Dashboard in Excel 1-4

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). Then, 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.

Dashboard in Excel 1-5

Step 5: Under Bar Direction, change the direction as Right-to-Left and press the OK key. You’ll see data bars added for 2017 Sales as below.

Dashboard in Excel 1-6

You’ll see data bars added for 2017 Sales as below.

Dashboard in Excel 1-7

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.

left to right context

Now, we are going to use an Excel Camera tool to add the snap of this chart under the dashboard tab. The 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 the Quick Access Menu bar, as shown below.

camera tool

Step 7: Select the data across cells A1 to C13 in the Data tab from your excel and click on the Camera button to take a screenshot of this selected data.

Dashboard Tab

Paste it under the Dashboard tab.

Dashboard output

Example #2 – Using Pivot Table in Excel Dashboard

Let’s take an example for the dashboard using Pivot Table.

Sales value

In this example, we will see how to add the pivot charts along with slicers in the dashboard. Please see below a partial screenshot of the data we are going to use for this example.

Pivot Table

Step 1: Select all the data (A1:E93) and click on the Insert tab. Choose the 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.

Pivot Table 1

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 the values section. See the layout of the pivot as below.

Pivot table fields

Sum of sales pivot table

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 the excel ribbon and click the PivotChart option present under the Tools section to see the variety of chart options to be added under.

Pivot Table Analyze

Step 4: As soon as you click on the PivotChart option, you’ll see a series of chart options available under a new window Insert Chart. Now, click on the Bar button inside the Insert Chart option and select the 3-D Stacked Bar option to have a stacked bar chart. Press the OK key once selected.

Bar Chart

You’ll be able to see the 3-D Stacked Bar chart as below.

Bar Chart in Pivot Table

The best thing about the pivot charts is that you can apply filters to the different column values and modify the graph in real-time. For Ex. you can filter the country and salesperson 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.

pivot table chart

Step 7: Navigate to the Dashboard tab and put the snap at the desired location. Your dashboard should look like the screenshot below.

Sum of sales value

Step 8: Give the names to each screenshot for a better understanding of the user.

Sales diiferent table

This is how we can create decent dashboards for management, and I 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

  • The dashboard is a great way to represent the data in a simpler manner to understand the key parameters without looking into actual messy data.
  • Using Excel’s Camera tool for creating a 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 Dashboard and How to create a Dashboard in Excel, practical examples, and a downloadable excel template. You can also go through our other suggested articles –

  1. KPI Dashboard in Excel
  2. Slicer in Excel
  3. VBA Pivot Table
  4. Excel Pivot Chart
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Data Analysis Training (17 Courses, 8+ Projects)4.8
Excel for Marketing Training (8 Courses, 13+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Dashboard Excel Template

EDUCBA

Download Dashboard Excel Template

EDUCBA

डाउनलोड Dashboard Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more