Business Analytics with Excel

Dashboarding
 Learning Objectives

By the end of this lesson, you will be able to:

Define dashboards

Classify the principles of dashboard design

Create interactive charts in excel and format them

Describe how to use form controls such as combo box, check box,
and radio button

Construct an interactive dashboard

 A Day in the Life of Business Analyst

As a business analyst of an organization:

You are required to create a dashboard with reports on sales by region, month, and
employee.

Also, the reports must be intuitive and contain various parameters and filters. It should also
be dynamic and avoid unnecessary information.

To achieve these tasks, you will be learning a few concepts, such as dashboard, chart creation,
chart formatting and form controls.
Dashboarding
 What Is a Dashboard?

A dashboard is a real-time tool with an easy-to-use user interface that displays data in
a graphical format.
Dashboard

Dashboards are an efficient

way to turn data into
actionable insights.

Dashboard reports are very

popular these days.
Dashboard

Dashboards are incisive in

validating the effectiveness of
the matrices.

Dashboards help consolidate

and organize these metrics
through a summary.
Principles of Dashboard Design
 Dashboard Design

An appropriately designed dashboard can:

Provide better
Quicken decision-making Record the performance
coordination for an
process outcome
organization’s efforts
 Principles of Dashboard: Need

A good business dashboard is an indispensable tool when designed appropriately.

Hard to read
Sluggish
Overstuffed

Good Business Dashboard Bad Business Dashboard

A good dashboard presents the right data sets seamlessly and understandably.
 Principles of Great Dashboard: Points to Consider

1 Who is the audience?

The dashboard must be designed keeping in mind the target audience.

 Principles of Great Dashboard: Points to Consider

For CEO-level dashboards,

business-level analysis should
be included.

Example
For managerial-level
dashboards, associate-level
analysis can be included.
 Principles of Great Dashboard: Points to Consider

2 What value will the dashboard add?

Decide which key performance indicators need to be added in a dashboard.

 Principles of Great Dashboard: Points to Consider

Management can
Example see trends and take
actions accordingly.
 Principles of Great Dashboard: Points to Consider

3 Which type of dashboard should be created?

Decide the type of dashboard required based on the target audience.

 Principles of Great Dashboard: Points to Consider

Operational

Types Analytical

Strategic
 Principles of Great Dashboard: Points to Consider

4 Why no scrolling?

It is best to be able to see all the data at once. Fragmentation of data

should be avoided.
 Principles of Great Dashboard: Points to Consider

5 What should be added to have drill-down capabilities?

It’s good to add form controls in the dashboard, to have drill-down

capabilities.
 Principles of Great Dashboard: Points to Consider

These controls enable users to drill down the

data and see the result.
 Principles of Great Dashboard: Points to Consider

6 What type of information should be added?

It’s good to include information that prompts the user to initiate an action.
 Dashboarding: Example

A large automobile component manufacturer is looking for a detailed analysis of profit or

sales across various regions in a product-wise manner.

Automobile component
manufacturer
 Dashboarding: Example

The data set is huge and

scattered across different
regions.

Problems

Collating all these data sets and

analyzing the profit and sale
values is a difficult task.
 Dashboarding: Example

Using dashboards, the data visualization tool of Excel, the following outputs can be created:
 Dashboarding: Example

Use Dashboards to create

detailed analysis reports

Solution
Dashboard reports will provide
insights and alert the
manufacturer
Creating Charts in Excel
 Why Use Charts?

Charts represent data graphically, making it easy to analyze comparisons and trends.

1,291,203.0 1,254,184.5

1,189,646.5 1,271,887.3

1,171,745.0 1,178,009.3

1,342,694.0
 Charts: Types

1,400,000.0
Sindy
1,350,000.0

Paul
1,300,000.0

1,250,000.0 Justin

Line Chart 1,200,000.0

Total
Henry
Column Chart
1,150,000.0 Daniel

1,100,000.0
Calvin

1,050,000.0
Adam Calvin Daniel Henry Justin Paul Sindy Adam

1,000,000.01,100,000.01,200,000.01,300,000.01,400,000.0
 Charts: Types

