MIS-3210 Data Visualization & Microsoft Excel Charts

MIS-3210

Data Visualization &

Microsoft Excel Charts

Learning Objectives

Introduce the basic benefits of “visualizing” data to

help with decision-making.

Become familiar with different chart types and be able

to select the appropriate chart type to convey your

message.

Learn how to create charts with spreadsheets.

Understand what a dashboard is, what its purpose is

and become familiar with the different types.

Learn how to create a digital dashboard.

A lesson on shark teeth

Total number of memberships
at fitness centers / health clubs
in the U.S. from 2005 to 2021
(in millions)
Year
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
Millions of
Memberships
32.8
33.8
36.3
39.4
41.3
41.3
42.7
46.7
45.6
45.3
50.2
51.4
50.2
52.9
54.1
55.0
55.8
Example
Video
Revenue of fitness and recreational sports centers in U.S.
from 2012 and projected to 2025
(in billions of dollars)
Year
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022*
2023*
2024*
2025*
* Projected
Fitness Centers
$11.2
$11.5
$11.8
$12.0
$12.5
$13.8
$15.2
$15.8
$17.5
$18.7
$19.2
$20.4
$21.4
$22.4
Recreational
Sports Centers
$11.3
$10.5
$10.7
$11.6
$12.0
$12.7
$12.6
$12.2
$11.9
$11.8
$11.9
$12.0
$12.1
$12.2
Total
$22.5
$22.0
$22.5
$23.6
$24.5
$26.5
$27.8
$28.0
$29.4
$30.5
$31.1
$32.4
$33.5
$34.6
Revenue (in billions of $)
Billions of Dollars
$25.0
$20.0
$15.0
$10.0
$5.0
$0.0
Revenue of fitness and recreational sports centers in U.S.
from 2012 and projected to 2025
(in billions of dollars)
$25.0
$20.0
$15.0 $11.2 $11.5 $11.8 $12.0 $12.5
$13.8 $15.2
$15.8
$19.2
$17.5 $18.7
$20.4 $21.4
$22.4
$10.0
$5.0
$0.0
2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022* 2023* 2024* 2025*
Previous & Projected Years
Fitness Centers
Recreational Sports Centers
Linear (Fitness Centers)
3D clustered column style 11
Market Share of
Major Fitness Center Chains
# of members (in thousands)
in 2021
Major Chain
Planet Fitness
24-hour Fitness
Gold’s Gym
Anytime Fitness
Lifetime Fitness
All other
TOTAL
Number of
Members
6,085
4,000
3,000
2,300
684
6,542
22,611
Revenue of 24-Hr Fitness and
Fitness Industry compared
2016-2021
Year
2016
2017
2018
2019
2020
2021
Entire Fitness
24-Hr Fitness Center Industry
(thousands)
(millions)
$980
$22.5
$1,004
$23.6
$1,126
$24.5
$1,205
$26.5
$1,290
$27.8
$1,275
$28.0
Number of participants
by exercise type in 2021 – US Only
(in millions)
Number of
Participants in
Type of Exercise
millions
Walking for fitness
117.4
Aerobic (low/high impact, step)
51.3
Treadmill
48.2
Stationary cycling
43.6
Weight/resistance machine
36.3
Stretching
36.2
Elliptical motion trainer
27.1
Swimming
26.4
Free Weights
25.6
Home gym exercise
25.5
Yoga
24.3
Other
23.5
MIS-3210
Data Visualization &
Microsoft Excel Charts
(Activity #7)
Learning Objectives
1. Introduce the basic benefits of “visualizing” data to
help with decision-making.
2. Become familiar with different chart types and be able
to select the appropriate chart type to convey your
message.
3. Learn how to create charts with spreadsheets.
4. Understand what a dashboard is, what its purpose is
and become familiar with the different types.
5. Learn how to create a digital dashboard.
A lesson on shark teeth
SHARK TEETH VS. HUMAN TEETH
As you probably guessed, humans and sharks do not have
the same number of teeth. As humans, we lose our baby
teeth when we are little and then get a set of permanent
‘adult’ teeth that are meant to last us our entire lives. That
means you have to be careful with them! Sharks, on the
other hand, have teeth that fall out all the time and are
continuously replaced with new teeth.
How many teeth does a shark go through in its lifetime?
Well, that answer depends on the species. For example,
calculations suggest that a lemon shark may shed more than
30,000 teeth over the course of its lifespan! On average, a
shark can lose at least one of its teeth every week since they
are not attached to gums like ours are.
HOW MANY ROWS OF TEETH DO THE SHARKS ACTUALLY
USE?
Sharks can have multiple rows that are attached to the jaws
by connective tissue, but they usually only use the first
couple. The other rows are folded back against the inside of
the jaw, where they are quickly forming. When one tooth
pops off, the one in the row behind it moves up to take its
place; lost teeth can sometimes be replaced in as little as 24
hours.
Fun fact: the cookiecutter shark doesn’t just lose one tooth –
it sheds its entire lower plate of teeth all at once, often
swallowing it with whatever it is eating!
ARE ALL THE TEETH IN A SHARK’S MOUTH THE SAME SHAPE?
Nope! Many sharks have teeth with shapes that are different in the
upper jaw and in the lower jaw. For example, a bull shark’s teeth in the
upper jaw are broad, triangular and heavily serrated, whereas its teeth
in the lower jaw have a broad base but are narrow and triangular with
fine serrations. Having more than one type of tooth shape within the
same fish is called ‘dignathic heterodonty’.
WHAT ARE SHARK TEETH MADE OUT OF?
The outer part of a shark’s tooth is made up of hard and mineral-rich
enameloid – in fact, enameloid is one of the hardest mineralised
tissues developed by animals. In reptiles and mammals (including
humans), this outermost layer of the tooth is known as enamel.
Enameloid differs from typical enamel in its chemistry and its
developmental timing (it is made first instead of last, as it is in
humans). Similar to a human tooth, a shark’s tooth has a core made of
dentine. Depending on the structure of the dentine, scientists
distinguish between two different types: orthodentine and
osteodentine. Species like the tiger shark have orthodentine
surrounding a pulpy cavity. It is quite compact and is similar to the
dentine we can find in human teeth. Other sharks, like the great white,
lack that cavity and their teeth instead have osteodentine, which is
spongy in appearance and kind of resembles real bone!
You can talk about all
that simply with a
single CAPTIVATING
visualization!!!
Data Visualization as a concept
Benefits of Data Visualization
• Absorb information in new and more constructive ways.
• Enable users to receive vast amounts of information regarding operational and business
conditions.
• Visualize relationships and patterns between seemingly separated concepts
or organizational activities.
• See connections as they are occurring between operating conditions and business
performance.
• Identify and act on emerging trends faster.
• Grasp shifts in customer behaviors and market conditions across multiple data sets much
more quickly.
• Manipulate and interact directly with data.
• Brings actionable insights to the surface and enable users to interact with data.
• Foster a new business language.
• Tell a story through data.
Charts & Graphs
Data
Visualization:
How to Pick
the Right
Chart Type?
http://www.ymarketresearchmethods.com/wpcontent/uploads/2013/01/Chart-types.jpg
Information conveyed
by charts
• Show changes over time
• Line & Column charts
• Show how one category compares to another
• Column & Bar charts
• Show how parts contribute to a whole
• Pie charts & stacked bar or column charts
• Show progress toward a goal
• Usually, a column chart
Charts that show trends over time
Something we
are measuring
(Y axis)
Passage of Time
(X axis)
Line Charts are especially
popular when you have the
x-axis plotting a time series
Comparing Trends – plotting two lines
Something we
are measuring
(Y axis)
Passage of Time
(X axis)
Things we are comparing
against each other
Comparing Trends – plotted a line and area-line
Plotted Line (purple)
Area Line (green)
Trend chart with a benchmark line
Benchmark line
How does this visual
convey a message
without words?
Charts showing
comparisons
(column and bar charts)
Units of things
we want to
compare
Quantity of these
various units
Clustered Column & Bar Charts
Bar Chart
Column Chart
A cluster of
somewhat dissimilar
things we want to
compare somehow.
Bar charts vs Histogram
• Bar charts depict separate data side by side and typically the bars
have a separation gap indicating they are not a continuous variable
• Histograms generally depict comparisons for continuous variables and
the bars are generally adjacent to each other with no gaps. – eg. years
Source:https://keydifferences.com/difference-between-histogram-and-bar-graph.html
Charts showing parts contributing to a whole
Pie Chart
Stacked
Bar and
Column
Charts
WSJ – 02/18/2022
Recent uses
in the media
This is called
a Treemap
Visualization
– It shows
hierarchical data
as a proportion
of a whole
Recent uses in
the media
A stacked bar
chart with color
and trend lines
Charts showing progress toward a goal
Bad Chart #1!
What’s
wrong
with
this
chart?
• No title
• No unit of measure
• No time period
• Y-axis numbers
are not labeled
• Large numbers
should have
commas,
rounded
• What are
series 1,2 and
3?
• Even though
there are 3 colors
in legend, only
one color is visible
How to fix?
• We can start
with fixing the
Y-axis – narrow
the range –
perhaps use
1000 increment
so the height of
other columns
is more
pronounced?
• Make this a
taller chart?
Add
descriptions,
headings and
labels
Bad Chart #2!
What’s wrong with this chart?
• No labels on Y-axis
• No Title
• X-axis – some years
are missing
Bad Chart #3!
What’s wrong with this chart?
• No Title “Expenditures by
category in three
separate years –
2002, 2001, and
2002. – in
millions of
dollars
• No unit of
measure for Yaxis
• No label for Xaxis e.g Types of
expenditures
• Wrong chart
type used Line charts
are used to
depict trends
over time –
best when Xaxis has series
of dates
• Labels on Xaxis too big so
not all are
seen (notice
extra ticks)
Best fit – cluster column
or bar – may need to
show years on x-axis
Changing the Y-axis range
Same data!!
Look how different it looks when you change the Y-axis range
Identify your minimum and maximum value to set the Y-axis appropriately
Changing the width: height Ratio of a Chart
Changing the
width to
height ratio
changes the
slope of the
line
If you want to
make the slope
more dramatic,
make the chart
taller!
Excel Charts
• To make your charts effective, keep them simple and uncluttered.
• Provide informative headings and axis titles
• When you chart in Excel, you chart one or more data series.
• A data series is a row or column of numbers that are entered into the worksheet
for graphing.
Some tips:
• Pie charts usually have one data series. Bar charts and other charts may have
multiple data series.
• Select your data by selecting the range. Select to include headings but generally
do not include totals)
• To select non-contiguous series use Ctrl key while selecting.
Excel chart Tutorials
• Watch this video for basics of how to create charts

