Chapter 2

Selecting a Chart Type

CONTENTS

DATA VISUALIZATION MAKEOVER 2-5 MAPS

The New York City Comptroller Geographic Maps
Heat Maps
2-1 DEFINING THE GOAL OF YOUR DATA Treemaps
VISUALIZATION 2-6 WHEN TO USE TABLES
Selecting an Appropriate Chart Tables versus Charts
2-2 CREATING AND EDITING CHARTS IN EXCEL 2-7 OTHER SPECIALIZED CHARTS
Creating a Chart in Excel Waterfall Charts
Editing a Chart in Excel Stock Charts
Funnel Charts
2-3 SCATTER CHARTS AND BUBBLE CHARTS
Scatter Charts 2-8 A SUMMARY GUIDE TO CHART SELECTION
Bubble Charts Guidelines for Selecting a Chart
Some Charts to Avoid
2-4 LINE CHARTS, COLUMN CHARTS, AND BAR
Excel’s Recommended Charts Tool
CHARTS
Line Charts SUMMARY
Column Charts
GLOSSARY
Bar Charts
PROBLEMS

L E A R N I N G O B J E C T I V ES
After completing this chapter, you will be able to

LO 1 Create charts and graphs using Excel LO 4 Interpret insights from charts and graphs

LO 2 Modify charts and graphs using Excel LO 5 Recognize which chart types should be avoided
and explain why
LO 3 Identify an appropriate chart type for a given goal
and data type
 Data Visualization Makeover 27

D ATA V I SU A L IZAT I O N M AK E O V E R

The New York City Comptroller

The New York City (NYC) comptroller’s office has to each of ten spending categories. For each of the ten
roughly 800 employees. Accountants, economists, categories, it also expresses the respective spending
engineers, investment analysts, information technol- allocation as a percentage of the total $70.24 billion
ogy support, and administrative support all support dollar budget for fiscal years 2020–2023.1
the mission of the NYC Comptroller, namely, to ensure The audience for the report is the public and most
the fiscal health of New York City. The Comptroller’s likely New York City residents who pay taxes and are
office is responsible for: auditing performance and interested in how the city allocates its budget. People
efficiency; ensuring integrity in city contracting; with a passion for a particular cause might also be
managing assets to protect pensions; resolving claims interested in this chart. For example, an advocate for
against the city and risk management; managing city parks and recreation might want to know how much
bonds; enforcing labor rights; and promoting fiscal money has been allocated to that cause and how
health and a sound budget for New York City. much it is relative to other spending categories.
In its work, the comptroller’s office generates a vari- Figure 2.2 is a horizontal bar chart that displays the
ety of annual reports including the Annual Audit Report, budget allocation amounts. Most data visualization
Annual Analysis of NYC Agency Contracts, Annual experts suggest that pie charts should be avoided in
Claims Report, and the Annual Report on Capital Debt favor of bar charts. There are several reasons for this.
and Obligations. The pie chart in Figure 2.1 is from First, science has shown that we are better at assessing
the Annual Report on Capital Debt and Obligations. differences in length than angle and area. Glancing at
It shows the amounts (in millions of dollars) allocated the pie chart in Figure 2.1 and comparing the Other

FIGURE 2.1 A PIE CHART SHOWING THE ALLOCATION OF NEW YORK CITY FUNDS

Parks
$3,876 Other City
5% Operations
$7,655
Hospitals 11% Education/CUNY
$1,257 $14,645
2% 21%

Admin. of Justice
$7,393
11% Environmental
Protection
$10,667
15%

Citywide
Equipment DOT & Mass
Housing & Transit
$4,019 Economic
6% $9,486
Development 14%
Computer $10,771
Equipment 15%
$473
1%

Source: NYC Office of Management and Budget, FY 2020 Adopted Capital Commitment Plan, October 2019.

1
Note that because of rounding, percentages do not add up to 100%.
(Continued)
28 Chapter 2 Selecting a Chart Type

FIGURE 2.2 A BAR CHART SHOWING THE NYC BUDGET ALLOCATION

Amount ($ millions) Allocated for New York City 2020–2023

Education/CUNY 14,645

Housing & Economic Development 10,771

Environmental Protection 10,667

DOT & Mass Transit 9,486

Other City Operations 7,655

Administration of Justice 7,393

Citywide Equipment 4,019

Parks 3,876

Hospitals 1,257

Computer Equipment 473

Total Budget ($ millions) 70,242

City Operations category with Administration of Justice distinguish the allocations by category. Third, we use a
category, it is difficult to tell which has a larger allocation. horizontal bar chart rather than a vertical bar chart so that
Indeed, the allocations for those two categories are very the category labels, a few of which are rather lengthy,
close. However, in Figure 2.2, because we have sorted are easier to read. Finally, we use the actual allocations
by amount allocated, we can see that the bar for Other and drop the percentages that appear in Figure 2.1. We
City Operations is longer (also, it appears higher up in could add the percentages, but it would make the
the list). Second, notice that in Figure 2.2, we no longer bar chart more crowded. Instead, we opt to use only
need to use different colors to distinguish categories. amounts because the bar lengths indicate the relative
Color is not necessary to distinguish each category in allocations, and a reader interested in the percentages
this bar chart because the length of the bars is used to can calculate them from the given allocation amounts.

In this chapter, we discuss in more detail how to select the right chart type to most effec-
tively convey a message to your audience. In the case of the NYC Comptroller, we are
comparing the amounts allocated by category, so that the constituents of New York City
can compare the spending categories and assess for themselves the budget allocation. A bar
chart is appropriate for comparison.
There are numerous types of charts available, each designed for a purpose. Understanding
the different types of charts available and why some charts are more appropriate for a certain
purpose will make you a better data analyst and a better communicator with data. In this
chapter, we describe some of the most commonly used types of charts and when they should
be used. We also discuss some more advanced charts, as well as charts to be avoided.

2-1 Defining the Goal of Your Data Visualization

Selecting an Appropriate Chart
How do you choose an appropriate chart? If the goal of your chart is to explain, then the
answer to this question depends on the message you wish to convey to your audience. If
you are exploring data, the best chart type depends on the question you are asking and hope
 2-2 Creating and Editing Charts in Excel 29

to answer from the data. Also, the type of data you have may influence your chart selection.
A few of the more common goals for charts are to show the following:
● Composition—Composition is what makes up the whole of an entity under consid-
eration. An example is the bar chart in Figure 2.2.
● Ranking—Ranking is the relative order of items. Figure 2.2 is also an example of
ranking, because we have sorted the categories by bar length, which is proportional to
the amounts allocated.
● Correlation/Relationship—Correlation is how two variables are related to one
another. An example of this is the relationship between average low temperature and
average annual snowfall for various cities in the United States.
● Distribution—Distribution is how items are dispersed. An example of this is the
number of calls received by a call center in a day, measured on an hourly basis.
The type of data you have should also influence your chart selection. For example, a bar or
column chart is often an appropriate chart when we are summarizing data about categories.
Students’ letter grades in a college course are categories. For summarizing the number of
students earning each letter grade, a bar or column chart would be appropriate.
The different types of data are The relationship between two quantitative variables often makes a scatter chart an
discussed in Chapter 1.
appropriate choice. Bar charts, scatter charts, and line charts with the horizontal axis being
time, are often the best choice for time series data. If your data have a spatial component, a
geographic map might be a good choice.
Creating great data visualizations is a skill that is best learned by doing. Therefore, before
getting into more detail on the various types of charts and in what circumstances they are most
appropriate, we provide detailed instructions on how to create and edit charts in Excel.

2-2 Creating and Editing Charts in Excel

In this section, we discuss how to create and edit a chart in Excel. Let us begin with how
to create the chart of the zoo attendance data discussed in Chapter 1. The data, zoo
attendance by month, are shown in Figure 2.3. We create a column chart using these
data in the following steps.

FIGURE 2.3 Data in File Zoo

Zoo
30 Chapter 2 Selecting a Chart Type

FIGURE 2.4 An Unedited Column Chart for the Zoo Data

Attendance
25000

20000

15000

10000

5000

0
Jan Feb Mar Apr May Jun July Aug Sept Oct Nov Dec

Creating a Chart in Excel

The following steps show how to create a column chart in Excel using the data in the file Zoo.
Step 1. Select cells A1:B13
Step 2. Click the Insert tab on the Ribbon
Step 3. Click the Insert Column or Bar Chart button in the Charts group
When the list of column and bar charts subtypes appears, click the
Clustered Column button
The chart created in the preceding steps appears in Figure 2.4. We can improve the appear-
ance of the column chart in Figure 2.4 by following the steps below to delete the horizontal
grid lines, make the axes better defined add axis labels, and remove the border of the chart.
This will improve the chart by making it simpler and better-defined.

Editing a Chart in Excel

Here we give the step-by-step instructions on how to edit the chart shown in Figure 2.4,
and included in the file ZooChart, so that it appears as shown in Figure 2.5. These steps for
ZooChart
editing will be used to throughout the following chapters in this book to improve the for-
matting of most charts created in Excel.
Step 1 removes the horizontal grid lines and Step 2 edits the title.
The Chart Elements button is Step 1. Click anywhere on the chart then click on the border of the chart title and
not available in Mac versions press the Delete key
of Excel. See the NOTES +
Click the Chart Elements button
COMMENTS at the end of this
section for a description of
Deselect the check box for Gridlines and select the check box for Axis Titles
how to access these features Step 2. Click the Chart Title text box above the chart and replace “Chart Title” with
on a Mac. Zoo Attendance by Month
Inside the text box, highlight “Zoo Attendance by Month”
Click the Home tab on the Ribbon and in the Font group select
Calibri 16 pt Bold
 2-2 Creating and Editing Charts in Excel 31

FIGURE 2.5 A Column Chart of Zoo Attendance by Month

Zoo Attendance by Month

Attendance
25000

20000

15000

10000

5000

0
Jan Feb Mar Apr May Jun July Aug Sept Oct Nov Dec
Month