Sindy, Adam,
414,850. 362,422.
1,350,000.0
0 8
1,300,000.0
Calvin,
1,250,000.0 Paul, 407,584.
381,035. 5
1,200,000.0 5
Bar Chart 1,150,000.0
Total
Pie Chart
1,100,000.0

Daniel,
1,050,000.0 Justin, 402,618.
436,253. 8
5 Henry,
469,768.
0
 Charts: Types

Column Chart Line Chart

Works with various data types Depicts trends in data and
and performs comparisons illustrates its progression over
between them time

Pie Chart Bar Chart

Compares proportion of each Compares data by displaying
category, where each category values either horizontally or
is shown as a slice of a pie vertically with respect to a
categorical value
Assisted Practice: Create Charts in Excel

Problem statement:

Demonstrate How to Create Charts in Excel

Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Create a chart
Assisted Practice: Apply Chart Formatting

Problem statement:

Demonstrate how to apply chart formatting in Excel.

Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Create a chart and apply formatting
Thermometer Chart
 Thermometer Chart: Introduction

Percentage
100.0%

98.0%

96.0%

94.0%
A thermometer chart is used to
represent data containing the
92.0%
actual and target value.
90.0%

88.0%

86.0%

84.0%

82.0%
This chart shows how much has
80.0%
1 been achieved from the target.
Assisted Practice: Create a Thermometer Chart

Problem statement:

Demonstrate how to create a thermometer chart in Excel.

Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Create a thermometer chart
Pareto Chart
 Pareto Chart: Introduction

A pareto chart contains both a column chart and a line graph.

60 120%

50 100%

40 80%

Cumulative
total
30 60%

20 40%

Individual values
arranged in 10 20%

descending order
0 0%
Defect 1 Defect 2 Defect 3 Defect 4 Defect 5 Defect 6 Defect 7 Defect 8 Defect 9 Defect 10
Pareto Chart: Introduction

The purpose of the pareto chart

is to highlight the most
important factor among a set of
factors.

It is used as a basic tool for

quality control. It allows us to
easily determine the common
issues.
Assisted Practice: Create a Pareto Chart

Problem statement:

Demonstrate How to Create a Pareto Chart in Excel.

Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Create a Pareto chart
Pivot Chart
 Pivot Chart

It is a virtual representation of a pivot table.

 Pivot Chart

Pivot tables summarize the base data.

 Pivot Chart: Example

The table below shows sales data by region.

Steps to Create a Pivot Chart

Step 1: Press control + A to select all data

 Steps to Create a Pivot Chart

Step 2: Click on the Insert tab and then on PivotTable

Steps to Create a Pivot Chart

Step 3: Click OK in the dialog box

Steps to Create a Pivot Chart

Step 4: In the PivotTable

Fields, choose Salesman,
Region, and Net Sales
Steps to Create a Pivot Chart

Step 5: Set up columns as Region,

rows as Salesman, and values as the
Sum of Net Sales
 Steps to Create a Pivot Chart

As a result, we get this customized pivot table.

 Steps to Create a Pivot Chart

Step 6: Press Control + A to select all data

 Steps to Create a Pivot Chart

Step 7: Go to the Insert

Click tab, click
on the on Insert
clustered column
bar chart or bar chart icon
Steps to Create a Pivot Chart

Step 8: Click on the Clustered Bar chart

 Steps to Create a Pivot Chart

As a result, the Pivot chart will look like the following:

 Steps to Create a Pivot Chart

Step 9: Select Chart Elements, Axis Titles, Chart Title, Data Labels, and Legend
Steps to Create a Pivot Chart

Step
Set10:
upSetup the Axis
Axis Titles andand Chart
Chart Title
Titles
 Filter the Chart According to Region

Choose only the North region in the Region drop down and click OK
Filter the Chart According to Region

Chart for Sales data for North region will look like:
Sort the Chart in Descending Order

Right click on the pivot chart bar and click Sort

Sort the Chart in Descending Order

Choose Sort Largest to Smallest option

Pivot Chart

