Excel Challenges, Learn Excel

Who is my boss’s boss? [Data Analytics Challenge – 001]

Last updated on August 8, 2025

Chandoo

Let’s try something different. I will share a data analytics challenge here. Post your solutions in the comments.

Our first challenge involves Employee Data Analysis. You can score maximum of 35 points.

Imagine you have employee data in this “staff” table (in Excel / Power BI / SQL / Python or whatever tool you fancy)

Download sample data file (it has all the 28 employees) or use the below CSV.

Emp ID	Name	Reports to
TO-0002	Jim Halpert	TO-0007
TO-0004	Pam Beesly	TO-0015
TO-0007	Michael Scott	TO-0067
TO-0009	Erin Hannon	TO-0004
TO-0010	Jan Levinson	TO-0067
TO-0013	Karen Filippelli	TO-0076
TO-0015	Dwight Schrute	TO-0007
TO-0018	Andy Bernard	TO-0002
TO-0021	Cathy Simms	TO-0002
TO-0024	Kevin Malone	TO-0035
TO-0028	Creed Bratton	TO-0007
TO-0030	Toby Flenderson	TO-0067
TO-0031	Kelly Kapoor	TO-0028
TO-0033	Robert California	TO-0067
TO-0035	Angela Martin	TO-0007
TO-0039	Stanley Hudson	TO-0007
TO-0043	Ryan Howard	TO-0031
TO-0046	Oscar Martinez	TO-0035
TO-0050	Meredith Palmer	TO-0028
TO-0051	Gabe Lewis	TO-0072
TO-0055	Phyllis Vance	TO-0030
TO-0059	Roy Anderson	TO-0004
TO-0063	Nellie Bertram	TO-0030
TO-0066	Darryl Philbin	TO-0039
TO-0067	David Wallace	TO-0072
TO-0068	Pete Miller	TO-0015
TO-0072	Jo Bennet	
TO-0076	Todd Parker	TO-0002

Emp ID	Name	Reports to
TO-0002	Jim Halpert	TO-0007
TO-0004	Pam Beesly	TO-0015
TO-0007	Michael Scott	TO-0067
TO-0009	Erin Hannon	TO-0004
TO-0010	Jan Levinson	TO-0067
TO-0013	Karen Filippelli	TO-0076
TO-0015	Dwight Schrute	TO-0007
TO-0018	Andy Bernard	TO-0002
TO-0021	Cathy Simms	TO-0002
TO-0024	Kevin Malone	TO-0035
TO-0028	Creed Bratton	TO-0007
TO-0030	Toby Flenderson	TO-0067
TO-0031	Kelly Kapoor	TO-0028
TO-0033	Robert California	TO-0067
TO-0035	Angela Martin	TO-0007
TO-0039	Stanley Hudson	TO-0007
TO-0043	Ryan Howard	TO-0031
TO-0046	Oscar Martinez	TO-0035
TO-0050	Meredith Palmer	TO-0028
TO-0051	Gabe Lewis	TO-0072
TO-0055	Phyllis Vance	TO-0030
TO-0059	Roy Anderson	TO-0004
TO-0063	Nellie Bertram	TO-0030
TO-0066	Darryl Philbin	TO-0039
TO-0067	David Wallace	TO-0072
TO-0068	Pete Miller	TO-0015
TO-0072	Jo Bennet	
TO-0076	Todd Parker	TO-0002

1. How many people are directly reporting to my boss? (5pts)

The first question is simple. For a given employee ID (say TO-0021, Cathy Simms), how many people are directly reporting to their boss (TO-0002, Jim Halpert)? The answer should be 3.

Write the necessary Excel formula / DAX / Power Query / SQL or Python code to find the answer.

2. Who is my boss’s boss? (10pts)

We are going to level up. For a given employee ID (say TO-0021, Cathy Simms), find out their boss’s boss. The answer should be TO-0007, Michael Scott.

3. What is my reporting chain? (Score=20pts)

For a given employee (say TO-0021, Cathy Simms), print their entire reporting chain, delimited by the symbol ->.

Expected Answer is:
Cathy->Jim->Michael->David->Jo

Download Sample File

Grab the sample data file (Excel format) here. Use it to solve the problems. Alternatively, I have pasted the data above. Copy it and paste it in Python or SQL.