To change the position of the title, click on the border of the title box and
slide the text box to the left so that the title is above the vertical axis.
Steps 3–5 format the horizontal axis and axis labels.
Step 3. Double click any label of the horizontal axis
Step 4. When the Format Axis task pane appears, click the Fill & Line button
Click Line
Select Solid line
In the drop down to the right of Color, under Theme Colors, select Black
Step 5. Click the Home tab on the Ribbon and in the Font group select Calibri 10.5
Steps 6–8 format the vertical axis and axis labels.
Step 6. Double click any label of the vertical axis
Step 7. When the Format Axis task pane appears, click the Fill & Line button
Click Line
Select Solid line
In the drop down to the right of Color, under Theme Colors, select Black
Step 8. In the Format Axis task pane, click the Axis Options button
Click Tick Marks
Next to Major type, select Inside
Step 9. Click the Home tab on the Ribbon and in the Font group select Calibri 10.5
Steps 10–11 add and format axis titles.
Step 10. Select the horizontal axis title, place the cursor over the border of the text box
and drag it to the right to the end of the axis.
In the Font group, select Calibri 10.5 and click the Bold B button.
Type Month
Step 11. Select the vertical axis title, right click and select Format Axis Title and click
the Size & Properties button. Click Alignment and next to Text direction,
32 Chapter 2 Selecting a Chart Type

from the drop-down menu, select Horizontal. Place the cursor over the border
and drag it to the top of the vertical axis aligned above the axis labels.
In the Font group, select Calibri 10.5 and click the Bold B button.
Type Attendance
Steps 12–13 eliminate the border of the chart.
In Step 12, if you click inside Step 12. Click the Chart Area of the chart (anywhere outside of the rectangular area
the rectangular area delimited delimited by the horizontal and vertical axes of the chart)
by the horizontal and vertical
Step 13. In the Format Chart Area task pane, click Chart Options
axes, the Format Plot Area
task pane will be activated
Click the Fill & Line button
instead of the Format Chart Click Border
Area task pane. Click No line
These steps produce the chart shown in Figure 2.5. In later chapters, we will introduce
additional design elements that can be used to further improve charts.

NO T E S 1 C O M M E N TS

4. The Chart Elements button offers a variety of features,

1. The Chart Elements button is not available in Excel for
including the ability to add or delete axes and axis titles, a
Mac. To access the features of Chart Elements in Excel for
chart title, data labels, gridlines, a legend, and a trendline.
Mac, double click the chart, and click the Chart Design tab,
5. An axis title can also be created using a text box by click-
and click Add Chart Element from the Chart Layouts group.
ing Insert on the Ribbon, clicking Text, and then selecting
2. When selecting data in Excel to populate a chart, the left-
where you want the axis title to appear.
most column of data is generally represented by the horizon-
6. The Chart Styles button allows you to change the
tal axis on the chart. The assignment of data to the horizontal
style of the chart type you have selected, as well the color
and vertical axes can be switched by right-clicking on the
scheme of the chart. The Chart Styles button is not avail-
chart, choosing Select Data Source, and on the Select Data
able in Excel for Mac. To access the features of Chart Styles
Source dialog box, click Switch Row/Column.
in Excel for Mac, double click the chart, click the Chart
3. To create a chart using nonadjacent columns of data in
Design tab, and click a style from the Chart Styles group.
Excel, select the leftmost column you wish to include in
To change the color scheme, click the Change Colors
the chart, press and hold down the control key (Ctrl), and
button in the Chart Styles group.
select the other columns of data you wish to include.

2-3 Scatter Charts and Bubble Charts

When exploring data, we are often interested in the relationship between two quantitative
variables. For example, we might be interested in the square footage of a house and the
cost of the house, or the age of a car and its annual maintenance cost. A scatter chart is a
graphical presentation of the relationship between two quantitative variables. One variable
is shown on the horizontal axis and the other is shown on the vertical axis, and a symbol is
used to plot ordered pairs of the quantitative variable values. A scatter chart is appropriate
for better understanding the relationship between two quantitative variables. As we shall
also see, a bubble chart is an appropriate chart when trying to show relationships with more
than two quantitative variables.

Scatter Charts
The file Snow contains the average low temperature in degrees Fahrenheit and the average
annual snowfall in inches for 51 major cities in the United States. A portion of the data
are shown in Figure 2.6. These averages are based on thirty years of data. Suppose we are
interested in the relationship between these two variables. Intuition tells us that the higher
the average low temperature the lower the average snowfall, but what is the nature of this
relationship?
 2-3 Scatter Charts and Bubble Charts 33

FIGURE 2.6 A Portion of the Data in File Snow

Snow

The data are plotted in Figure 2.7. This scatter chart is created using the following steps.
Step 1. Select cells C1:D52
Step 2. Click the Insert tab on the Ribbon
Step 3. Click the Insert Scatter (X,Y) or Bubble Chart button in the Charts
group
When the list chart subtypes appears, click the Scatter button
Then edit the chart as outlined in Section 2-2.
Each point on the chart in Figure 2.7 represents a pair of numbers. In this case, we have a
pair of measurements for each of 51 cities. The measurements are average low temperature
in degrees Fahrenheit and average annual amount of snowfall in inches. We can see from
the chart that average annual amount of snowfall intuitively levels off at zero for warm-
weather cities.
Scatter charts are among the most useful charts for exploring pairs of quantitative data.
But, what if you wish to explore the relationships between more than two quantitative
variables? When exploring the relationships between three quantitative variables, a bubble
chart may be useful.

Bubble Charts
A bubble chart is a scatter chart that displays a third quantitative variable using different
sized dots, which we refer to as bubbles.
The file AirportData contains data on a sample of 15 airports. These data are shown
in Figure 2.8. For each airport, we have the following quantitative variables: average wait
time in the non-priority Transportation Security Authority (TSA) queue measured in min-
utes, the cheapest on-site daily rate for parking at the airport measured in dollars, and the
number of enplanements in a year (the number of passengers who board including trans-
AirportData fers) measured in millions of passengers.
34 Chapter 2 Selecting a Chart Type

FIGURE 2.7 A Scatter Chart for the Data in File Snow

Average Snowfall versus Average Low Temperature for 51 U.S. Cities

Average Annual Snowfall (inches)
120

100

80

60

40

20

0
0 10 20 30 40 50 60 70 80
Average Low Temperature (degrees Farenheit)

FIGURE 2.8 Data in File AirportData

The data are plotted as a bubble chart in Figure 2.9. This chart was created using the
following steps:
Step 1. Select cells B1:D16
Step 2. Click the Insert tab on the Ribbon
2-4 Line Charts, Column Charts, and Bar Charts 35

Step 3. Click the Insert Scatter (X,Y) or Bubble Chart button in the Charts group
When the list of chart subtypes appears, click the Bubble button
Then edit the chart as outlined in Section 2-2.
We plot the TSA wait time along the horizontal axis and the parking rate along the vertical
axis, and vary the size of each bubble to represent the number of enplanements. We see
that airports with fewer passengers tend to have lower wait times than those with more
passengers. There seems to be less of a relationship between parking rate and number of
passengers. Airports with lower wait times do tend to have lower parking rates.

FIGURE 2.9 A Bubble Chart Using the Data in File AirportData

Cheapest Parking Rate (per day)

$25.00

$20.00

$15.00

$10.00

$5.00

$0.00
0.00 2.00 4.00 6.00 8.00 10.00 12.00
TSA Wait Time (minutes)

In a bubble chart, you might wish to change which variables correspond to the x (horizontal)
values, the y (vertical) values, and the bubble sizes. Once the chart has been created in Excel,
the following steps can be used to change these assignments.
Step 1. Right-click any bubble and choose Select Data...
Step 2. When the Select Data Source dialog box appears, click the Edit button under
Legend Entries (Series)
Step 3. Enter the location of the data you want to correspond to the horizontal values
in the Series X values: box (see Figure 2.10). Do not include column headers
Step 4. Repeat Step 3 for Series Y Values: box and the Series bubble size: box
Click OK

2-4 Line Charts, Column Charts, and Bar Charts

In this section, we consider the line chart, a natural extension of a scatter chart discussed in
the previous section. We also introduce column and bar charts which are useful for display-
ing categorical data.

Line Charts
A line chart uses a point to represent a pair of quantitative variable values, one
value along the horizontal axis and the other on the vertical axis, with a line connecting the
points. Line charts are very useful for time series data (data collected over a period of time:
minutes, hours, days, years, etc.). As an example, let us consider Cheetah Sports. Cheetah
sells running shoes and has retail stores in shopping malls throughout the United States. The
36 Chapter 2 Selecting a Chart Type

FIGURE 2.10 The Edit Series Dialog Box

file Cheetah contains the last ten years of sales for Cheetah Sports, measured in millions of
dollars. These data are shown in Figure 2.11. Figure 2.12 displays a scatter chart and a line
chart created in Excel for these sales data.

FIGURE 2.11 Data in the File Cheetah

Cheetah
 2-4 Line Charts, Column Charts, and Bar Charts 37

The following steps create the line chart of the Cheetah Sports sales data shown in
Figure 2.12b.
Step 1. Select cells A1:B11
Step 2. Click the Insert tab on the Ribbon
Step 3. In the Charts group, click the Insert Scatter (X,Y) or Bubble Chart
button
Select Scatter with Straight Lines and Markers
Edit the chart as described in Section 2-2
Comparing Figure 2.12b with Figure 2.12a, the addition of lines between the points sug-
gests continuity and makes it is easier for the reader to see and interpret changes that have
occurred over time.

FIGURE 2.12 Scatter Chart (a) and Line Chart (b) for Cheetah Sports Sales

Sales ($ millions)
250

200

150

100

50

0
0 2 4 6 8 10 12
Year
(a)

Sales ($ millions)
250

200

150

100

50

0
0 2 4 6 8 10 12
Year
(b)
38 Chapter 2 Selecting a Chart Type

Let us consider a second example that illustrates multiple lines on a single chart. Consider
the file CheetahRegion. Cheetah Sports has two sales regions: the eastern region and the
western region. The file breaks down the total sales for the ten-year period by region as
CheetahRegion shown in Figure 2.13.

FIGURE 2.13 The Data in the File CheetahRegion

Cheetah Sports sales by region are shown in Figure 2.14. To create the line chart shown in
Figure 2.14, select cells A1:C11 (do not select D1:D11) in the file CheetahRegion and follow
the Steps 2 and 3 previously outlined for constructing a line chart. In addition to the chart editing
from Section 2-2, we have also changed the color scheme to Monochromatic Palette 1 (using
the Chart Styles option as described in the Notes + Comments at the end of Section 2-2).
We can see from Figure 2.14 that sales in the Western region have increased over the
last three years of this ten-year period whereas sales in the Eastern region have dropped
substantially since year seven.

FIGURE 2.14 Line Chart for Cheetah Sports Sales by Region

Sales ($ millions) Eastern Sales Western Sales

140

120

100

80

60

40

