Exercises Module 4 – Graphing in Excel
1. In case you haven’t done yet, create a (horizontal) bar chart for the frequency of the customer reviews
(from slide 11 of the PowerPoint file associated with Module 4).
2. Derek, Andrew, and Phil are having a mild-mannered argument about which country’s car brands are
most widely represented in their city. To resolve their argument, they agree to collect data based on cars
parked at the local mall. They collect information from 300 cars on the following countries and their
brands: America (i.e., General Motors, Ford), Germany (i.e., Audi, BMW), Japan (i.e., Mazda, Subaru),
Korea (i.e., Hyundai, Kia), and Other (i.e., Fiat).
a. Create a bar chart for the frequency of each country’s car brands. Note that this data is categorical,
but you need to find the number of observations per category yourself first.
b. Which country’s brands are most widely represented at the local mall near Derek, Andrew, and Phil?
Which is second most widely represented?
3. Caroline counted the number of customers to visit her downtown music store between March 1, 2023,
and June 30, 2023, ultimately hoping to improve the match between customer visits and staffing levels.
a. Create a histogram to gain insight into the distribution of daily customer visits to Caroline’s store. Use
the following bin ranges: ≤ 50, (50, 55], (55, 60], …, (80, 85], > 85.
b. Create a line graph to see whether there is any pattern (or trend) in the customer visit data.
4. Recall from Module 3 that we collected the height distribution of NFL players.
a. Create a histogram for the height (cm) distribution of the NFL players. Use the following bin ranges: <=
170 cm, (170; 173], (173; 176], … (200; 203], and > 203 cm.
b. Other than the NFL players, we have collected the same data for NBA and NHL players (see the
additional spreadsheets). Compare the height distribution of NBA, NFL and NHL players with the use
of three Box-and-Whisker diagrams in one figure.
c. Make a descriptive comparison between the height distributions of NBA, NFL and NHL players by
comparing the dispersion of the data (including the location and spread of the data) based on your
result from part (b).
5. We would like to see whether there is a relationship between the height and weight for NFL players.
a. Create a scatter plot with the height (cm) of the NFL players on the x-axis and the weight (lbs) of the
NFL players on the y-axis. Include a trendline. What can you conclude?
b. Other than creating one scatter plot for all players, we also would like to create a scatter plot that
illustrates the relationship between the height and weight for NFL players, but we would like to
distinguish between players in particular positions. Only consider the offensive positions (FB, OL, QB,
1
RB, TE, WR). Create a scatter plot similar to part (a), but only for the offensive positions, where each
position has a different color (i.e., six subsets of the data need to get shown in one scatter plot, where
each subset corresponds to a different position and shows as a different color in the graph). You can
create different colors for each of the six positions by creating six data series in your scatter plot
(similar like creating multiple data series in a Box-and-Whisker plot). What can you conclude?