You will learn the following
1. Insert Chart
2. Adjust chart
3. Improve your chart
4. Add series to chart
5. Create combination chart
Insert chart
• Highlight your range (including headings but not totals)
• Insert>select type of chart to insert or expand the selection
• Keyboard shortcut is Fn key + 11
Click on the arrow icon to expand choices and
select a default chart if using shortcut
Data series details – example in video
Series Name is the
Title Sales
Series Values are
your numerical
values column
Lastly, on the right
hand side, edit and
select the value
descriptions
If you try this yourself –
right-click labels and adjust
fonts to make the axis
labels and titles bigger
Adjusting the chart
• Chart Elements – a single part of the chart – the background gridlines,
the title, the axis labels are all chart elements that you can format and
edit
• Access them by clicking on the “plus” sign
Chart elements “more options”
A relevant dialogue box to format that element
will pop on the right side when you select
“More options”
Pay close attention to the Title of the options as
it clearly tells you what you are editing!
Shortcuts:
Ctrl +1
Or doubleclick
that element
and format
Or right-mouse
click that data
element
“Element” format box
Most important items to adjust for that
element is usually the first icon from the
right at the top of the dialogue box but
familiarize yourself with all the options
available
To improve the style of the element colors and fonts etc. select the tipped
paint bucket icon or use many of the
the alternate ways shown in video
Add a new data series
Fightrr
Kryptis
Perino
Hackrr
WenCal
Sales Budget
102
112.2
119
130.9
300
330
89
97.9
226
248.6
• Let’s say you add a new column “Budget” – a new data series and
now want to include that in the chart!
• Select the chart
• Expand the data range [Tip: to expand the data range hover near column and wait for
cursor to turn into a slanting double arrow!]
• Another option is to right-click and manually add Data series
• To add legend, make sure Series Name is entered correctly
Format Gridlines dropdown
Select the gridlines in chart to bring up Format Gridlines.
This is an important one!
The dropdown here can quickly take you to any of the elements
that exist in the chart
Combination chart – using two different chart
types for each data series
• Right click on the data series you want to show as something different
– let’s say a line type – and select “change series chart type” to select
line
Final Chart
• Make sure your chart is easy to read & visually appealing
• Add Titles and Legends
• Add data labels
• Add style – font and color
• Adjust axis to show data completely
•…
In-Class Activity #7
Creating charts
• Remember Alex from the Athlete’s Foot? He is considering opening a
fitness center by his shoe store.
• We will do some analysis of his data to help him learn more about
growth and trends in the fitness industry.
• Download the file Activity #7 DataForCharts.xlsx and open it in Excel.
Relevant questions for Alex to address:
• How are fitness centers doing historically in terms of
membership? Growing in numbers? Shrinking in numbers?
• Assuming people are more interested in becoming fit, where are
people going to get exercise? Fitness Centers? Rec Centers?
• If he’s going to license a franchise fitness business, which brand is
doing the best?
• Should he open a 24-hour fitness facility or one that operates in
standard business hours only? (How do the two models
compare?)
Activity #7 instructions
• Review the next few slides
• Use the Act#7-DataForCharts.xlsx as your starter file
• Create charts 1 through 5 to duplicate the examples
provided to you
• Answer the questions posed in previous slide
Millions of
Memberships
2005
32.8
2006
33.8
2007
36.3
2008
39.4
2009
41.3
2010
41.3
2011
42.7
2012
46.7
2013
45.6
2014
45.3
2015
50.2
2016
51.4
2017
50.2
2018
52.9
2019
54.1
2020
55.0
2021
55.8
Total number of memberships
at fitness centers / health clubs
in the U.S. from 2005 to 2021
(in millions)
46.7
Millions
Year
Chart 1: Total number of memberships at fitness centers /
health clubs in the U.S. from 2005 to 2021 (in millions)
32.8
33.8
36.3
39.4
41.3
41.3
42.7
50.2
45.6
51.4
50.2
52.9
54.1
45.3
Year
Type = Line chart
Video
55.0
55.8
Chart 2: Revenue of fitness and recreational sports centers in
U.S. from 2012 and projected to 2025 (in billions of dollars)
Year
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022*
2023*
2024*
2025*
Fitness
Centers
$11.2
$11.5
$11.8
$12.0
$12.5
$13.8
$15.2
$15.8
$17.5
$18.7
$19.2
$20.4
$21.4
$22.4
Recreational
Sports Centers
$11.3
$10.5
$10.7
$11.6
$12.0
$12.7
$12.6
$12.2
$11.9
$11.8
$11.9
$12.0
$12.1
$12.2
Total
$22.5
$22.0
$22.5
$23.6
$24.5
$26.5
$27.8
$28.0
$29.4
$30.5
$31.1
$32.4
$33.5
$34.6
Type = Vertical Bar/Clustered Column
Video
Chart 3: Market Share of Major Fitness Center Chains # of members
(in thousands) in 2021
Market Share of
Major Fitness Center Chains
# of members (in thousands)
in 2021
Major Chain
Number of
Members
Planet Fitness
6,085
24-hour Fitness
4,000
Gold’s Gym
3,000
Anytime Fitness
2,300
Lifetime Fitness
684
All other
6,542
TOTAL
22,611
Type = Pie Chart
Video
Chart 4: Revenue for Entire Fitness Industry and 24-hour fitness
2010-2015
Revenue
2010-2015
Year
2010
2011
2012
2013
2014
2015
Entire Fitness
24-Hr Fitness Center Industry
(thousands)
(millions)
$980
$22.5
$1,004
$23.6
$1,126
$24.5
$1,205
$26.5
$1,290
$27.8
$1,275
$28.0
Type = Combo Chart
Video
Hint on this style
combo chart
Chart 5: Number of participants by exercise type in
2021
(horizontal bar chart)
Type of Exercise
Walking for fitness
Aerobic (low/high impact, step)
Treadmill
Stationary cycling
Weight/resistance machine
Stretching
Elliptical motion trainer
Swimming
Free Weights
Home gym exercise
Yoga
Other
Number of
Participants in
millions
117.4
51.3
48.2
43.6
36.3
36.2
27.1
26.4
25.6
25.5
24.3
23.5
Type = Horizontal Bar
Video
Digital Dashboards
A Digital Dashboard is an electronic interface used to acquire and consolidate data across
an organization. A digital dashboard provides in-depth business analysis, while providing
a real-time snapshot of department productivity, trends and activities and key performance
indicators, etc.
Dashboards
• Displays summarized data
graphically
• Provides metrics of key
performance indicators
• Conveys a readily-understood
message ”
Types of Dashboards
• Strategic – focus on high level measures of performance
• Analytic– focus on comparisons, and historical data that
may be used in analyses.
• Operational – monitor activities and events that are
constantly changing and might require attention and
response at a moment’s notice.
Types of Dashboards by Functional Area
• Human Resources
• Finance
• Sales
• Operations
• Safety
• Security
• Etc.
Create your dashboard with Excel
Just copy and
paste your
visuals sideby-side on a
new
worksheet
and rename
the tab to
“Dashboard”!
[Select even number –
any 4 that best support
your answers]
Save and Upload
• Under the Dashboard, provide your individual
answers in complete sentences to the Activity
questions in slide #40
• Save your Excel file and upload it to the Activity #7
Assignment Folder