• Pivot charts are used to represent

summarized data with total and
subtotals.
• This is essential for slicing and dicing data
to investigate granularity and summing
up to larger numbers.
Speedometer Chart
 Speedometer

The speedometer chart is The needle points to the

based on the speedometer. current speed.

A speedometer shows the minimum

and the maximum speed achieved by
a vehicle.
 Speedometer Chart

Current value

It shows where the Different milestones

users are placed with can be represented
respect to the target. with different colors.

Target value
 Speedometer Chart: Example

Fund-raising status can be represented by a speedometer chart.

Target value: 10 million Dollars The graphic depicts the attained

Raised: 8.69 million Dollars aim of 87 percent.
Create a Speedometer Chart
Steps to Create a Speedometer Chart

• Step 1: Create a data set

• Enter 25, 50, 75, 100 in Values
column, which will be constant.
Steps to Create a Speedometer Chart

Step 2: Enter the pie values for the pointer

Pointer value
Pointer size
200 – pointer
value
 Steps to Create a Speedometer Chart

26-75%
complete

• The first three values are the lengths

of respective bars in the chart.
0-25% 75-100% • The fourth value is the maximum value
complete complete
achievable, which is 100.
Steps to Create a Speedometer Chart

Step 3: Click on the Insert tab and charts group

Step 4: Click on More pie charts option

 Steps to Create a Speedometer Chart

Step 5: Click on Combo in the dialog box and click OK

Steps to Create a Speedometer Chart

Step 6:
• Select Chart Type for Values as
Doughnut
• Select Chart type for Pie as Pie
• Make Pie chart the secondary axis
• Click OK
Steps to Create a Speedometer Chart

Step 7:
Select Chart Elements, click the
checkbox next to Chart Title,
and remove Legend
 Steps to Create a Speedometer Chart

Step 8: Click on the Format tab and choose Series ‘Pie’

 Steps to Create a Speedometer Chart

Step 9: Change Angle of the first slice to 271 degrees

Steps to Create a Speedometer Chart

Step 10:
• Click control + right arrow to see
Series ‘Pie’ Point 1
• Choose Series Option as No Fill
Steps to Create a Speedometer Chart

Step 11:
• Click on Control + right arrow to see
Series ‘Pie’ Point 2
• Change Fill to Solid fill and color to
black
Steps to Create a Speedometer Chart

Step 12:
• Click on Control + right arrow to see
Series ‘Pie’ Point 3
• Change Fill to No fill
Steps to Create a Speedometer Chart

Step 13:
Change series options to Series ‘Values’
and choose Angle of the slice as 271
degrees
Steps to Create a Speedometer Chart

Step 14:
• Click on Control + right arrow to see
Series ‘Value’ Point 1
• Change Fill to Solid fill and color to red
Steps to Create a Speedometer Chart

Step 15:
• Click on Control + right arrow to see
Series ‘Value’ Point 2
• Change Fill to Solid fill and color to
yellow
Steps to Create a Speedometer Chart

Step 16:
• Click on Control + right arrow to see
Series ‘Value’ Point 3
• Change Fill to Solid fill and color to
green
Steps to Create a Speedometer Chart

Step 17:
• Click on Control + right arrow to see
Series ‘Value’ Point 4
• Change Fill to No fill
 Steps to Create a Speedometer Chart

As a result, the Speedometer chart will look like the following:

Stacked Column Chart
 Stacked Column Chart

A stacked column chart is like a column or bar chart.

Multiple y-axis values can be stacked

together for the same x-axis values.
 Stacked Column Chart

Different color coding is used for different types of y values.

 When to Use a Stacked Column Chart?

This chart can be used when we want to have a categorical set of values in x-axis and a set of
numerical values in y-axis with different groups.

• We can stack the CGPAs of students in different

subjects and find the total CGPA using a stacked
column chart.
• This is useful to understand the distribution of
markings of students in different subjects.
How to Create a Stacked Column Chart?

Step1: Choose the data to include in the chart

 How to Create a Stacked Column Chart?

Step2: Click on Stacked 2-D