20

0
0 2 4 6 8 10 12
Year
 2-4 Line Charts, Column Charts, and Bar Charts 39

Column Charts
A column chart displays a quantitative variable by category or time period using vertical
bars to display the magnitude of a quantitative variable. We have seen an example of a col-
umn chart in the zoo attendance data, where the categories are months of the year and the
quantitative variable is zoo attendance. Let us elaborate more about when to use a column
Cheetah
chart by continuing the Cheetah Sports annual sales example shown in Figure 2.11.
The following steps create the column chart of the Cheetah Sports sales data shown in
Figure 2.15.
Step 1. Select cells A1:B11
Step 2. Click the Insert tab on the Ribbon
Step 3. In the Charts group, click the Insert Column or Bar Chart button
Select Clustered Column
Excel displays the year as if it is a quantitative variable. To correct this, we need the
following steps:
Step 4. Right click the chart and select Change Chart Type…
Step 5. When the Change Chart Type task pane appears, select the Cluster Column
type that plots the appropriate number of variables (in this case, the single
variable Sales plotted with ten monochromatic columns) and click OK
Edit the chart as outlined in Section 2-2
The next step adds data labels to the bars.
Step 6. Click the Chart Elements button and select Data Labels

FIGURE 2.15 A Column Chart for Cheetah Sports Sales

Total Sales ($ millions)

195
183
177 175
170
154
145

110
87 90

1 2 3 4 5 6 7 8 9 10
Year

The line chart in Figure 2.12b, and the column chart in Figure 2.15, are both good displays
of the Cheetah Sports annual sales. The line chart, with its connected lines, makes it easier
to see how the sales are changing over time. The column chart, with its data labels, is pre-
ferred if it is important for the audience to know the values of sales in each year. Moreover,
adding data labels to a line chart generally makes the chart too cluttered. On the other
hand, if there are numerous categories or time periods, the line chart (without data labels)
would be preferred over the column chart with data labels because the column chart would
appear too cluttered and labels would not be readable.
40 Chapter 2 Selecting a Chart Type

Let us now reconsider the regional data for Cheetah Sports in the file CheetahRegion.
Using these data, let us construct a clustered column chart and compare it to the line chart
in Figure 2.14. A clustered column chart displays multiple quantitative variables by cat-
CheetahRegion
egories or time periods with different colors, with the height of the columns denoting the
magnitude of the quantitative variable.
To create the clustered column chart in Figure 2.16, select cells A1:C11 in the file
CheetahRegion as shown in Figure 2.13 (do not select cells D1:D11). Follow Steps 2–5
Clustered column charts with
previously outlined for a column chart. In addition to the chart editing from Section 2-2,
multiple variables are also
called side-by-side column we have also changed the color scheme to Monochromatic Palette 1 (using the Chart
charts. Styles option as described in the Notes + Comments at the end of Section 2-2).

FIGURE 2.16 Clustered Column Chart for Cheetah Sports Sales by Region

Sales ($ millions)
Eastern Sales Western Sales
140

120

100

80

60

40

20

0
1 2 3 4 5 6 7 8 9 10
Year

Comparing Figures 2.14 and 2.16, we see that the changes in sales within a region over
time are more apparent in the line chart. The clustered column chart in Figure 2.16 appears
cluttered and the changes in sales are not as obvious as in Figure 2.14. Adding data labels
to Figure 2.16 would make the clustered column chart even more cluttered.
While Figure 2.14 is preferred over Figure 2.16 for the regional sales data for Cheetah
Sports, neither of these charts convey that the Eastern and Western regions make up the
total sales. It is difficult to tell how total sales are changing. We make this more obvious by
using a stacked column chart. A stacked column chart is a column chart that uses color to
denote the contribution of each subcategory to the total.
To create a stacked column chart for Cheetah Sports, we select cells A1:C11 in the file
CheetahRegion, and repeat Steps 2–5 previously outlined for a column chart—except in
Step 3, we click the Insert Column or Bar Chart in the Charts group and select Stacked
Column . After chart editing, we obtain the stacked column chart shown in Figure 2.17.
This chart shows the combination of Eastern and Western region sales by year and the total
height of the column indicates the level of total sales.
The Cheetah Sports example with regional sales data demonstrates the important princi-
ple that the appropriate chart depends not only on the type of data, but also the goal of the
analysis and needs of the audience. If demonstrating the change in sales over time within
each region is a key point, then a line chart in this case is a good choice. If representing the
total sales level and how each region contributes to total sales over time is important, then a
stacked column chart is a good choice.
 2-4 Line Charts, Column Charts, and Bar Charts 41

FIGURE 2.17 Stacked Column Chart for Cheetah Sports Sales by Region

Sales ($ millions)
250
Eastern Sales Western Sales

200

150

100

50

0
1 2 3 4 5 6 7 8 9 10
Year

Bar Charts
A bar chart shows a summary of categorical data using the length of horizontal bars to
display the magnitude of a quantitative variable. That is, a bar chart is a column chart
turned on its side. Like column charts, bar charts are useful for comparing categorical
variables and are most effective when you do not have too many categories. Figure 2.2
in the Data Visualization Makeover of the Allocation of Funds in New York City is a
good example. As shown in that example, a bar chart can be a good substitute for a pie
chart when showing composition. Sorting the data as in Figure 2.2 makes the rank order
of the components by the magnitude of the quantitative variable more obvious. A bar
chart is preferred over a column chart if there are lengthy category names because it is
easier to display the names horizontally (for improved legibility). However, for time
series data, a column chart is better as it is more natural to display the passage of time
from left to right horizontally.
A clustered bar chart displays multiple quantitative variables for categories or time
periods using the length of horizontal bars to denote the magnitude of the quantitative
variables and separate bars and colors to denote the different variables. Like a stacked
column chart, a stacked bar chart is a bar chart that uses color to denote the contribution
of each subcategory to the total. As with column charts, clustered and stacked bar charts
are available in Excel by clicking on the Insert Column or Bar button in the Charts
group and then selecting either Clustered Bar or Stacked Bar .

N OT E S 1 C O M M E NT S

1. In this section, we have shown how to use the Insert Scat- Sports data, which are numbers rather than actual years,
ter (X,Y) or Bubble Chart button and the Scatter with show up as a line on the chart rather than being interpreted
Straight Lines and Markers to construct a line chart. as the categories for the horizontal axis.
Another alternative is to use the Insert Line or Area 2. In Chapter 3, we discuss the issue of trying to present too
Chart button . This works for time series data (dates, much information on a single chart. In some cases, it may
months, years), but the option assumes numerical data is be preferable to use two similar charts rather than a stacked
to be graphed. For example, the periods in the Cheetah bar/column or clustered bar/column chart.
42 Chapter 2 Selecting a Chart Type

2-5 Maps
In this section, we introduce three types of maps used to display various types of data. You
are most likely familiar with geographic maps which are very useful for displaying data
that have a spatial or geographic component. We will also discuss heat maps and treemaps.
Each is available in Excel.

Geographic Maps
A geographic map is generally defined as a chart that shows characteristics and the
arrangement of the geography of our physical reality. A geographic map of the United
States shows state borders and how the states are arranged. A choropleth map is a geo-
graphic map that uses shades of a color, unique colors, or symbols to indicate quantitative
or categorical variables by geographic region or area.
Let us consider creating a choropleth map of the United States for which color shading
is used to denote the population of each state. A darker shade will indicate a higher popula-
tion and a lighter shade will indicate a lower population.
The population data for the fifty states can be found in the file StatePopulation. A por-
tion of the data set is shown in Figure 2.18. The fifty states in the United States are listed in
column A and the corresponding estimated population of each state is in column B.

FIGURE 2.18 A Portion of the State Population Data

StatePopulation

The following steps will create a choropleth map using shading to denote size of the popu-
lation for each state.
Step 1. Select cells A1:B51
Step 2. Click the Insert tab on the Ribbon
Step 3. In the Charts group, click the Maps button Maps

After selecting Filled Map, you Select Filled Map

may need to allow Excel to
send your request to the Bing
After editing the chart title as outlined in Section 2-2, we obtain the map in Figure 2.19. The
search engine to complete the map shows the states with the highest populations are California, Texas, Florida, and New York.
appropriate map. As an example of a choropleth map with categorical data, let us consider how Amazon
is able to deliver packages so quickly to its U.S. customers. Amazon distributes customer
orders from fulfillment centers which stock most of the products Amazon sells. The file
AmazonFulfill contains categorical data by state. For each state, “Yes” or “No” is provided
to denote whether or not that state has at least one Amazon fulfillment center. A portion of
the Amazon fulfillment data set is shown in Figure 2.20.
 2-5 Maps 43

FIGURE 2.19 A Choropleth Map Using Shading for State Population

Estimated United States Population by State

Estimated 2020 Population
39,937,489

567,025

Powered by Bing
© GeoNames, Microsoft, TomTom

FIGURE 2.20 A Portion of the Amazon Fulfillment Data

AmazonFulfill

Selecting cells A1:B51 and following Steps 2–3 listed in the state population example
results in the map shown in Figure 2.21. Amazon has at least one fulfillment center in 38
of the 50 states. The states without a fulfillment center tend to be either relatively sparsely
populated or a geographic outlier. Clearly, Amazon has a lot of fulfillment centers to ensure
quick customer delivery times for many of the products it sells.
Next we consider two useful types of maps that are not required to be geographic.
44 Chapter 2 Selecting a Chart Type

FIGURE 2.21 A Choropleth Map Using Color for a Categorical Variable

Which States Have At Least One Amazon Fullfillment Center?

Yes
No

Powered by Bing
© GeoNames, Microsoft, TomTom

Heat Maps
A heat map is a two-dimensional (2D) graphical representation of data that uses different
shades of color to indicate magnitude. Let us consider the data in file SameStoreSales. The
SameStoreSales data are shown in Figure 2.22. The rows of this data set correspond to store locations and

FIGURE 2.22 Same-Store-Sales Data in the File SameStoreSales

 2-5 Maps 45

the columns are the months of the year. The percentages given indicate the change in sales
over the same month last year for a given store. This percentage change metric is com-
monly used in the retail industry and is referred to as “same-store-sales.” For example, the
St. Louis store’s sales for January are 2% lower than last year in January.
Figure 2.23 shows a heat map of the same-store-sales data given in Figure 2.22. The
cells shaded red in Figure 2.23 indicate declining same-store sales for the month, and cells
shaded blue indicate increasing same-store sales for the month. The following steps create
the heat map shown in Figure 2.23.
Step 1. Select cells B2:M17
Step 2. Click the Home tab on the Ribbon.
Step 3. Click Conditional Formatting in the Styles group
Select Color Scales and click Blue-White-Red Color Scale

