Excel Extension Tutorial: Creating a Transition Matrix in Human Resource Management
Chapter 6
Excel Extension: Now You Try!
Creating a Transition Matrix
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Background
In this Excel Extension tutorial, you will learn how create a transition matrix
with raw values and a transition matrix with proportions.
For the purposes of this exercise, imagine that there are four job titles of
interest: Project Manager, Project Consultant, Technical Consultant, and
Technical Assistant. In addition, we are interested in those who were not in
the organization in 2017 but who held one of these jobs in 2018, as well as
those who held one of these jobs in 2017 but who was no longer in the
organization as of 2018.
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Open the Excel workbook titled “Chapter 6 - Excel
Extension.xlsx”.
Click on the sheet called Tutorial. You will use the data
contained in this sheet to learn how to create a transition
matrix.
Note that the sheet includes three fields/columns:
•
EmployeeID (unique employee identifier)
•
2017 (job title in 2017)
•
2018 (job title in 2018)
Note that there are 79 employees in this sample.
Step 1
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Select the cells in the three fields.
Do not, however, select cells below the last row of data.
In other words, select the cells in columns A, B, and C and rows
1 ̶ 80.
Step 2
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Click on the tab called
Insert
.
Click on the
Tables
button.
Select
PivotTable
.
Step 3
1
2
3
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
A window called
Create PivotTable
appears. The
Table/Range:
field is already filled based on the previously selected cells.
Click on the bubble next to
New Worksheet
.
Click
OK
.
Step 4
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
A blank PivotTable appears in a new sheet.
Note that the field names EmployeeID, 2017, and 2018 appear
under
Choose fields to add to report:
, which is part of the
PivotTable Fields
window.
Step 5
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Drag the EmployeeID field name to the
Values
box (from the
list of field names that appears under
Choose fields to add to
report:
).
Step 6
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Drag the 2017 field name to the
Rows
box (from the list of field
names that appears under
Choose fields to add to report:
).
Drag the 2018 field name to the
Columns
box (from the list of
field names that appears under
Choose fields to add to
report:
).
Step 7
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Click the
X
in the upper right of the
PivotTable Fields
window to
close the box.
Step 8
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
The resulting PivotTable is a
transition matrix that contains
the raw number of individuals
who worked in a given job in
2018 based on which job they
worked in 2017. Specifically, the
Row Labels correspond to 2017,
and the Column Labels
correspond to 2018.
Next, we will edit the transition
matrix to make it easier to
interpret.
Step 9
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
To make the PivotTable more
intuitive to use, we will order the
job name rows such that the jobs
are ranked in hierarchical order,
with Not in Organization at the
bottom.
To do this, right click (or hold
CTRL and click if you have a Mac)
on a cell that appears under Row
Labels, select
Move
, and select
Move [“row label name”] to
[desired location]
(e.g.,
Move
“Not in Organization” to End
).
Repeat until the rows are in the
following order: Project Manager,
Project Consultant, Technical
Consultant, Technical Assistant,
and Not in Organization.
Step 10
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
The Row Labels should be
ordered as depicted in the
image.
Step 11
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Repeat the process from Step 10,
except in this case
right click
the
cell corresponding to each
column name, select
Move
, and
select
Move [“column label
name”] to [desired location]
(e.g.,
Move “Not in Organization”
to End
).
Step 12
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
The Column Labels should be
ordered as depicted in the
image.
Step 13
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
The transition matrix in the form
of a PivotTable is now in a more
interpretable format.
Again, this transition matrix
contains the raw numbers
representing how employees
transitioned through the
company from 2017 to 2018.
For example, as shown in cell
B6
,
2 individuals who were Project
Consultants in 2017 transitioned
to Project Managers by 2018.
Step 14
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
To create a transition matrix
containing proportions, right
click
on any cell containing a
value in the PivotTable, and
select
Value Field Settings…
.
Step 15
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
A window called
Value Field
Settings
opens.
Click on the
Show Values As
tab.
Click on the dropdown menu.
Select
% of Row Total
from the
dropdown menu after.
Click the
Number Format
button.
Step 16
2
1
3
4
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
A window called
Format Cells
opens.
Select
Custom
in the
Category:
dropdown menu.
In the field below
Type:
, enter
the following format: .00.
Click
OK
.
Step 17
2
1
3
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Click
OK
in the
Value Field
Settings
window.
Step 18
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Now the transition matrix
contains proportions as opposed
to raw counts.
Step 19
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Select the cells that contain the
row and column names, as well
as the proportions.
Click on the
Copy
button in the
Home
tab, or press CTRL+C (or
Command+C if you are using a
Mac).
Step 20
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Paste the copied cells from the
previous step below the
PivotTable by clicking the
Paste
button in the
Home
tab, or by
pressing CTRL+V (or Command+V
if you are using a Mac).
Step 21
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Although not necessary, consider
deleting the .00 values in the
new transition matrix to make it
easier to read.
Step 22
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Click on the header above the
first column (
A
) to highlight the
entire column.
Right click on the column header
and select
Insert
to insert a blank
column to the left of the first
column.
Step 23
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
In the empty row directly above
the recently pasted transition
matrix, enter the year 2017 in
the first cell above the Project
Manager
label, which
corresponds to 2018 job titles.
In the cell directly to the left of
the column containing the
Project Manager label, enter the
year 2017, as this column
references 2017 job titles.
Step 24
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Select the cells directly above the
2018 job titles, and click the
Merge
button.
Select the cells directly to the left
of the 2017 job titles, and click
the
Merge
button.
The transition matrix with
proportions is now formatted
and complete.
Step 25
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Questions
You just learned how to create a transition matrix in Excel. Now, it’s time to apply
your new skills to a different dataset, which appears in the sheet called “Practice” in
the Excel workbook; the job titles contained within the “Practice” sheet are (in
descending order in terms of job level): Floor Manager, Sales Associate, Cashier, and
Product Runner. Once you have created a transition matrix with raw values and a
transition matrix with proportions, respond to the following questions.
1.
Which job(s) showed the most turnover (left the organization) from 2017 to
2018?
2.
What other trends did you notice?
3.
In terms of workforce planning, what will you recommend to the organization
based on your interpretations of the transition matrices?
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Chapter 6
Excel Extension: Now You Try!
Creating a Transition Matrix
Bauer, Human Resource Management, First Edition. SAGE
Publishing, 2020.
Learn how to create a transition matrix in Excel for HR management. Follow step-by-step instructions to analyze job title transitions from 2017 to 2018 with sample data. Explore pivot tables to visualize employee movements within the organization.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Chapter 6 Excel Extension: Now You Try! Creating a Transition Matrix Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Background In this Excel Extension tutorial, you will learn how create a transition matrix with raw values and a transition matrix with proportions. For the purposes of this exercise, imagine that there are four job titles of interest: Project Manager, Project Consultant, Technical Consultant, and Technical Assistant. In addition, we are interested in those who were not in the organization in 2017 but who held one of these jobs in 2018, as well as those who held one of these jobs in 2017 but who was no longer in the organization as of 2018. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 1 Open the Excel workbook titled Chapter 6 - Excel Extension.xlsx . Click on the sheet called Tutorial. You will use the data contained in this sheet to learn how to create a transition matrix. Note that the sheet includes three fields/columns: EmployeeID (unique employee identifier) 2017 (job title in 2017) 2018 (job title in 2018) Note that there are 79 employees in this sample. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 2 Select the cells in the three fields. Do not, however, select cells below the last row of data. In other words, select the cells in columns A, B, and C and rows 1 80. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 3 Click on the tab called Insert. Click on the Tables button. 2 Select PivotTable. 3 Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 4 A window called Create PivotTable appears. The Table/Range: field is already filled based on the previously selected cells. Click on the bubble next to New Worksheet. Click OK. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 5 A blank PivotTable appears in a new sheet. Note that the field names EmployeeID, 2017, and 2018 appear under Choose fields to add to report:, which is part of the PivotTable Fields window. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 6 Drag the EmployeeID field name to the Values box (from the list of field names that appears under Choose fields to add to report:). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 7 Drag the 2017 field name to the Rows box (from the list of field names that appears under Choose fields to add to report:). Drag the 2018 field name to the Columns box (from the list of field names that appears under Choose fields to add to report:). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 8 Click the X in the upper right of the PivotTable Fields window to close the box. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 9 The resulting PivotTable is a transition matrix that contains the raw number of individuals who worked in a given job in 2018 based on which job they worked in 2017. Specifically, the Row Labels correspond to 2017, and the Column Labels correspond to 2018. Next, we will edit the transition matrix to make it easier to interpret. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 10 To make the PivotTable more intuitive to use, we will order the job name rows such that the jobs are ranked in hierarchical order, with Not in Organization at the bottom. To do this, right click (or hold CTRL and click if you have a Mac) on a cell that appears under Row Labels, select Move, and select Move [ row label name ] to [desired location] (e.g., Move Not in Organization to End). Repeat until the rows are in the following order: Project Manager, Project Consultant, Technical Consultant, Technical Assistant, and Not in Organization. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 11 The Row Labels should be ordered as depicted in the image. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 12 Repeat the process from Step 10, except in this case right click the cell corresponding to each column name, select Move, and select Move [ column label name ] to [desired location] (e.g., Move Not in Organization to End). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 13 The Column Labels should be ordered as depicted in the image. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 14 The transition matrix in the form of a PivotTable is now in a more interpretable format. Again, this transition matrix contains the raw numbers representing how employees transitioned through the company from 2017 to 2018. For example, as shown in cell B6, 2 individuals who were Project Consultants in 2017 transitioned to Project Managers by 2018. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 15 To create a transition matrix containing proportions, right clickon any cell containing a value in the PivotTable, and select Value Field Settings . Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 16 A window called Value Field Settings opens. Click on the Show Values As tab. Click on the dropdown menu. Select % of Row Total from the dropdown menu after. Click the Number Format button. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 17 A window called Format Cells opens. Select Custom in the Category: dropdown menu. In the field below Type:, enter the following format: .00. Click OK. 1 Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 18 Click OK in the Value Field Settings window. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 19 Now the transition matrix contains proportions as opposed to raw counts. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 20 Select the cells that contain the row and column names, as well as the proportions. Click on the Copy button in the Home tab, or press CTRL+C (or Command+C if you are using a Mac). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 21 Paste the copied cells from the previous step below the PivotTable by clicking the Paste button in the Home tab, or by pressing CTRL+V (or Command+V if you are using a Mac). Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 22 Although not necessary, consider deleting the .00 values in the new transition matrix to make it easier to read. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 23 Click on the header above the first column (A) to highlight the entire column. Right click on the column header and select Insert to insert a blank column to the left of the first column. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 24 In the empty row directly above the recently pasted transition matrix, enter the year 2017 in the first cell above the Project Managerlabel, which corresponds to 2018 job titles. In the cell directly to the left of the column containing the Project Manager label, enter the year 2017, as this column references 2017 job titles. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Step 25 Select the cells directly above the 2018 job titles, and click the Merge button. Select the cells directly to the left of the 2017 job titles, and click the Merge button. The transition matrix with proportions is now formatted and complete. Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Questions You just learned how to create a transition matrix in Excel. Now, it s time to apply your new skills to a different dataset, which appears in the sheet called Practice in the Excel workbook; the job titles contained within the Practice sheet are (in descending order in terms of job level): Floor Manager, Sales Associate, Cashier, and Product Runner. Once you have created a transition matrix with raw values and a transition matrix with proportions, respond to the following questions. 1. Which job(s) showed the most turnover (left the organization) from 2017 to 2018? 2. What other trends did you notice? 3. In terms of workforce planning, what will you recommend to the organization based on your interpretations of the transition matrices? Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
Chapter 6 Excel Extension: Now You Try! Creating a Transition Matrix Bauer, Human Resource Management, First Edition. SAGE Publishing, 2020.