chart under Insert tab
 How to Create a Stacked Column Chart?

Step3: Go to Stacked 2-D

column and select Stacked
Column chart
 How to Create a Stacked Column Chart?

The result for stacked column

chat will look like following:
 How to Create a Stacked Column Chart?

Step 4: Click on Axes to

show the X and Y axes
 Bar Chart vs. Stacked Column Chart

This is particularly useful when

Bar chart holds a categorical we represent grouped data
value in the x-axis and a set of with individual data elements in
numerical values in the y-axis. y-axis.

Stacked column chart holds

multiple numerical columns in
y-axis with different color
coding's.
Funnel Chart
Funnel Chart

• A funnel chart is very similar to a stacked

horizontal column chart.
• The bars are ordered in accordance with the
stages of a process.
• The name funnel describes that the data flows
through a funnel of processes.
 Funnel Chart

It is used to represent stages in a sales process and show the amount of potential revenue for each stage.
 Funnel Chart

The number of defects can be found in a project in different phases.

 How to Create a Funnel Chart?

The funneling of defects is shown in the given funnel chart.

 How to Create a Funnel Chart?

Choose the data and go to Insert. Then click on More charts and select Funnel chart
 How to Create a Funnel Chart?

The resultant chart will look like this:

Excel automatically groups the count of

defects in the decreasing order and
displays the phases like a funnel.
Funnel Chart vs. Stacked Horizontal Bar Chart

• The difference between a funnel chart and a

stacked horizontal bar chart is that in the
stacked chart there is no ordering of numeric
values.
• In the funnel chart the values are arranged in
decreasing order and the bars are centered in
the plot.
Chart with Combo Box
 Dynamic Charts

Dynamic charts in Excel allows us to interact with the chart and changes visualizations based on criteria.
 Chart with Combo Box

Chart with Combo box is used to show different

types of information on a single chart, such as
actuals against a target
 Chart with Combo Box

A combo box chart has a dropdown list of values to choose the x-axis variables from the data table and
dynamically changes the chart based on the chosen value.

Source: https://stackoverflow.com/questions/38270750/is-it-possible-to-control-charts-in-powerpoint-using-combo-box
 Chart with Combo Box

The CGPA of students are stored for three subjects. Based on the dropdown chosen, the bar graph
changes data to show a subject chosen.
Chart with Scrollbar
 Chart with Scrollbar

Form controls can be used to dynamically change the appearance of Excel charts.

One such control is by using scrollbar,

which can change the data as shown in
the chart.
Chart with Scrollbar

• A scroll control is added to provide a view of

the chart by moving vertically or horizontally.
• This is useful if the size of the chart is large,
with large number of data elements in the
data set.
 Chart with Scrollbar

The marks of 25 students are available, but only five are visible at a time.

The scrollbar enables to scroll

horizontally to view the data of
other students as well.

Scroll bar
Form Controls in Excel
 Introduction

Form controls are objects that allow one to interact with their data in excel.

Checkbox Spin Button

Combo Box List Box

Button Option Button

Group Box Scroll Bar

Labels

Excel provides several form controls, which are useful for selecting items from a list.
Assisted Practice: Install Developer Tab

Problem statement:

Demonstrate How to Install Developer Tab to Use Various Form Controls in Excel.
Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Install the Developer Tab
Assisted Practice: Create Interactive Dashboards
Using Combo Box

Problem statement:

Demonstrate How to Create Interactive Dashboards Using Combo Box.

Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Create an interactive dashboard with Combo Box
Assisted Practice: Create Interactive Dashboards
Using Checkbox

Problem statement:

Demonstrate How to Create Interactive Dashboards Using Checkbox.

Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Create interactive dashboard with Checkbox
Assisted Practice: Create Interactive Dashboards
Using Scroll Bar

Problem statement:

Demonstrate How to Create Interactive Dashboards Using Scroll Bar in Excel.

Assisted Practice Guidelines

Steps to follow:

Step 1: Open the Excel file

Step 2: Create an interactive dashboard with Scrollbar
 Key Takeaways