FIGURE 2.23 A Heat Map for Same-Store Sales

The heat map in Figure 2.23 helps the reader to easily identify trends and patterns. We can see
that Austin has had positive increases throughout the year, while Pittsburgh has had consistently
negative same-store sales results. Same-store sales at Cincinnati started the year negative but
then became increasingly positive after May. In addition, we can differentiate between strong
positive increases in Austin and less substantial positive increases in Chicago by means of color
shadings. A sales manager could use the heat map in Figure 2.23 to identify stores that may
require countermeasures and other stores that may provide ideas for best practices. Heat maps
can be used effectively to convey data over different areas, across time, or both, as seen here.

Treemaps
A treemap is a chart that uses the size, color, and arrangement of rectangles to display the
magnitudes of a quantitative variable for different categories, each of which are further
decomposed into subcategories. The size of each rectangle represents the magnitude of the
quantitative variable within a category/subcategory. The color of the rectangle represents
the category and all subcategories of a category are arranged together.
Categorical data that is further decomposed into subcategories is called hierarchical
data. Hierarchical data can be represented with a tree-like structure, where the branches
of the tree lead to categories and subcategories. As an example, let us consider the top ten
46 Chapter 2 Selecting a Chart Type

brand values given in the file BrandValues (source: Forbes.com). The data appear in the
file as shown in Figure 2.24. Each observation consists of an industry, a brand within an
industry, and the value of the brand.

FIGURE 2.24 Data in the file BrandValues

BrandValues

Figure 2.25 shows how these data have a hierarchical or tree structure. The base of the
tree is the top ten brand values. The category is the industry of each company, the subcate-
gory is the brand name, and the value of the brand is the quantitative variable.

FIGURE 2.25 The Hierarchical Tree Structure of the Top Ten Brand Values Data

Top 10 Brand Values

Industry Technology Beverages Leisure Automotive Restaurant

Company Apple Google Microsoft Amazon Facebook Samsung Coca-Cola Disney Toyota McDonald’s
Value ($ Billions) 205.5 167.5 125.3 97 88.9 53.1 59.2 52.2 44.6 43.8

Figure 2.26 is an example of a treemap for the brand values data. The following steps are
used to create a treemap in Excel using the data in the file BrandValues.
Step 1. Select cells A1:C11
Step 2. Sort the data by Industry by using the following steps:
Click Data on the Ribbon
Click the Sort button in the Sort & Filter group
In the Sort dialog box, select Industry from the drop-down menu
From the Order drop-down menu select A to Z
 2-6 When to Use Tables 47

Step 3. Click Insert on the Ribbon.

Click the Insert Hierarchy Chart button in the Charts group
Select Treemap from the drop-down menu
To display the brand values:
Step 4. Click any brand label and then right click
Select Format Data Labels… from the drop-down menu
Select Text Options, Label Options and select Value
The colors in the treemap shown in Figure 2.26 correspond to industries. Each of the rect-
angles represents a brand and the size of the rectangles indicates the size of the brand’s
value. We see that the technology industry has six brands in the top ten. Apple, Google, and
Microsoft are the three highest brand values.

FIGURE 2.26 A Treemap of the Ten Most Valuable Brands

The Ten Most Valuable Brands ($ Billions)

Automotive Beverages Leisure Restaurants Technology

Technology Beverages

Coca-Cola, 59.2
Leisure
Amazon, 97.0

Google, 167.7 Disney, 52.2

Automotive Restaurants

Facebook, 88.9

McDonald's,
Apple, 205.5 Microsoft, 125.3 Samsung, 53.1 Toyota, 44.6 43.8

2-6 When to Use Tables

Tables versus Charts
In general, charts can often convey information faster and easier to readers than tables, but
in some cases a table is more appropriate. Tables should be used when the:
● reader needs to refer to specific numerical values.
● reader needs to make precise comparisons between different values and not just rela-
tive comparisons.
● values being displayed have different units or very different magnitudes.
Let us consider the case of Gossamer Industries. When the accounting department of Gos-
samer Industries is summarizing the company’s annual data for completion of its federal tax
forms, the specific numbers corresponding to revenues and expenses are important and not just
the relative values. Therefore, these data should be presented in a table similar to Table 2.1.
Similarly, if it is important to know by exactly how much revenues exceed expenses
each month, then this would also be better presented as a table rather than as a line chart as
seen in Figure 2.27. Notice that it is very difficult to determine the monthly revenues and
48 Chapter 2 Selecting a Chart Type

Table design is discussed in costs in Figure 2.27. We could add these values using data labels, but they would clutter the
Chapter 3. figure. A preferred solution is to combine the chart with the table into a single figure, as in
Figure 2.28, to allow the reader to easily see the monthly changes in revenues and costs
while also being able to refer to the exact numerical values.

TABLE 2.1 Table Showing Exact Values for Costs and Revenues by Month
for Gossamer Industries
Month
Jan Feb Mar Apr May June Total
Costs ($) 48,123 56,458 64,125 52,158 54,718 50,985 326,567
Revenues ($) 64,124 66,125 67,125 48,178 51,785 55,678 353,015

Gossamer

FIGURE 2.27 A Line Chart of Monthly Costs and Revenues for Gossamer Industries

Gossamer Industries: Revenue and Costs

80,000 Costs ($) Revenues ($)

70,000

60,000

50,000

40,000

30,000

20,000

10,000

0
Jan Feb Mar Apr May June

We construct the chart-table Using the data in the file Gossamer, the following steps show how to create the line chart
combination using a line accompanied with a table as shown in Figure 2.28.
chart because this option
is not available for a scatter Step 1. Select cells A2:G4
chart. Excel does not support Step 2. Click the Insert tab on the Ribbon
integration of charts and
Step 3. Click the Insert Line or Area Chart button in the Charts group
tables for all chart types.
Step 4. When the list of column and bar charts subtypes appears, click the
Line button
Step 5. Click anywhere on the chart
Click the Chart Elements button
Select the check box for Data Table
Edit the chart as outlined in Section 2-2
 2-7 Other Specialized Charts 49

FIGURE 2.28 Combined Table and Line Chart of Monthly Costs and Revenues for Gossamer
Industries

Gossamer Industries: Revenue and Costs

80,000

70,000

60,000

50,000

40,000

30,000

20,000

10,000

0
Jan Feb Mar Apr May June
Costs ($) 48,123 56,458 64,125 52,158 54,718 50,985
Revenues ($) 64,124 66,125 67,125 48,178 51,785 55,678

Displaying values with Now suppose that we wish to display data on revenues, costs, and head count for each
different units on the same
month. Costs and revenues are measured in dollars, but head count is measured in number
line chart is known as a dual-
axis chart. We will discuss
of employees. Although all of these values can be displayed on a line chart using multiple
these again in Chapter 9. vertical axes, this is generally not recommended. Because the values have widely different
magnitudes (costs and revenues are in the tens of thousands, whereas head count is approx-
imately 10 each month), it would be difficult to interpret changes on a single chart.
Therefore, a table similar to Table 2.2 is recommended.

TABLE 2.2 Table Displaying Head Count, Costs, and Revenues, for
Gossamer Industries
Month
Jan Feb Mar Apr May June Total
Head Count 8 9 10 9 9 9
Costs ($) 48,123 56,458 64,125 52,158 54,718 50,985 326,567
Revenues ($) 64,124 66,125 67,125 48,178 51,785 55,678 353,015

2-7 Other Specialized Charts

In this section, we discuss three additional chart types: the waterfall chart, the stock chart,
and the funnel chart. Waterfall charts and stock charts are used primarily in financial ana-
lytics, while funnel charts are prominent in marketing and sales.

Waterfall Charts
A waterfall chart is a visual display that shows the cumulative effect of positive and nega-
tive changes on a variable of interest. The changes in a variable of interest are reported for a
series of categories (such as time periods) and the magnitude of each change is represented
by a column anchored at the cumulative height of the changes in the preceding categories.
50 Chapter 2 Selecting a Chart Type

FIGURE 2.29 Gossamer Data on Costs, Revenues, and Gross Profit

GossamerGP

Continuing with the Gossamer Industries example from the Section 2-6, consider the
data in the file GossamerGP. The data are shown in Figure 2.29. Gross profit is the differ-
ence between revenue and variable costs.
The following steps are used to create the waterfall chart of gross profit shown in Figure 2.30.
Step 1. Select cells A2:H2. Hold down the control key (Ctrl) and also select cells A5:H5
Step 2. Click the Insert tab on the Ribbon
Step 3. Click the Insert Waterfall, Funnel, Stock, Surface or Radar Chart button
in the Charts group
When the list of subtypes appears, click the Waterfall button
In the initial chart, notice that the Total has been treated like another month. The following
steps will make the total appear as in Figure 2.30.
Step 4. Double-click the column Total to activate the Format Data Series task pane, and
then click the column of data again to activate the Format Data Point task pane
Step 5. When the Format Data Point task pane appears, click the Series Options
button
Select the check box for Set as total
Then edit the chart as outlined in Section 2-2

FIGURE 2.30 A Waterfall Chart for the Gossamer Gross Profit Data

Gossamer Gross Profit ($)

Increase Decrease Total
3,000

9,667 4,693 26,448

–3,980

–2,933

16,001

Jan Feb Mar Apr May June Total

 2-7 Other Specialized Charts 51

Figure 2.30 shows the gross profit by month, with blue indicating a positive gross profit and
orange indicating a negative gross profit. The upper or lower level of the bar indicates the
cumulative level of gross profit. For positive changes, the upper level of the bar is the cumula-
tive level, and for negative changes, the lower end of the bar is the cumulative level. Here we
see that cumulative level of gross profit rises from January to March, drops in April and May
and then increases in June to the cumulative gross profit of $26,448 for the six-month period.

Stock Charts
A stock chart is a graphical display of stock prices over time. Let us consider the stock price
data for telecommunication company Verizon Communications given in the file Verizon. As
shown in Figure 2.31, this data set lists, for five trading days in April: the date, opening price
per share (price per share at the beginning of the trading day), the high price (highest price per
share observed during the trading day), the low price (the lowest price per share observed dur-
ing the trading day), and the closing price (the price per share at the end of the trading day).

FIGURE 2.31 Stock Price Data for Verizon Communications

