Home > Pivot > Layout > Data Analysis

Hockey Player Data Analysis Example

The sample files on this page have data from the 2018 Winter Olympic Hockey teams, from Canada and the USA. Download the file that has the data only, and experiment on your own. Or, get the file with my example analysis, using pivot tables to compare the teams.

Note: You many not resell this sample data, in whole or in part, or include it in paid products.

Author: Debra Dalgleish

Hockey Player Data - Introduction

At the 2018 Winter Olympics, the USA Women's Hockey team won the Olympic gold medal. They beat our Canadian team, in a hard-fought game that went into overtime, and ended with a shootout.

The hockey team rosters were available online, so I used those for a hockey player data analysis, using Excel pivot tables. Would the player data give any clues as to why the USA team won?

Since I'm not a hockey expert, or a master statistician, I'll leave the in-depth game analysis to someone else. Instead, here is my rudimentary player data analysis, based on the team rosters.

1) Sample Data - Hockey Players

The first sample data file has the team data that I downloaded and cleaned up, I also added columns with calculations, to create more data for sample analysis.

In the sections below, you can see where I got the data, how I organized it, and the Excel calculations that were added.

-- A) Team Rosters

-- B) Calculations

Get this File: You can get this sample file (#1) in the Download section, at the bottom of this page.

A) Team Rosters

The hockey team rosters were available online, so I used those for a hockey player data analysis

If you'd like to do your own analysis, you can find the hockey team rosters here:

Team Canada Site

• Women – Canada
• Men – Canada

• Women – USA
• Men – USA

• Women – All Teams
• Men – All Teams

Organize the Data

The rosters had player names, height, date of birth, etc., but each roster was set up slightly differently, so there was some cleanup work to do, before starting.

I rearranged the columns in each roster, and copied all the roster data into an Excel table.

On the main data sheet, there is an Excel table, with the following structure:

• There are 15 columns of data, including 4 columns with calculations, that I added later.
• There are 96 rows of data in the hockey player table.

Manual Entries

Each row shows the following 11 manual entry fields. I copied the roster data into these manual entry columns:

• ID: unique ID number for player
• Team: type of hockey team - Men, Women
• Country: country of hockey team - Canada, USA
• NameF: player's first name (given name)
• NameL: player's last name (family name)
• Weight: player weight in pounds
• Height: player height in feet and inches
• DOB: player date of birth
• Hometown: player's hometown city
• Prov: player's hometown province
• Pos: player's position on hockey team

B) Calulated Fields

Each row shows these 4 calculated fields:

• Age: player's age in years, based on date of birth
• HeightFt: player's height converted to feet, with 2 decimal places shown
• HtIn: player's height converted to inches (HeightFt x 12)
• BMI: player's BMI based on Weight and HtIn

The details for these calculations are in the sections below

-- 1) Age Calculations

-- 2) Player Height in Feet

-- 3) Height and Weight

Calc 1) Age Calculations

Before doing any player data analysis, a few calculations were needed in the rosters. First, the rosters had each player's date of birth, so what formula could you use to get each player's age?

To calculate the ages, I used this DATEDIF formula, but be careful with that function - it can give incorrect results in some versions.

• =DATEDIF([@DOB],TODAY(),"y")

Calc 2) Player Height in Feet

Next, I had to calculate the height, so it could be used in the data analysis. In the roster, the height was listed as text, with feet and inches, and a foot mark, such as 5'7 How would you convert that to a usable number?

I used the following formula to convert the text measurement to feet – for example, change from 5'7 to 5.58.

• =SUM(LEFT([@Height],1), RIGHT([@Height], LEN([@Height])-2)/12)

Here's how the formula works:

• The first character at the LEFT is the feet (there were no double-digit heights)
• The 1 or 2 characters at the RIGHT are the inches
• Those inches are divided by 12, to convert them into decimal portions of a foot
  • For example, 6 inches divided by 12 = 0.5 feet
• SUM the feet and the inch calculation, to get the height in feet
  • For example, 5'6 = 5.5 feet

Calc 3) Height and Weight

The weight was entered as a number, so that didn't need to be changed.

I thought it would be interesting to get a height/weight ratio, so I used the BMI calculation for that. weight in pounds / [height in inches x height in inches] x 703

First, I needed another column, to convert the height to inches, from feet.

That was a simple formula – multiple the calculated foot height by 12:

• =[@HeightFt]*12

Then, for the BMI calculation, I used the following formula – rounding to zero decimals, to limit the number of BMIs. (You could round to 1 or 2 decimals, if you needed greater precision. )

• =ROUND([@Weight]/([@HtIn]*[@HtIn])*703,0)

2) Sample Data - Hockey Player Analysis

This sample file has the same data as the file above -- 2018 Olympic Hockey teams, from Canada and the USA.

In addition to the data, this file includes several pivot tables that are described in the sections below

-- A) Summarize Data in Pivot Table

-- B) What Matters?

-- C) Team Comparisons

-- D) Why Did Team USA Win?