Dashboard reports help users to analyze the data and take the
appropriate actions.

Charts allow us to represent the data graphically, making it easy to

highlight comparisons and trends.

The thermometer chart depicts how much of your target you have
achieved.

A pareto chart contains a line graph, which represents the

cumulative total, and a bar chart, which represents the individual
values in descending order.

Form controls are objects that allow you to interact with your
data in excel.
Knowledge Check
Knowledge
Check
The plot area in a chart is .
1

a. The area defined by the vertical and horizontal axes and their opposite sides

b. Vertical axis in the chart

c. The entire chart, including the data series, axes, title, and legends

d. Horizontal axis in the chart

 Knowledge
Check
The plot area in a chart is . The
1

a. The area defined by the vertical and horizontal axes and their opposite sides

b. Vertical axis in the chart

c. The entire chart, including the data series, axes, title, and legends

d. Horizontal axis in the chart

The correct answer is a

The plot area in a chart is the area defined by the vertical and horizontal axes and their opposing sides.
Knowledge
Check The box on the chart that contains the name of each record is the
2 .

a. Cell

b. Title

c. Axis

d. Legend
 Knowledge
Check The box on the chart that contains the name of each record is the
2 .

a. Cell

b. Title

c. Axis

d. Legend

The correct answer is d

The box on the chart that contains the name of each record is the legend.
Knowledge
Check
You can interact with your data in an excel worksheet using form controls.
3

a. True

b. False
 Knowledge
Check
You can interact with your data in an excel worksheet using form controls.
3

a. True

b. False

The correct answer is a

Form controls are objects that allow you to interact with your data in excel.
Knowledge
Check
How can you update series as a secondary axis in a chart?
4

a. Right-click on Series, then click Format Data Series, and then select the Secondary Axis radio button

b. Right-click on horizontal axis, then click Format Axis, and then select the Secondary Axis radio button

c. Right-click on plot area, then click Format Plot Area, and then select the Secondary Axis radio button

d. None of the above

 Knowledge
Check
How can you update series as a secondary axis in a chart?
4

a. Right-click on Series, then click Format Data Series, and then select the Secondary Axis radio button

b. Right-click on horizontal axis, then click Format Axis, and then select the Secondary Axis radio button

c. Right-click on plot area, then click Format Plot Area, and then select the Secondary Axis radio button

d. None of the above

The correct answer is a

To update series as a secondary axis in a chart, right-click on Series, then click format data series, and then select
the secondary axis radio button.
 Knowledge
Check Which of the following options allows you to set the maximum value of the vertical
5 axis in a thermometer chart?

a. a. b. c. d.

b.

c.

d.
 Knowledge
Check Which of the following options allows you to set the maximum value of the vertical
5 axis in a thermometer chart?

a. a. b. c. d.

b.

c.

d.

The correct answer is b

The Format Axis option allows you to set the maximum value of the vertical axis in a thermometer chart.
Knowledge
Check
are mainly used to depict trends in data and illustrate its progression over time.
6

a. Column charts

b. Line charts

c. Pie charts

d. Bar charts
 Knowledge
Check
are mainly used trends
to depict to depict trends
in data in illustrate
and data and its
illustrate its progression
progression over time.over time.
6

a. Column charts

b. Line charts

c. Pie charts

d. Bar charts

The correct answer is b

Line charts are mainly used to depict trends in data and illustrate its progression over time.
Knowledge
Check
Which of the following tabs allows you to add a combo box in the worksheet?
7

a. Review tab

b. Insert tab

c. Developer tab

d. Design tab
 Knowledge
Check
Which of the following tabs allows you to add a combo box in the worksheet?
7

a. Review tab

b. Insert tab

c. Developer tab

d. Design tab

The correct answer is c

The developer tab allows you to add a combo box in the worksheet.
Create Interactive Charts and Worksheets with Form
Controls

Problem Scenario:

Now that you know the principles of dashboarding, how to create interactive charts, and how
to create worksheets with form controls, let us tackle a problem scenario to reinforce the
concepts learned. We will provide you a step-by-step guide to do this exercise. Let’s start!