Verizon

Excel also provides an open- Excel provides four different types of stock charts. We illustrate the simplest one here, the
high-low-close stock chart, a high-low-close stock chart. A high-low-close stock chart is a chart that shows the high
volume-high-low-close stock
chart, and a volume-open-
value, low value, and closing value of the price of a share of a stock at several points in
high-low-close chart. These time. The difference between the highest and lowest share prices for each point in time is
charts add data on a stock’s represented by a vertical bar, and the closing share price by a marker on the bar.
opening price and trading The following steps are used to create Figure 2.32, the high-low-close stock chart for
volume to the basic high-low- the Verizon stock price data.
close stock chart.
Step 1. Select cells A1:A6. Hold down the control key (Ctrl) and also select cells C1:E6
Step 2. Click the Insert tab on the Ribbon
Step 3. Click the Insert Waterfall, Funnel, Stock, Surface or Radar Chart button
in the Charts group
When the list of subtypes appears, click the High-Low-Close button
Edit the chart using steps outlined in Section 2-2
The following steps add the closing price labels and markers.
Step 4. Click the Chart Elements button and select Data Labels
Step 4 places three sets of labels on each vertical bar (highest, closing and lowest price per
share). The following steps clean up the display.
Step 5. Click any of the high price per share labels and press the Delete key. Do the
same for the low price per share labels
Step 6. On one of the vertical lines, click a data point directly next to one of the
closing price labels
52 Chapter 2 Selecting a Chart Type

Step 7. When the Format Data Series task pane appears, click the Fill &Line button
, then click Marker
Under Fill, select Solid fill and to the right of Color, select black from
the drop down menu
Under Border, select Solid line, and to the right of Color, select Black
While typically used to display from the drop down menu
stock price data over time,
To the right of Width, select 3 pt
a high-low-close stock chart
can also be used to display As shown in Figure 2.32, the closing prices per share over the five days are given. We see
the maximum, minimum, and
that on April 20, 21, and 23, the price closed near the low end of the trading price range.
mean (or median) of a variable
of interest measured over a
On April 24, the closing price was near the highest price of the day. On April 22, the clos-
set of categories. ing price was near the middle of the trading price range.

FIGURE 2.32 A High-Low-Close Stock Chart for Verizon Wireless

Price per Share ($)

59.50 Close

59.00

58.50

58.13
58.00 57.99 57.93
57.59
57.50

57.00
56.82
56.50

56.00

55.50
20-Apr 21-Apr 22-Apr 23-Apr 24-Apr
Date

Funnel Charts
Another specialized chart is a funnel chart. A funnel chart shows the progression of a
quantitative variable for various categories from larger to smaller values. A funnel chart
is often used to show the progression of sales leads that are converted through a series of
steps to an eventual sale, but any progression of larger values to smaller values over a series
of nested categories can be illustrated with a funnel chart. As an illustration, let us consider
a company whose goal is to grow the number of well-qualified members on its data science
team. The hiring process involves the following steps: (1) post the job ad and candidates
apply and are then referred to as applicants, (2) applicants are given a technical test; those
who pass are deemed technically qualified, (3) the technically qualified set of applicants
are invited to do Zoom interviews, and based on the Zoom interviews, a subset of the tech-
nically qualified applicants are deemed finalists and are invited for on-site interviews,
(4) based on test scores and the on-site interviews, a subset of the finalists are offered
employment, and (5) those who accept are hired.
The data for this process are in the file DataScienceSearch shown in Figure 2.33. A
funnel chart of these data is shown in Figure 2.34. First, we give the steps for creating this
chart and then we provide a brief summary of the chart.
 2-7 Other Specialized Charts 53

FIGURE 2.33 Data for Data Scientist Hiring

DataScienceSearch

The following steps are used to create the funnel chart shown in Figure 2.34.
Step 1. Select cells A1:B6.
Step 2. Click the Insert tab on the Ribbon
Step 3. Click the Insert Waterfall, Funnel, Stock, Surface or Radar Chart button
in the Charts group
When the list of subtypes appears, click the Funnel Chart button
Edit the chart using steps outlined in Section 2-2.

FIGURE 2.34 A Funnel Chart for Data Scientist Hiring

Data Science Search

Applicants 51

Technically Qualified 37

Finalists 12

Offers 7

Hired 4

The funnel chart shows the narrowing of the field of applicants as the process progresses.
We see that the process started with 51 applicants and ended with 4 new hires. Specifically,
we observe that the Zoom interviews narrowed the field from 37 technically qualified
applicants to 12 finalists who were invited to interview on-site.
54 Chapter 2 Selecting a Chart Type

2-8 A Summary Guide to Chart Selection

In this section, we conclude the chapter with a discussion summarizing guidelines for chart
selection.

Guidelines for Selecting a Chart

Recognizing that there are often exceptions to rules and that there are often disagreements
even among data visualization experts, we provide general recommendations based on the
goal of the visualization and the type of data being analyzed.
Goal: To Show a Relationship
–5% –6%
16% 15%
–9% –6%

scatter bubble line stock column bar heat map

To show a relationship between two quantitative variables, we recommend a scatter chart. An

example is the temperature and snowfall data shown in a scatter chart in Figure 2.7. When
dealing with three quantitative variables, a bubble chart can be used. Line charts can be used to
emphasize the pattern across consecutive data points and are commonly used to display relation-
ships over time. Stock charts show the relationship between time and stock price. Column charts,
bar charts, and heat maps can be used to show the relationships that exist between categories.
Goal: To Show Distribution

scatter bubble column bar choropleth map

In addition to being useful for showing the relationships between quantitative variables, scat-
ter and bubble charts can be useful for showing how the quantitative variable values are dis-
tributed over the range for each variable. For example, from the scatter chart in Figure 2.7, we
can see that only 2 of the 51 cities have an average annual snowfall greater than 80 inches.
Other chart types useful Column and bar charts can be used to show the distribution of a variable of interest over
for showing how data are discrete categories or time periods. For example, Figure 2.5 shows the distribution of zoo
distributed that rely on more
attendance by time (month). As previously mentioned, column charts rather than bar charts
advanced statistical concepts
are discussed in Chapter 5.
should be used for distribution over time, as it is more natural represent the progression of
time from left to right. A choropleth map shows the distribution of a quantitative or cate-
gorical viable over a geographic space. Figures 2.19 and 2.21 are examples of these.
Goal: To Show Composition

bar stacked bar stacked column treemap waterfall funnel

When the goal is to show the composition of an entity, a good choice is a bar chart, sorted by
contribution to the whole. An example is the New York City budget in Figure 2.2. A stacked
bar chart is appropriate for showing the composition of different categories and a stacked
column chart is good for showing composition over a time series. Figure 2.17, the sales for
Cheetah Sports by region is a good example of a stacked column chart with time series data.
While the goal of a pie chart A treemap shows composition in the situation where there is a hierarchical structure
is to show composition, for among categorical variables. In Figure 2.26, we see the brand values (the quantitative vari-
reasons discussed in the able of interest) for companies within industry sectors. For example, the technology sector is
next section, we do not composed of six brands in the top ten. All other sectors are composed of only a single brand.
recommend the use of pie
charts.
A waterfall chart shows the composition of a quantitative variable of interest over time
or category. For example, Figure 2.30 shows the composition of the final value of gross
profit over time. A funnel chart also shows composition in the sense that going from the
bottom of the funnel to the top gives the composition of the original set at the top of the
funnel. The funnel chart for the hiring process in Figure 2.34 is an example.
 2-8 A Summary Guide to Chart Selection 55

Goal: To Show Ranking

bar column
Bar charts and column charts, sorted on the cross-sectional quantitative data of interest
across categories, can be used to effectively show the rank order of categories on the quan-
titative variable. An example is the ten categories ranked by spending allocation in the
New York City budget shown in Figure 2.2.
When trying to select a chart type, we recommend starting with understanding the needs
of the audience to determine the goal of the chart, understanding the types of data you
have, and then selecting a chart based on the guidance provided in this section. Like most
analytics tools, it is important to experiment with different approaches before arriving at a
final decision on your data visualization.

Some Charts to Avoid

In this section, we discuss some charts that should be avoided. There are charts that many
data visualization experts agree should be avoided. Usually this is because a chart is overly
cluttered or takes too much effort for most audiences to interpret the chart quickly and
accurately. Here we provide some guidance on charts we believe should be avoided in
favor of other types of charts.
As we have already discussed in the data visualization makeover at the beginning of
this chapter, many experts suggest that pie charts should be avoided. Instead of a pie chart,
consider using a bar chart. This is because science has shown that we are better at assessing
differences in length than angle and area. Small differences can be better detected in length
than area, especially when sorted by length. Also, using a bar chart simplifies the chart in
that there is no longer a need for a different color for each category. Figures 2.1 and 2.2
show the difference between the pie chart and the bar chart and illustrate why the latter is
preferred.
A radar chart is also referred Another chart to be avoided is a radar chart. A radar chart is a chart that displays mul-
to as a spider chart or a web
tiple quantitative variables on a polar grid with an axis for each variable. The quantitative
chart.
values on each axis are connected with lines for a given category. Multiple categories can
be overlaid on the same radar chart.
Let us consider data on four suppliers of a component needed by Newton Industries.
Newton manufactures high-performance desktop computers and has started to vet four pos-
sible suppliers of one of the components needed for its computers. Newton’s management
needs to select a supplier to provide the component and has collected data on the percent-
age of late shipments, the percentage of defective components delivered and the cost per
unit each supplier would charge. These data are in the file NewtonSuppliers and are shown
in Figure 2.35. Figure 2.36 is the radar chart created from these data.

FIGURE 2.35 Supplier Performance Data for a Component for Newton

Industries

NewtonSuppliers
56 Chapter 2 Selecting a Chart Type

The radar chart in Figure 2.36 has three axes corresponding to the three columns of data in
Figure 2.35. Luckily the three variables are of roughly the same magnitude. Variables of very
different scales can distort a radar chart. The four suppliers each have their own color and
their data are connected by lines. Since Newton presumably wants low values for percentage
late, percentage of defective components and cost per unit, a dominant supplier’s rectangle
would be completely inside its competitors. It appears from Figure 2.36 that the supplier
Foster might be the best choice, but it is difficult to distinguish the cost per unit. Even with
this very small data set, the radar chart is quite busy and difficult for an audience to interpret.

FIGURE 2.36 A Radar Chart of Supplier Performance for a Component

for Newton Industries

