0% found this document useful (0 votes)
67 views31 pagesDashboards Types - MS Excel
The document is a tutorial on creating Excel dashboards, which are visual displays of data that help in monitoring key performance indicators and making informed decisions. It covers various Excel features that facilitate the creation of dynamic and interactive dashboards, including tables, charts, and conditional formatting. The tutorial is designed for users with a basic understanding of Excel and aims to enhance their skills in data analysis and visualization.
Uploaded by
patnamrythmsCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
67 views31 pagesDashboards Types - MS Excel
The document is a tutorial on creating Excel dashboards, which are visual displays of data that help in monitoring key performance indicators and making informed decisions. It covers various Excel features that facilitate the creation of dynamic and interactive dashboards, including tables, charts, and conditional formatting. The tutorial is designed for users with a basic understanding of Excel and aims to enhance their skills in data analysis and visualization.
Uploaded by
patnamrythmsCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 31
Excel Dashooard
E vt
a9
tutorialspoint
SIMPLY EASY LEARNING
i Re
www.tutorialspoint.com
EF} httes://www.facebook.com/tutorialspointindia J) https://twitter.com/tutorialspointExcel Dashboards
About the Tutorial
Dashboards are popular visual displays of data, mostly comprising of charts / graphs with
striking attention seeking components, There are various tools available in the market to
create dashboards. If you are a Microsoft Office user with reasonably good mastery on
Excel, then creating dashboards in Excel is a wise decision. This is because Microsoft has
introduced several powerful features in Excel, making your job of handling large datasets
from various data sources simple and less tiresome.
In this tutorial, you will learn how to use Excel features effectively in dashboards. They
include features that can make a dashboard dynamic and interactive.
Audience
This tutorial has been designed for all those readers who depend heavily on MS-Excel to
prepare charts, tables, and professional reports that involve complex data. It will help all
those readers who use MS-Excel regularly to analyze data.
Once you get an understanding of the several Excel features that come handy in creating
Excel dashboards, creating dashboards will become a trivial task for you.
Prerequisites
Before proceeding with this tutorial, the reader should have a preliminary understanding
of Excel workbooks, Excel charts, Excel PivotTables, Excel Data Model, Excel Power
PivotTables and Power PivotCharts and Excel Power View reports. All these topics are
available as full-fledged tutorials in our tutorials library.
Copyright & Disclaimer
© Copyright 2016 by Tutorials Point (1) Pvt. Ltd.
All the content and graphics published in this e-book are the property of Tutorials Point (1)
Pvt, Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish
any contents or a part of contents of this e-book in any manner without written consent
of the publisher.
We strive to update the contents of our website and tutorials as timely and as precisely as
possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.
Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our
website or its contents including this tutorial. If you discover any errors on our website or
in this tutorial, please notify us at [email protected]
QpererisersinsExcel Dashboards
Table of Contents
About the Tutorial
Audience
Prerequisites.
Copyright & Disclaimer...
‘Table of Contents
1, Excel Dashboards — Introduction.
Dashboard ~ Definition.
Key Metrics for Dashboard,
Dashboard Benefits
‘Types of Dashboards
Dashboard Data and Formats.
A
2
2
3
3
6
6
Live Data on Dashboards.
2. Excel Dashboards — Excel Features to Create Dashboards.
Excel Tables
Sparklines
Conditional Formatting sentient sennnenntnennnnnn
ExCe! Chat ts sonsnsnininninininninininininninmninnnmninninmnnnnnnnnmnnnnnnmnnnnn
Excel Camera sninsninnnininnnininnnnninnnnnmnnnnnnnnmnnnnnnnnmnnnninnnnnnnnnnnsne SS
Excel PivotTables 15
Dynamic Dashboard Elements with Interactive Controls. v7
Excel Power PivotTables and Power PivotCharts 18
Excel Power View Reports. 19
Key Performance Indicators (KPIs) 20
3. Excel Dashboards — Conditional Formatting...
Highlighting Cells.
Top / Bottom Rules...
Data Bars
Color Scales
leon Sets
Using Custom Rules
Managing Conditional Formatting Rules.
4, Excel Dashboards — Excel Charts
‘Types of Charts
Selecting the Aopropriate Chart Type ... '
Showing Trends with Sparklines in TableS.....:ennnnmninnninninnnnnnnnnnnnnnnnnnne 8
Using Combo Charts for Comparisons
Fine Tuning Charts Quickly
Using Aesthetic Data Labe's
Using Trendlines in Charts.
Using Shapes in Charts
Using Cylinders, Cones, and Pyramids...
Using Pictures in Charts.
5. Excel Dashboards — Interactive Controls.
Scroll Bars in Dashboards...
Creating a Scrollbar... sonnnnnnn
Creating a Dynamic and Interactive Target Line
Qpererisersins10.
a.
Excel Dashboards
Excel Option (Radio) Buttons. 3
Excel Checkboxes. ai
Excel Dashboards — Advanced Excel Charts.
‘Types of Advanced Excel Charts
Displaying Quarterly Performance with Bullet Charts. 91
Displaying Profit % Region-Wise with Waffle Charts.....n:nnininninninnninnninnninennnnnnne SL
Excel Dashboards — Excel PivotTables ..
Creating a PivotTable...
Filtering Data in PivotTable
Using Slicers in PivotTable,
Excel Dashboards ~ Power PivotTables & Power PivotChart 103
Uses of Power PIVOt nnn o nnn 103
Differences between PivotTable and Power PivotTable 103,
Creating a Power PivotTable 103
Creating a Power PivotChart 107
Table and Chart Combinations 12
Hierarchies in Power Pivot se 13
Caeulations Using Hierarchy in Power PivotTables 17
Drilling Up and Drilling Down a Hierarchy .nnmmennneninnninnnnnnnnnnnnrnnennenenenne LD
Using a Common Slicer 121
Aesthetic Reports for Dashboards. 125
Excel Dashboards ~ Power View Reports
Power View Visualizations. 27
Combination of Power View Visualizations. 131
Interactive Nature of Charts in Power View Visualizations. 132
Slicers in Power VieWnsnnnennnnnnnnnnnninnn son sone DBZ
Tiles in Power Viewer 133
Power View Reports sannnnmnmninennninninennnininnnininnnnnnnnnnnnnnnnnennnenne dT
Excel Dashboards — Key Performance Indicators. 138
Components of a KPI 138
Base Value .. 138
Target Value senna nnn 139
Status Thresholds and Status 139
Defining KPIs in Excel 139
Visualizing KPIs with Bullet Charts... o ns ns 139
Visualizing KPIs with Power View. 140
Excel Dashboards — Build a Dashboard. 142
Initial Preparation. 142
Organize the Data Source for the Excel Dashboard snntnnininnnninnnnnnnnnnnnsnnnne D3
Set Up the Excel Dashboard Workbook... 143
Prepare the Data for the Excel Dashboard... 143,
Select the Dashboard Components. 143
Identify Parts of the Dashboard for Highlighting. 144
Build the Dashboard 144
Using Excel Camera 145,
Date and Time Stamp on Excel Dashboard sninnninnnnennnnnnnnnnrnnne VOB
Test, Sample, and Enhance the Dashboard wn..nnmennnmnennnennnnnninnniannmnnnnnnnennne dD
Qpererisersins2.
Excel Dashboards
Share the Dashboard 150
Tips for Effective Excel Dashboards 150
Excel Dashboards — Examples 153
Example — Executive Dashboard 153,
Example — Project Management Dashboard. 154
Example Sales Management Dashboard ...ur.unnennn seinen 1S
Example ~Training Management Dashboard ...u.u1ninmnnininnninnnnnnnnnnnnnnrenene 56
Example Service Management / Support Dashboard 187
Dashboards — More Example. 158
iv1. Excel Dashboards — Introduction
For those who are new to dashboards, it would be ideal to get an understanding of the
dashboards first. In this chapter, you will get to know the definition of dashboard, how it got
its name, how they became popular in IT, key metrics, benefits of dashboards, types of
dashboards, dashboard data and formats and live data on dashboards.
In information technology, a dashboard is an easy to read, often single page, real-time user
interface, showing a graphical presentation of the current status (snapshot) and historical
trends of an organization's or department's key performance indicators to enable
instantaneous and informed decisions to be made at a glance.
Dashboards take their. name from
automobile dashboards. Under the hood of
your vehicle, there may be hundreds of
processes that impact the performance of
your vehicle. Your dashboard summarizes
these events using visualizations so that you
have the peace of mind to concentrate on
safely operating your vehicle. In a similar way,
business dashboards are used to view and/or
monitor the organization's performance with
ease.
The idea of digital dashboards emerged
from the study of decision support systems in
the 1970s, Business dashboards were first developed in the 1980s, but due to the problems
with data refreshing and handling, they were put on the shelf. In the 1990s, the information
age quickened pace and data warehousing, and online analytical processing (OLAP) allowed
dashboards to function adequately. However, the use of dashboards did not become popular
until the rise of key performance indicators (KPIs), and the introduction of Robert S. Kaplan
and David P. Norton's Balanced Scorecard. Today, the use of dashboards forms an important
part of decision making.Excel Dashboards
ote ir coe
iowa Arta val ents ani ais THAN un ae Asa AN
tomares
In today’s business environment, the tendency is towards Big Data, Managing and extracting
real value from all that data is the key for modern business success. A well-designed
dashboard is a remarkable information management tool.
Dashboard — Definition
Stephen Few has defined a dashboard as “a visual display of the most important information
needed to achieve one or more objectives which fits entirely on a single computer screen so
it can be monitored at a glance”
In the present terms, a dashboard can be defined as a data visualization tool that displays
the current status of metrics and key performance indicators (KPIs) simplifying complex data
sets to provide users with at a glance awareness of current performance.
Dashboards consolidate and arrange numbers and metrics on a single screen. They can be
tailored for a specific role and display metrics of a department or an organization on the
whole.
Dashboards can be static for a one-time view, or dynamic showing the consolidated results
of the data changes behind the screen. They can also be made interactive to display the
various segments of large data on a single screen.Excel Dashboards
Key Metrics for Dashboard
The core of the dashboard lies in the key metrics required for monitoring. Thus, based on
whether the dashboard is for an organization on the whole or for a department such as sales,
finance, human resources, production, etc. the key metrics that are required for display vary.
Further, the key metrics for a dashboard also depend on the role of the recipients (audience).
For example, Executive (CEO, CIO, etc.), Operations Manager, Sales Head, Sales Manager,
etc. This is due to the fact that the primary goal of a dashboard in to enable data visualization
for decision making
The success of a dashboard often depends on the metrics that were chosen for monitoring.
For example, Key Performance Indicators, Balanced Scorecards and Sales Performance
Figures could be the content appropriate in business dashboards.
Dashboard Benefits
Dashboards allow managers to monitor the contribution of the various departments in the
organization. To monitor the organization's overall performance, dashboards allow you to
capture and report specific data points from each of the departments in the organization,
providing a snapshot of current performance and a comparison with earlier performance,
Benefits of dashboards include the following -
+ Visual presentation of performance measures
+ Ability to identify and correct negative trends.
+ Measurement of efficiencies/inefficiencies.
+ Ability to generate detailed reports showing new trends,
‘+ Ability to make more informed decisions based on collected data.
+ Alignment of strategies and organizational goals.
+ Instant visibility of all systems in total
+ Quick identification of data outliers and correlations.
+ Time saving with the comprehensive data visualization as compared to running
multiple reports,
Types of Dashboards
Dashboards can be categorized based on their utility as follows -
+ Strategic Dashboards
‘+ Analytical Dashboards
+ Operational Dashboards
+ Informational Dashboards
Qereristers tExcel Dashboards
Strategic Dashboards
Strategic dashboards support managers at any level in an organization for decision making.
They provide the snapshot of data, displaying the health and opportunities of the business,
focusing on the high level measures of performance and forecasts.
+ Strategic dashboards require to have periodic and static snapshots of data (e.g. daily,
weekly, monthly, quarterly and annually). They need not be constantly changing from
one moment to the next and require an update at the specified intervals of time.
= They portray only the high level data not necessarily giving the details.
+ They can be interactive to facilitate comparisons and different views in case of large
data sets at the click of a button. But, it is not necessary to provide more interactive
features in these dashboards.
The following screenshot shows an example of an executive dashboard, displaying goals and
progress.
Analytical Dashboards
Analytical dashboards include more context, comparisons, and history. They focus on the
various facets of data required for analysis.
Analytical dashboards typically support interactions with the data, such as drilling down into
the underlying details and hence should be interactive.
Examples of analytical dashboards include Finance Management dashboard and Sales
Management dashboardExcel Dashboards
=“ comeytaroy vate
sz teat Vass wes
Papen TT = | ee ” ones
Vote ans Papes vty Our etry
edison a
102
% ark
ena a
102 si
£19200
. tan
Operational Dashboards
Operational dashboards are for constant monitoring of operations, They are often designed
differently from strategic or analytical dashboards and focus on monitoring of activities and
events that are constantly changing and might require attention and response at a moment's
notice. Thus, operational dashboards require live and up to date data available at all times
and hence should be dynamic.
An example of an operation dashboard could be a support-system dashboard, displaying
live data on service tickets that require an immediate action from the supervisor on high-
priority tickets.Excel Dashboards
Informational Dashboards
Informational dashboards are just for displaying figures, facts and/or statistics. They can be
either static or dynamic with live data but not interactive. For example, flights
arrival/departure information dashboard in an airport.
eur cs 32 PM GMT+1
10Excel Dashboards
Dashboard Data and Formats
The data required for a dashboard depends on its category. The premise for the data is that
it should be relevant, error-free, up to date and live if required. The data can possibly be from
various and different sources and formats (Spreadsheets, Text Files, Web Pages,
Organizational Database, etc.).
The results displayed on a dashboard must be authentic, correct and apt. This is crucial since
the information on 2 dashboard would lead to decisions, actions and/or inferences. Thus,
along with the data being displayed, the medium chosen for the display is equally important
as it should not give an erroneous impression in the data portrayal. The focus should be on
the ability of the data visualization that would unambiguously project the conclusions.
Live Data on Dashboards
As discussed earlier in this chapter, data warehousing and online analytical processing (OLAP)
is making it possible to refresh the dynamic dashboards instantly with live data. It is also
making those who design the dashboards be independent of the organization's IT department
for obtaining data,
Thus, the dashboards have become the most sought after medium from top management to
a regular user.
it2. Excel Dashboards — Excel Features to Create
Dashboards
You can create a dashboard in Excel using various features that help you make data
visualization prominent, which is the main characteristic of any dashboard. You can show data
in tables with conditional formatting to highlight the good and bad results, you can summarize
the data in charts and PivotTables, you can add interactive controls, and you can define and
manage KPIs and so on.
In this chapter, you will get to know the most important Excel features that come handy when
you are creating a dashboard. These features help you arrive at the dashboard elements that
simplify complex data and provide visual impact on the current status or performance in real
time.
Excel Tables
The most important component of any dashboard is its data. The data can be from a single
source or multiple sources. The data might be limited or might span several rows.
Excel tables are well suited to get the data into the workbook, in which you want to create
the dashboard. There are several ways to import data into Excel, by establishing connections
to various sources. This makes it possible to refresh the data in your workbook whenever the
source data gets updated.
You can name the Excel tables and use those names for referring your data in the dashboard.
This would be easier than referring the range of data with cell references. These Excel tables
are your working tables that contain the raw data.
You can arrive at a summary of the analysis of data and portray the same in an Excel table
that can be included as a part of a dashboard.
A B c D E
1 % Profits Region-wise and Quarter-wise
2 Qtra. Qtr2. Qtr Qed
3 East 87% 90% 79% 96%
4 North 92% 94% 85% 97%
5 South 88% 95% 75% 80%
6 West. 85% 87% 87% 88%
Analysis Summary in an Excel table
12
tutorialspointExcel Dashboards
Sparklines
You can use Sparklines in your Excel tables to show trends over a period of time. Sparklines
are mini charts that you can place in single cells. You can use line charts, column charts or
win-loss charts to depict the trends based on your data.
A 8 c D E F
1 % Profits Region-wise and Quarter-wise
2 Qe ‘atr2 Qte3 Qtra
a East 87% 90% 79% 96%
4 North 92% 94% 85% 97%
5 ‘South 88% 95% 75% 80%
6 West | 85% 87% 87% 88%
Sparklines with Column Charts
Conditional Formatting
Conditional formatting is a big asset to highlight data in the tables. You can define the rules
by which you can vary color scales, data bars and/or icon sets. You can either use the Excel
defined rules or create your own rules, based on the applicability to your data.
A D
Conditional Formatting with Color Scales
13Excel Dashboards
A B c D E
1[ % Profits Region-wise and Quarter-wise 1
2 Qtri Qtr2 Qtrs tra
3 East
4 North
5 South
6 West
Conditional Formatting with Data Bars
A B c D E
fl % Profits Region-wise and Quarter-wise
2 Qtrt Qtr2 Qtrs Qtra
3| East Be 67% 85% [> 79% 96%
4 North lt 92% ft 94% [Ge 65% Ge 60%
5 South 71% (it 85% [fF 90% [> 80%
6 West 55% 67% [=> 71% ft 88%
Conditional Formatting with Icon Sets
You will learn these conditional formatting techniques in the chapter — Conditional
Formatting for Data Visualization.
Excel Charts
Excel charts are the most widely used data visualization components for dashboards. You can
get the audience view the data patterns, comparisons and trends in data sets of any size
strikingly adding color and styles.
Excel has several built-in chart types such as line, bar, column, scatter, bubble, pie, doughnut,
area, stock, surface and radar if you have Excel 2013.
14
tutorialspointExcel Dashboards
a9gg3a3
2
2: Clustered Column
salt
100% stacked Column
2.0 Stacked Column
3-0 Column chart
‘tutorialspoint
15Excel Dashboards
LUne Chart without Mackers Line Chart with Markers
stacked Line with Markers
auupoggaeed
euenogagoa8
fom eam ene emt ee
16Excel Dashboards
Doughnut Chart
2-0 Pie Chart
‘Stacked Bar Chart
Clustered Bar Chart
—
Area Chart Stacked Area Chart
‘Scatter Chart ‘Scatter with Smooth Lines and Markers
7Excel Dashboards
Bubble Charts
au ia
ee = ee
e = @
e pen
@ ® e? = @ e @ 3
‘Stock Charts.
vec eae
| — po 1 a +
t amet '
vemane = vans as aaRse aaa AS. ne aie ae Ne eR
SS a
ia aaearo
i ! = =P patos
18Excel Dashboards
Surface and Contour Charts
[a suace cha) acteurs cot
Contour ‘Wiretrame Contour
LESS
e #4 ae
Radar Charts
Radar
nessa — sa sey venesan say on
Filed Rade
suse
19
‘tutorialspointExcel Dashboards
You will understand how to use these charts and the chart elements effectively in your
dashboard in the chapter — Excel Charts for Dashboards,
In addition to the above-mentioned chart types, there are other widely used chart types that
come handy in representing certain data types, These are Waterfall Chart, Band Chart, Gantt
chart, Thermometer Chart, Histogram, Pareto Chart, Funnel Chart, Box and Whisker Chart
and Waffle Chart.
Perormance
85% oe
baagee
Gauge Chart = ans
5
Bullet Chart
20Excel Dashboards
Histogram
Pareto Chart na
~HREBEEEE
Gantt Chart
=
—
va =
~ 4
os —
- =: |
Step Chart ‘Thermometer Chart
Waterfall Chart Band Chart
‘Customer Stiefection Survey (2025-16)
PAP PLL EPL PM
Box and Whisker Chart
‘tutorialspoint
2aExcel Dashboards
You will learn about these charts in the chapter - Advanced Excel Charts for Dashboards.
Excel Camera
Once you create charts, you need to place them in your dashboard. If you want to make your
dashboard dynamic, with the data getting refreshed each time the source data changes, which
is the case with most of the dashboards, you would like to provide an interface between the
charts in your dashboard and the data at the backend. You can achieve this with the Camera
feature of Excel.
Excel PivotTables
When you have large data sets and you would like to summarize the results dynamically
showing various facets of the analysis results, Excel PivotTables come handy to include in
your dashboard. You can use either the Excel tables or the more powerful data tables in the
data model to create PivotTables.
The main differences between the two approaches are —
Excel Tables T Data Tables
Data from more than one table can be used
to create PivotTable, defining relationships
between the tables.
Data from only one table can be used to
create PivotTable.
When the tables increase in the no. of | Can handle huge data sets with thousands
rows, the memory handling and storage | of rows of data with memory optimization
will not be optimistic. and decreased file size.
If you try to create a PivotTable with more than one Excel table, you will be prompted to
create relationship and the tables with the relationship get added to the data model.
22Excel Dashboards
Filter
———— c ° E
1 [Region (Al!)
Summarizing Value | (-—___eoturmns__ Grand rotal
3 Sum of Order Amount’ Column Labels| ~
RowLabels January February March Grand Total
‘Albertson. Kathy 925 «(1375350 2650
Brennan, Michoel 2750 © 550400 3700
Davis, Witiam 1100-235. 600 1995
Dumico, Richard 400-965-125 1490
Flores, Tia 1655 985 1925 4565
Post, Melissa 765 «75350 1690
Tnompson, Shannon 140172000 3160
Walters, Chris 3552755 1265 4375
Grand Total 9090-9160 «531523565
Grand Total
Excel PivotTable
You will learn about PivotTables in the chapter — Excel PivotTables for Dashboards.
If you have data in the Data Model of your workbook, you can create Power
Power PivotCharts that span data across multiple data tables.
otTables and
Tscrolt Bar
24
tutorialspointRadio Buttons
Excel Dashboards
1000 100000
1200000 s200coo © sony
O teonery
1900000 2000000 “O March
‘00000 a e000 0 Aart
aaa ooo OM
dune.
0000
20000
° °
nary Feonany
Checkboxes
Fest Now 7 Sou 7 West