Get this File: You can get this sample file (#2) in the Download section, at the bottom of this page.

A) Summarize Data in Pivot Table

After the calculations were completed, I built pivot tables to show the player data analysis.

This video shows the steps for creating a pivot table in Excel 2013 and later, using different sample data.

For detailed steps on building an Excel pivot table, go to the Create a Pivot Table in Excel page.

B) What Matters?

• What age range is represented in the hockey teams. Are there differences among the teams? Did a team with younger players do better?
• You'd expect the players to be in top physical condition, so what does the height and weight data show? Did the bigger team win, or is there an advantage to being smaller?
• Finally, are the players from a snowy region? Most of Canada gets snow and ice in winter, but what about the USA players? Are they all from the northern states?

C) Team Comparisons

In the sample workbook, there are several pivot tables, comparing the teams. Details are in the following sections, below:

--1) Teams - Overview

--2) Spot Team Differences

--3) Player Age Ranges

--4) Player BMI by Position

--5) Home Town Locations

1) Teams - Overview

The first table shows player counts, for each position, plus age, height and weight.

Summary Functions

In the pivot table, the age, height and weight are shown using 3 different summary functions - Average, Minimum, and Maximum.

• After you add the value, right-click on one of the numbers
• Then, click Summarize Values by, and choose one of the functions.

Conditional Formatting

In the country subtotal row, conditional formatting highlights:

• higher amounts in red
• lower amounts in green.

At the top of the worksheet, click the Slicer, to select the team type -- in the screen shot below, the Women's data was selected.

Observation: On average, the USA team is a couple of years younger, a bit shorter and 4 pounds lighter.

Men's Teams Comparison

For the Men, the data is similar – the USA team is a little younger, shorter and lighter.

2) Spot Team Differences

For the next analysis, I wanted to see the differences between the teams' age, weight and height numbers.

Instead of doing the arithmetic in your head, you can get a pivot table to do custom calculations.

For these calculations, Iadded two more pivot tables, below the first one, connected to the same Slicer.

• first pivot table shows the difference as an amount
• second pivot table shows the percent difference, based on the amounts for Canada.

I hid the rows for Canada, because they are empty.

3) Player Age Ranges

Next, I created a pivot table and pivot chart, to show the age ranges for the Mens and Womens players, from both countries.

Observation: The men have a substantially longer career, from what this pivot table shows.

NOTE: I created a new Theme for the workbook, with red and blue as the first 2 accent colours. That way, all the charts had red and blue bars.

4) Player BMI by Position

Next, I set up a pivot table to show the BMIs, with a count of players in each position – Defence, Forward and Goalie.

• I changed the values to % of Column total
• From the pivot table, Icreated a stacked bar pivot chart to show the results.

NOTE: To keep the BMI list consistent, I set that field to "Show Items with No Data". Otherwise, some of the BMIs would not be listed, when you switch between the data for Men and Women.

Observation: There's a noticeable difference between the BMIs for the two Women's teams.

Or, just look at the player counts per BMI on each of the Women's teams – the patterns are quite different. Was that body type difference a factor in the USA victory?

5) Home Town Locations

Finally, to see where the player home towns were located, I used the 3D Maps feature in Excel 2016. That command is on the Insert tab, between Charts and Sparklines.

NOTE: The maps are not in the sample workbook – I created them in a separate file. If you have the 3D Map tool, you can build your own maps after you download my file.

I was surprised that the map was able to get the locations from the shortened state and province names, e.g. "Ont.", instead of requiring full names or official postal abbreviations.

Canadian Players

Here's the map of home towns for all the Canadian players. They're spread from coast to coast, with most cities close to the US border, and some further north, in the the prairie provinces.

USA Players

And here is the map of USA player home towns. Most are in the north and northeast, with a few scattered across the rest of the country. There were none in Alaska or Hawaii.

D) Why Did Team USA Win?

From my analysis of the data in the hockey player rosters, here's what I notices:

• USA Women team members were slightly younger, shorter and lighter, on average than Canada's team members.

Maybe that gave them an advantage against our Canadian team. Or maybe they're just better under the pressure of a game-deciding shootout!

If you analyze the data, you might find more clues about why the USA Women's team won, but I couldn't find any obvious answers.

Download Sample Data

1) Hockey - Data Only: Click here to get the hockey player data file, with the data only -- there are NO pivot table sheets. The zipped Excel file is in xlsx format, and does not contain any macros.

2) Hockey - Data Analysis: Click here to get the hockey data file, with pivot tables and pivot charts, analyzing the data. The zipped Excel file is in xlsx format, and does not contain any macros.

Related Links

Here are a few of my Contextures tutorials on working with data in Excel, organizing it in tables, and summarizing the data in pivot tables.

-- Excel Sample Data

-- Sample Data, Food Sales

-- Excel Tables

-- Pivot Tables

-- Data Entry Tips

Video – Gold Medal Game Highlights

Here's a video from CBC Sports, showing some of the Women's Hockey gold medal game highlights.