Supplier Performance
Ace Beaty Foster Rolf
% Late
12

10

Cost per unit ($) % Defective

Perhaps a better choice is the clustered column chart shown in Figure 2.37. Here we can
see that Foster is clearly better on percentage late and percentage defective and competitive
on price. We do note that for more suppliers, even the clustered column chart will become

FIGURE 2.37 A Clustered Column Chart of Supplier Performance for a Component for Newton
Industries

Supplier Performance
% Late % Defective Cost per Unit ($)
12

10

0
Ace Beaty Foster Rolf
Supplier
 2-8 A Summary Guide to Chart Selection 57

cluttered. Not surprisingly, manufacturers will often develop a scoring model so that a sin-
gle score can be computed and used to compare suppliers.
In Chapter 3, we will provide In addition to too much clutter and problems with scaling, another criticism of radar
a more detailed discussion of charts is that as the number of factors increases, they become more circular and suffer
how to remove clutter from
charts.
from the same criticisms as pie charts. Finally, although not as obvious in our three-
factor example, the order of the axes in a radar chart can dramatically alter the picture
presented by a radar chart and hence the audience’s perception. For these reasons, we
suggest avoiding the use of radar charts.
Another chart that many find difficult to read is an area chart. An area chart is a line
chart with the area between the lines filled with color. Figure 2.38 is an area chart of the
Cheetah Regional sales data shown in Figure 2.13. Area charts display volume and convey
continuity, but a simpler line chart such as Figure 2.14 or a stacked column chart such as
Figure 2.17 provide less cluttered alternatives.

FIGURE 2.38 An Area Chart for the Cheetah Sports Regional Sales Data

Sales ($ millions)
250
Eastern Sales Western Sales

200

150

100

50

0
1 2 3 4 5 6 7 8 9 10
Year

Dual-axis charts are one form Excel also provides combination charts called combo charts. A combo chart com-
of a combo chart; these are bines two separate charts, for example, a column chart and a line chart, on the same chart.
discussed in Chapter 9.
Combo charts can be overly cluttered and difficult to interpret, especially when they con-
tain both a left and right vertical axis.
Finally, we recommend always avoiding unnecessary dimensionality on any of the
Unnecessary use of
dimensionality and other chart
charts you select. Many Excel charts come in 2-dimensional (2D) and 3-dimensional (3D)
design issues are discussed in versions. We recommend avoiding 3D versions as the third dimension typically adds no
more detail in Chapter 3. additional understanding and can lead to more clutter.

Excel’s Recommended Charts Tool

Excel provides guidance for chart selection through its Recommended Charts tool. The
Recommended Charts button is found in the Charts group of the Insert tab on the
Ribbon. The following steps demonstrate the use of the Recommended Charts tool using
Zoo the zoo attendance data in the file Zoo shown in Figure 2.3.
58 Chapter 2 Selecting a Chart Type

Step 1. Select cells A1:B13

Step 2. Click the Insert tab on the Ribbon Recommended
Step 3. Click the Recommended Charts button Charts in the Charts group
The Insert Chart dialog box appears as shown in Figure 2.39. Four different chart types
are recommended, a column chart (also shown to the right), a bar chart, a funnel chart, and
a combination (combo) chart. Clicking on any of the four charts on the left will display that
chart enlarged and to the right, in the same way the column chart is displayed on the right in
Figure 2.39. This allows you to see an enlarged version of the chart before committing to the
chart.
Step 4. Select the Clustered Column chart and click OK
Edit the chart as outlined in Section 2-2

FIGURE 2.39 The Insert Chart Task Pane for the Zoo Attendance Data
 Summary 59

N OT E S 1 C O M M E NT S

1. After clicking the Recommended Charts button, observe that Indeed, sometimes chart types that we would not recom-
selecting the All Charts tab from the Insert Chart task pane mend show up as choices under Recommended Charts.
generates a listing of all available chart types. Selecting any Two examples are apparent in the last two choices shown in
of the listed charts provides a preview of the selected chart. Figure 2.39. Using a funnel chart for the zoo attendance is
Hence an alternative to navigating the Charts group on the a poor choice, as there is no natural progression from high
Insert tab on the Ribbon is to click the Recommended Charts values to low values. Likewise, notice the combo chart sorts
button, select the All Charts tab and select from the list. the months by decreasing order of attendance, which is not
2. The Recommended Charts tool does not always recom- likely to be useful for these time series data if the goal is
mend chart types consistent with the advice in this chapter. to better understand the pattern of attendance over time.

S U M M A RY

In this chapter, we discussed how the goal of the analysis and the type of data should inform
chart selection. We provided detailed steps to create and edit charts in Excel. We discussed
a variety of popular chart types and provided steps for creating these charts in Excel.
A scatter chart displays pairs of quantitative variables and is very useful for detecting
patterns. A bubble chart is a scatter chart that represents a third quantitative variable by dif-
ferent size dots, known as bubbles. A line chart is a scatter chart with lines connecting the
points. A line chart, like a scatter chart, is good for detecting patterns and is very useful for
time series data. Line charts, by connecting the dots representing data points, provide more
of a sense of continuity than scatter charts.
A column chart displays a quantitative variable by using the height of the column to
denote the magnitude of the quantitative variable by category or time period. A clustered
column chart is a column chart that displays multiple quantitative variables using different
colors and side-by-side columns. A stacked column chart is a column chart that shows com-
position for each column by using color to denote subcategory contributions to the total.
Similar to a column chart, a bar chart displays the magnitude of a quantitative variable
using length, but by using horizontal bars rather than vertical columns. A clustered bar chart
and a stacked bar chart are similar to their column-chart counterparts, but they use horizon-
tal bars rather than vertical columns to denote the magnitude of the quantitative variable.
We also discussed three types of maps. A choropleth map is a geographic map that uses
shades of a color, different colors, or symbols to indicate quantitative or categorical vari-
ables by geographic region or area. A heat map is a two-dimensional graphical represen-
tation of data that uses different shades of color to indicate magnitude. Finally, a treemap
uses different-sized rectangles and color to display quantitative data that is associated with
hierarchical categories. We briefly discussed when to use a table or a combination of a
table and a chart, rather than a chart. If exact values are needed, a table or table/chart com-
bination might be the best choice.
Three specialized charts, waterfall, stock, and funnel were discussed. A waterfall chart
shows the cumulative effect of positive and negative changes on a variable of interest. A
stock chart displays various information about the share price of a stock over time. For
example, a high-low-close stock chart shows the high value, low value, and closing value
of the price of a share of stock over time. A funnel chart shows the progression of a quanti-
tative variable across various nested categories from larger to smaller values.
We provided guidance on how to select an appropriate chart based on the goal of the
chart and the type of data being displayed. We also discussed some chart types to avoid.
We concluded the chapter with a discussion of the Recommended Charts tool in Excel.
60 Chapter 2 Selecting a Chart Type

G L O S S A RY

Area chart A line chart with the area between the lines filled with color.
Bar chart A chart that displays a quantitative variable by category using the length of
horizontal bars to display the magnitude of a quantitative variable.
Bubble chart A scatter chart that displays a third quantitative variable using different sized
dots, which we refer to as bubbles.
Choropleth map A geographic map that uses shades of a color, different colors, or
symbols to indicate quantitative or categorical variables by geographic region or area.
Clustered bar chart A chart that displays multiple quantitative variables for categories or
time periods using the length of horizontal bars to denote the magnitude of the quantitative
variables and separate bars and colors to denote the different categories.
Clustered column chart A chart that displays multiple quantitative variables for
categories or time periods with different colors, with the height of the columns denoting
the magnitude of the quantitative variable.
Column chart A chart that displays a quantitative variable by category or time period
using vertical bars to display the magnitude of a quantitative variable.
Combo chart A chart that combines two separate charts, for example, a column chart and
a line chart, on the same chart.
Funnel chart A chart that shows the progression of a quantitative variable for various
nested categories from larger to smaller values.
Geographic map A chart that shows characteristics and the arrangement of the geography
of our physical reality.
Heat map A two-dimensional graphical representation of data that uses different shades
of color to indicate magnitude.
Hierarchical data Data that can be represented with a tree-like structure, where the
branches of the tree lead to categories and subcategories.
High-low-close stock chart A chart that shows the high value, low value, and closing
value of the price of a share of stock over time.
Line chart A chart that uses a point to represent a pair of quantitative variable values, one
value along the horizontal axis and the other on the vertical axis, with a line connecting the
points.
Radar chart A chart that displays multiple quantitative variables on a polar grid with an
axis for each variable. The quantitative values on each axis are connected with lines for a
given category.
Scatter chart A graphical presentation of the relationship between two quantitative
variables. One variable is shown on the horizontal axis and the other is shown on the
vertical axis and a symbol is used to plot ordered pairs of the quantitative variable values.
Stacked bar chart A bar chart that uses color to denote the contribution of each
subcategory to the total.
Stacked column chart A column chart that shows part-to-whole comparisons, either
over time or across categories. Different colors, or shades of color, are used to denote the
different parts of the whole within a column.
Stock chart A graphical display of stock prices over time.
Treemap A chart that uses the size, color, and arrangement of rectangles to display the
magnitudes of a quantitative variable for different categories, each of which are further
decomposed into subcategories. The size of each rectangle represents the magnitude of the
quantitative variable within a category/subcategory. The color of the rectangle represents
the category and all subcategories of a category are arranged together.
Waterfall chart A visual display that shows the cumulative effect of positive and negative
changes on a variable of interest. The basis of the changes can be time or categories and
changes are represented by columns anchored at the previous time or category’s cumulative
level.
Problems 61

P R O B L E M S

CONCEPTUAL
1. Sales by Region. Consider the following data for percentage of sales by region. LO 3

Percentage of
Sales Region Total Sales
East 28%
North 14%
South 36%
West 22%

a. Should a bar chart or a pie chart be used to display these data? Explain.
b. List two ways to enhance the formatting of the chart to improve interpretability.
2. Academic Makeup of Departments. You are conducting an analysis of the makeup
of the departments in your firm. Your goal is to compare the departments’ mixes of
academic backgrounds. You have defined the following categories for academic back-
ground: Business, Engineering, and Other. You have the percentage of employees in
each category for each of the four departments as shown in the table below. LO 3

Department Business Engineering Other

A 84% 0% 16%
B 45% 43% 12%
C 48% 20% 32%
D 17% 68% 15%

What type of chart is best suited to display these data?

3. Charts of Gasoline Prices. The following charts both show the average price (in dol-
lars) per gallon of gasoline in the United States for 36 consecutive months. Consider
the following charts. The first is a line chart and the second is a column chart. LO 3
Average Price per Gallon ($)
4.50

4.00

3.50

3.00

2.50

2.00

1.50

1.00

0.50

0.00
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35
Month
62 Chapter 2 Selecting a Chart Type

Average Price per Gallon ($)

4.50

4.00

3.50

3.00

2.50

2.00

1.50

1.00

0.50

0.00
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35
Month

Which better displays the data? Why?

i. Line chart
ii. Column chart
4. Pickup Truck Sales. The following charts display the sales of pickup trucks in the
United States by manufacturer for one year (source: The Wall Street Journal). You
must choose one of these charts to edit to produce the final chart for your presentation
to management. LO 3, 5

Number of Vehicles Number of Vehicles

1,000,000
900,000
800,000
700,000
600,000
500,000
400,000
300,000
200,000
100,000
0
Ford F-Series GM Chevy Fiat Chrysler Ram Toyota Tundra Nissa Titan
Silverado/GMC
Sierra

Column Chart

Fiat Chrysler Ram Ford F-Series

GM Chevy Silverado/GMC Sierra Nissa Titan
Toyota Tundra
Pie Chart

Number of Vehicles Number of Vehicles

Ford F-Series
Fiat Chrysler Ram 633,694

GM Chevy
Silverado/GMC Sierra
Food F-Series 896,526
Fiat Chrysler Ram

GM Chevy Silverado/
Toyota Tundra 807,923
GMC Sierra

Nissa Titan
Nissa Titan
0 200,000 400,000 600,000 800,000 1,000,000

Bar Chart
Toyota Tundra

Funnel Chart

Which chart is best for displaying these data? Explain your answer.
i. Column chart
ii. Pie chart
iii. Bar chart
iv. Funnel chart
Problems 63

5. NCAA Women’s Basketball. Since 1994, the NCAA Division I women’s basketball tour-
nament has had a starting field of 64 teams and, over the course of 63 single-elimination
games, a champion is determined. The following two charts (a funnel chart and a bar chart)
show how the tournament progresses from the starting field of 64 teams. LO 3
NCAA Women’s Basketball Tournament
First Round 64

Second Round 32

Sweet 16 16

Elite Eight 8

Final Four 4

Final 2
Champion 1

Funnel Chart

NCAA Women’s Basketball Tournament

First Round 64
Second Round 32
Sweet 16 16
Elite Eight 8
Final Four 4
Final 2
Champion 1

Bar Chart

Which chart best conveys these data? Why?

i. Funnel chart
ii. Bar chart
6. Worldwide Robot Supply. The International Federation of Robotics estimates the
worldwide supply of industrial robots each year. The charts below show estimates of the
worldwide supply of industrial robots for the years 2009 to 2021. The data are shown in
two different charts below, first is a line chart and the second a column chart. LO 3
Number of Robots (1000s units)
700

600

500

400

300

200

100

0
2009 2011 2013 2015 2017 2019 2021
Year
64 Chapter 2 Selecting a Chart Type

Industrial Robot Supply (1000s units)

630

553
484
421
381

294
254
221
166 178
159
121
60

2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021
Year

Which chart best displays these data? Why?

i. Line chart
ii. Column chart
7. R&D Project Portfolio. The Ajax Company uses a portfolio approach to manage their
research and development (R&D) projects. Ajax wants to keep a mix of projects to bal-
ance the expected return and risk profiles of their R&D activities. Consider a situation
in which Ajax has six R&D projects as characterized in the following table.
Each project is given an expected rate of return and a risk assessment, which is a value
between 1 and 10, where 1 is the least risky and 10 is the riskiest. Ajax would like to
visualize their current R&D projects to keep track of the overall risk and return of their
R&D portfolio. LO 3

Expected Rate of Capital Invested

Project Return (%) Risk Estimated ($ millions)
1 12.6 6.8 6.4
2 14.8 6.2 45.8
3 9.2 4.2 9.2
4 6.1 6.2 17.2
5 21.4 8.2 34.2
6 7.5 3.2 14.8

Which of the following chart types would be the most appropriate for these data?
Explain your answer.
i. Stacked bar chart
ii. Line chart
iii. Bubble chart
iv. Funnel chart
8. E-Marketing Campaign. Gilbert Furniture has initiated a new marketing campaign
for its high-end desk lamp. The analyst for e-commerce, Lauren Stevens, has been
tracking the progress of the campaign and has collected the following data based on an
email sent to the customer list: 68% opened the email, 29% clicked on the web link in
the email, 11% added the desk lamp to their cart, and 9% purchased the lamp. LO 3
Which of the following is the most appropriate chart for these data?
i. Funnel chart
ii. Stacked bar chart
 Problems 65

iii. Line chart

iv. Bubble chart
9. Choosing the Best Chart Type. Choose the most appropriate chart type (bar chart,
bubble chart, choropleth map, line chart) for each data set described below. Use each
chart type exactly once. LO 3
a. Advertising budget, number of salespeople, percent of market share for 10 products
b. Annual demand for potato chips (in tons) in the United States by state
c. Annual sales in millions of dollars for seven regional salespeople
d. Population of the United States for each year 1900–2020
10. Disney Ticket Prices. The three charts below show the price of a general admission
ticket to Walt Disney World for the years 2000 to 2020. The first is a bar chart, the
second is an area chart, and the third is a scatter chart. LO 3
Year
2020
2019
2018
2017
2016
2015
2014
2013
2012
2011
2010
2009
2008
2007
2006
2005
2004
2003
2002
2001
2000
0 20 40 60 80 100 120 140
Disney Ticket Price ($)

Disney Ticket Price ($)

140

120

100

80

60

40

20

0
2000 2005 2010 2015 2020
Year
66 Chapter 2 Selecting a Chart Type

Disney Ticket Price ($)

140

120

100

80

60

40

20

0
2000 2005 2010 2015 2020
Year

Which type of chart best displays these data? Why?

i. Bar chart
ii. Area chart
iii. Scatter chart
11. Exploring Private Colleges. For a sample of 103 private colleges, data have been
collected on year founded, tuition and fees (not including room and board), and the
percentage of undergraduates who obtained their degree within six years (source: The
World Almanac). The following two charts plot tuition versus year founded, and gradu-
ation rate versus year founded, respectively. LO 4
Tuition ($)
50,000

45,000

40,000

35,000

30,000

25,000

20,000

15,000

10,000

5,000

0
1600 1700 1800 1900 2000
Year Founded
Problems 67

Graduation Rate (%)

100

90

80

70

60

50

40

30

20

10

0
1500 1600 1700 1800 1900 2000
Year Founded

a. The two charts are the same type of chart. What type of chart are these?
i. Line chart
ii. Scatter chart
iii. Stock chart
iv. Waterfall chart
b. Which of the following statements best describes the relationship between tuition
and year founded?
i. Private colleges founded before 1800 are expensive, but there are greater differ-
ences in tuition for private colleges founded after 1800.
ii. There is no apparent relationship between year founded and tuition for private
colleges.
iii. The newer the private college, the higher the tuition.
c. Which of the following best describes the relationship between graduation rate and
year founded?
i. There is no apparent relationship between year founded and graduation for private
colleges.
ii. The newer the private college, the higher the graduation rate.
iii. Private colleges founded before 1800 have high graduation rates, but there are
greater differences in graduation rate for private colleges founded after 1800.
12. Vehicle Production Data. The International Organization of Motor Vehicle Man-
ufacturers (officially known as the Organisation Internationale des Constructeurs
d’Automobiles, OICA) provides data on worldwide vehicle production by manufac-
turer. The following three charts, a line chart, a line chart with a table, and a clustered
column chart, show vehicle production numbers for four different manufacturers for
five recent years. LO 3
68 Chapter 2 Selecting a Chart Type

Number of Autos (millions) TOYOTA GM VOLKSWAGEN HYUNDAI

10
9
8
7
6
5
4
3
2
1
0
Year 1 Year 2 Year 3 Year 4 Year 5

Number of Autos (millions) TOYOTA GM VOLKSWAGEN HYUNDAI

10
9
8
7
6
5
4
3
2
1
0
Year 1 Year 2 Year 3 Year 4 Year 5
TOYOTA 8.04 8.53 9.24 7.23 8.56
GM 8.97 9.35 8.28 6.46 8.48
VOLKSWAGEN 5.68 6.27 6.44 6.07 7.34
HYUNDAI 2.51 2.62 2.78 4.65 5.76
 Problems 69

Number of Autos (millions)

10

0
Year 1 Year 2 Year 3 Year 4 Year 5
TOYOTA 8.04 8.53 9.24 7.23 8.56
GM 8.97 9.35 8.28 6.46 8.48
VOLKSWAGEN 5.68 6.27 6.44 6.07 7.34
HYUNDAI 2.51 2.62 2.78 4.65 5.76

Which type of chart best displays these data? Why?

i. Line chart
ii. Line chart with table
iii. Clustered column chart with table
13. Smartphone Ownership in Emerging Countries. Suppose we have the following
survey results regarding smartphone ownership by age in emerging countries. LO 3, 4

Other Cell No Call

Age Category Smartphone (%) Phone (%) Phone (%)
18–24 49 46 5
25–34 58 35 7
35–44 44 45 11
45–54 28 58 14
55–64 22 59 19
65+ 11 45 44
70 Chapter 2 Selecting a Chart Type

Cell Phone Ownership by Age Smartphone Other Cell No Cell Phone

100%
90%
80%
70%
60%
50%
40%
30%
20%
10%
0%
18–24 25–34 35–44 45–54 55–64 65+
Age Category

Cell Phone Ownership by Age Smartphone Other Cell No Cell Phone

70%
60%
50%
40%
30%
20%
10%
0%
18–24 25–34 35–44 45–54 55–64 65+
Age Category

a. Which of the following charts best displays these data?

i. Stacked column
ii. Clustered column
b. Is the following statement true or false? Older people are less likely to own a smart-
phone than a different type of cell phone than younger people.
14. Home Goods Demand. In supply chain planning, demand is often measured in pounds
shipped. The following choropleth map shows the demand for home goods, measured
in millions of pounds shipped for each state in the United States. LO 4
a. Which five states have the highest demand?
b. Compare this map with Figure 2.19, which shows estimates of state population.
Does the demand for home goods appear to be related to population? Explain.
 Problems 71

Home Goods Demand Demand (in millions of pounds

shipped)
WA 87.6
MT ME
ND
OR MN
ID
SD WI NY 3.7
WY MI
IA PA
NV NE OH
UT IL IN
CA WV
CO KS VA
MO KY
NC
AZ TN
NM OK SC
AR
MS AL GA
TX
LA
FL

Powered by Bing
© GeoNames, Microsoft, TomTom

15. Coca-Cola Stock Prices. The following stock chart shows stock price performance for
Coca-Cola over a two-week period. Note that May 16 and May 17 are a Saturday and a
Sunday and are non-trading days. LO 4

Coca-Cola Stock Price Performance

Price per Share ($) Close
46.50

46.00
45.89
45.50 45.54
45.17
45.00 44.97 45.03
44.82
44.50 44.54

44.00 43.94
43.70
43.50
43.26
43.00

42.50
11-May 12-May 13-May 14-May 15-May 16-May 17-May 18-May 19-May 20-May 21-May 22-May
Date

a.
What type of chart is this?
b.
Which day seems to have the most intra-day price variability?
c.
What is the closing price on May 22?
d.
If you bought 100 shares at the closing price on May 19 and sold all of those shares
at the closing price on May 22, how much did you gain or lose (ignoring any trans-
action fees)?
16. Day Trading. In addition to the high, low, and closing price, an open-high-low-close
stock chart uses the opening price per share to give an indication of the net change in
the stock price from open to close on a given day. This is designated by a box inside the
72 Chapter 2 Selecting a Chart Type

high-low range. The range of the box is determined by the opening and closing price
per share. A black box indicates a loss and a white box indicates a gain for that day.
The length of the box indicates the magnitude of the loss or gain in share price. The
following chart is an open-high-low-close chart for a two-week period for Coca-Cola.
Note that May 16 and May 17 are a Saturday and a Sunday and are non-trading days.
Day trading is the practice of purchasing and then selling stock within the same day.
As a novice day trader, your strategy is to buy at the start of the day and sell at the end
of the day. LO 4

Coca-Cola Stock Price Performance

Price per share ($)
46.50

46.00

45.50

45.00

44.50

44.00

43.50

43.00

42.50

42.00

41.50
11-May 12-May 13-May 14-May 15-May 16-May 17-May 18-May 19-May 20-May 21-May 22-May

For which days would you have a gain, and which would you have taken a loss (ignor-
ing transaction costs) for the Coca-Cola data shown in the chart?

APPLICATIONS

17. Smart Speaker Usage. Futuresource Consulting conducted a survey of owners of

smart speakers to better understand how they use these devices (source: The Wall Street
Journal). The file SmartSpeaker contains the percentage of the respondents who use
SmartSpeaker
their smart speaker for each of 11 activities. LO 1, 2, 4
a. Construct a bar chart that shows the percentage of respondents by category. Use
“How People Use Their Smart Speakers” for the chart title. Edit the chart to make it
easier to interpret. Add data labels.
b. Sort the data so percentages are ordered from smallest to largest, and note the differ-
ences in the corresponding chart.
c. Of the categories in the survey, which is the most popular use of a smart speaker?
What is the least popular use?
18. Age and Ridesharing. A Gallup Poll showed that 30% of all Americans regularly use
a ride-sharing service such as Lyft or Uber. The file RideShare contains the survey
results by age category. LO 1, 2, 4
RideShare
a. Construct a column chart that shows the percentage of respondents who use a ride
share by age category. Use “Who Uses Rideshare?” as the chart title and “Age (years)”
as the horizontal axis title. Edit the chart to make it easier to interpret. Add data labels.
b. Comment on the results.
 Problems 73

19. Exploring Private Colleges (Revisited). In this problem, we revisit the charts in Prob-
lem 11 showing the relationships between tuition and year founded, and graduation rate
and year founded. The two charts are similar. Consider the data in the file Colleges. The
Colleges
file contains the following data for the sample of 102 private colleges: year founded,
tuition and fees (not including room and board), and the percentage of undergraduates
who obtained their degree within six years (source: The World Almanac). LO 1, 2, 4
a. Create a scatter chart to explore the relationship between tuition and percent who
graduate. Use “Graduation Rate versus Tuition” as the chart title, “Tuition” as the
horizontal axis title, and “Graduation Rate (%)” as the vertical axis title.
b. Comment on any apparent relationship.
20. Top Management. The Drucker Institute ranks corporations for managerial effec-
tiveness based on a composite score derived from the following five factors: customer
satisfaction, employee engagement and development, innovation, financial strength,
ManagementTop25
and social responsibility. The file ManagementTop25 contains the top 25 companies in
the Institute’s ranking based on the composite score (source: The Wall Street Journal).
For each company, the industry sector, the company name, and the composite score are
given. LO 1, 2, 4
a. Create a treemap chart using these data with the sector being the category, company
being the subcategory, and the composite score being the quantitative variable.
Use “Management Top 25” for the chart title. Hint: Be sure to first sort the data by
sector.
b. In the sector with the most companies in this top 25, which company has the highest
composite score?
21. Biodiversity Preservation. Ecologists often measure the biodiversity of a region by
the number of distinct species that exist in the region. Nature reserves are lands specif-
ically designated by the government to help maintain biodiversity. Care must be taken
Species
when setting up a network of nature reserves so that the maximum number of species
can exist in the network. Geography matters as well, as putting reserves too close
together might subject the entire network to risks, such as devastation from wildfires.
The initial step in this type of planning usually involves mapping the number of species
that exist in each region. The file Species contains the number of unique species that
exist in each of the 50 states in the United States. LO 1, 2, 4
a. Create a choropleth map that displays number of species by state. Use “Number of
Species per State” for the chart title. Add data labels.
b. Comment on the distribution of species over the United States. Which regions of the
United States have relatively many species? Which regions have relatively few species?
c. Which two states have the most species?
22. Disney Ticket Prices (Revisited). In this problem, we revisit Problem 10, which
displays the price of a general admission ticket to Walt Disney World for the years
2000 to 2020. However, these prices did not factor in inflation over these years. The
DisneyPricesAdjusted
file DisneyPricesAdjusted gives the general admission price and the general admission
price adjusted for inflation for the years 2000 to 2020. LO 1, 2, 4
a. Create a line chart that shows the price of admission and the adjusted price of
admission for the years 2000 to 2020.
b. Explain what the adjusted ticket price data series shows that the nominal ticket price
data series did not.
23. Bubble Chart Labels. The following bubble chart shows TSA wait time (in minutes)
on the horizontal axis, cheapest daily parking rate on the vertical axis, and the size of
each bubble is the number of enplanements in a year (measured in millions). The file
AirportBubbleChart
AirportBubbleChart contains this chart. LO 2, 4
a. Using the following steps, add labels to the bubbles so that the airport codes are eas-
ily identifiable with each bubble.
74 Chapter 2 Selecting a Chart Type

Step 1. Click anywhere on the chart

Step 2. Click the Chart Elements button and select Data Labels
This puts the value of enplanements in each bubble
Step 3. Click the Chart Elements button and move the cursor over Data Labels,
and then select the black triangle on the right Data Labels

Cheapest Parking Rate

$25.00

$20.00

$15.00

$10.00

$5.00

$0.00
0.00 2.00 4.00 6.00 8.00 10.00 12.00
TSA Time

to open a drop-down menu.

From this drop-down menu, select Other Options to reveal the Format
Data Labels task pane
Step 4. When the Format Data Labels task pane appears, under Label Contains,
select Value from Cells and click the Select Range…button
When the Data Label Range dialog box opens, select cells A2:A16 in the
worksheet
Click OK
Step 5. In the Format Data Labels task pane, deselect Y Value under Label Contains
a. In this problem.
b. Which airport has the lowest TSA wait time?
c. Which airport has the most enplanements?
24. Regional Gains and Losses in Population. The United States Univ Census Bureau
tracks shifts in population by each state and region in the United States. The net migra-
tion rate is an indicator of the movement of people from one area to another. A positive
NetMigration
net migration rate means more people moved to the area than moved away, and a
negative net migration rate means more people left the area than moved to the area.
Mathematically the net migration rate N is defined as follows:

(I ] E)
N5 3 1000
M
Where
I = number of people moving to the area in the year under consideration,
E = the number of people moving away from the area in the year under consideration, and
M = the mid-year population of the area. The file NetMigration contains net migration
rates for four regions of the United States. LO 1, 4
 Problems 75

a. Create a heat map using conditional formatting with the Blue-White-Red Color
Scale.
b. Which regions are losing population? Which regions are gaining population?
25. Income Statement. An income statement is a summary of a company’s revenues and
costs over a given period time. The data in the file BellevueBakery is an example of an
income statement. It contains the revenues and costs for last year for Bellevue Bakery.
BellevueBakery
Revenues include gross sales and other income. Costs include returns, cost of goods
sold, advertising, salaries/wages, other operating expenses, and taxes. In the income
statement there are intermediate calculations:
Net Sales = Gross Sales – Returns
Gross Profit = Net Sales – Other Income – Cost of Goods Sold
Net Income Before Taxes = Gross Profit – Advertising – Salaries/Wages – Other
Operating Expenses
Net Income = Net Income Before taxes – Taxes
Create a waterfall chart of the income statement for Bellevue Bakery. Use “Bellevue
Bakery Income Statement” for the chart title. Click the column associated with each
of the calculations above and select Set as Total. Edit the chart to make it easier to
interpret. LO 1, 2
26. Marathon Records. The file MarathonRecords contains marathon world records for
ages from 6 to 90 for women and men (records for ages 9 and 10 are missing). LO 1, 2, 4
a. Create a scatter chart with age on the horizontal axis and the women’s marathon
MarathonRecords
record on the vertical axis. Use “Female Marathon Records (in minutes)” as the ver-
tical axis title and “Age (years)” as the horizontal axis title. Edit the chart to improve
interpretation.
b. Create a scatter chart with age on the horizontal axis and the men’s marathon
record on the vertical axis. Use “Male Marathon Records (minutes)” as the vertical
axis title and “Age (years)” as the horizontal axis title. Edit the chart to improve
interpretation.
c. Create a scatter chart that plots both the women’s record versus age and the men’s
record versus age. Select Scatter with Straight Lines. Use “Marathon Records
(minutes)” as the vertical axis title and “Age (years)” as the horizontal axis title.
Edit the chart to improve interpretation.
d. Based on the charts in parts a, b, and c, what observations can you make regarding
the women’s and men’s marathon records?