Org charts in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the number of direct reports for a manager

Given a list of employees in column A and their managers in column B, which formula in cell D2 correctly counts how many direct reports the manager named in C2 has?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
C2 contains the manager name to check
D2 is where the formula goes

A =COUNTIF(A2:A10, C2)

B =COUNTIF(B2:B10, C2)

C =SUMIF(B2:B10, C2, A2:A10)

D =COUNTIFS(A2:A10, C2)

💡 Hint

Think about which column lists the managers for each employee.

The COUNTIF function counts how many times the manager's name in C2 appears in the managers column B2:B10, which equals the number of direct reports.

❓ Function Choice

intermediate

Choose the function to find an employee's manager

You have a table with employees in column A and their managers in column B. Which function would you use in cell D2 to find the manager of the employee named in C2?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
C2 contains the employee name to find the manager for
D2 is where the formula goes

A =INDEX(A2:A10, MATCH(C2, B2:B10, 0))

B =HLOOKUP(C2, A2:B10, 2, FALSE)

C =VLOOKUP(C2, A2:B10, 2, FALSE)

D =MATCH(C2, B2:B10, 0)

💡 Hint

You want to find a value in the first column and return a value from the second column.

VLOOKUP looks for the employee name in the first column and returns the corresponding manager from the second column.

❓ data_analysis

advanced

Identify the top-level manager in an org chart

Given a list of employees in column A and their managers in column B, which formula in cell D2 correctly identifies the top-level manager (the one who is not anyone's employee)?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
D2 is where the formula goes

A =INDEX(B2:B10, MATCH(TRUE, ISNA(MATCH(B2:B10, A2:A10, 0)), 0))

B =COUNTIF(B2:B10, "")

C =VLOOKUP("", A2:B10, 2, FALSE)

D =INDEX(A2:A10, MATCH(TRUE, ISNA(MATCH(A2:A10, B2:B10, 0)), 0))

💡 Hint

The top-level manager appears in the managers list but not in the employees list.

This formula finds the first manager in B2:B10 who does not appear in A2:A10, meaning they have no manager above them.

🎯 Scenario

advanced

Create a formula to display the full reporting chain for an employee

You want to create a formula that shows the full chain of managers for an employee listed in C2, using columns A (employees) and B (managers). Which approach is best to build this chain in Google Sheets?

Google Sheets

A2:A10 contains employee names
B2:B10 contains their managers
C2 contains the employee name
D2 is where the formula goes

A Use a recursive custom function (Apps Script) to loop through managers until top-level

B Use a single VLOOKUP to find the direct manager only

C Use FILTER to list all managers in one formula without recursion

D Use COUNTIF to count managers in the chain

💡 Hint

A chain requires repeated lookups until no manager is found.

Google Sheets formulas alone cannot loop recursively; a custom script is needed to build a full chain of managers.

📊 Formula Result

expert

Determine the total number of employees under a manager including indirect reports

Given columns A (employees) and B (managers), which formula or method correctly calculates the total number of employees reporting directly or indirectly to the manager named in C2?

Google Sheets

A2:A20 contains employee names
B2:B20 contains their managers
C2 contains the manager name
D2 is where the formula or method result goes

A =SUMPRODUCT(--(B2:B20=C2))

B =COUNTIF(B2:B20, C2)

C =COUNTIFS(A2:A20, C2)

D Use a recursive Apps Script function to count all employees under the manager

💡 Hint

Counting indirect reports requires checking multiple levels, not just direct reports.

Only a recursive script can traverse the hierarchy to count all direct and indirect reports; simple formulas count only direct reports.

Practice

1. What is the main purpose of an org chart in a company?

easy

A. To create a list of company products

B. To calculate employee salaries automatically

C. To show the structure and relationships between employees

D. To track daily sales numbers

Solution

1. Step 1: Understand what an org chart represents

   An org chart visually shows who reports to whom and the hierarchy in a company.

2. Step 2: Identify the correct purpose

   Among the options, only showing structure and relationships matches the org chart's purpose.

3. Final Answer:

   To show the structure and relationships between employees -> Option C

4. Quick Check:

   Org chart = company structure [OK]

Hint: Org charts show who reports to whom [OK]

Common Mistakes:

• Confusing org charts with data tables
• Thinking org charts calculate numbers
• Mixing org charts with sales reports

2. Which Google tool is best suited to create an org chart visually?

easy

A. Google Slides or Google Drawings

B. Google Docs text editor

C. Google Sheets with formulas

D. Google Calendar

Solution

1. Step 1: Identify tools for visual diagrams

   Google Slides and Drawings allow easy creation of shapes and connectors for org charts.

2. Step 2: Compare with other tools

   Sheets is for data and formulas, Docs is for text, Calendar is for scheduling, so they are not ideal for org charts.

3. Final Answer:

   Google Slides or Google Drawings -> Option A

4. Quick Check:

   Visual org charts = Slides/Drawings [OK]

Hint: Use Slides or Drawings for org charts, not Sheets formulas [OK]

Common Mistakes:

• Trying to build org charts only with Sheets formulas
• Using Docs which is mainly text
• Confusing Calendar with diagram tools

3. Given this data in Google Sheets:
A1: Name
B1: Manager
A2: Alice
B2: Bob
A3: Bob
B3: Carol
A4: Carol
B4: (blank)
Which person is at the top of the org chart?

medium

A. Bob

B. Carol

C. Alice

D. No one, data is incomplete

Solution

1. Step 1: Identify the top manager

   Carol has no manager listed (blank), so she is at the top.

2. Step 2: Confirm hierarchy

   Bob reports to Carol, Alice reports to Bob, so Carol is the highest level.

3. Final Answer:

   Carol -> Option B

4. Quick Check:

   Top manager = no manager listed [OK]

Hint: Top person has blank or no manager in the list [OK]

Common Mistakes:

• Choosing the first name in the list
• Picking a person who has a manager
• Assuming data is incomplete without checking blanks

4. You tried to create an org chart in Google Sheets using formulas but it shows errors. What is a likely reason?

medium

A. You forgot to enter employee names

B. You need to enable org chart mode in Sheets settings

C. You used the wrong font style

D. Google Sheets formulas cannot create visual org charts

Solution

1. Step 1: Understand Sheets formula limits

   Sheets formulas handle data but cannot create visual org charts directly.

2. Step 2: Check other options

   Missing names or font style won't cause formula errors; no org chart mode exists in Sheets.

3. Final Answer:

   Google Sheets formulas cannot create visual org charts -> Option D

4. Quick Check:

   Sheets formulas ≠ visual org charts [OK]

Hint: Sheets formulas can't make org charts visually [OK]

Common Mistakes:

• Thinking Sheets has a hidden org chart mode
• Blaming font or missing data for formula errors
• Trying to draw org charts only with formulas

5. You have a list of employees and their managers in Google Sheets. How can you best prepare this data to create an org chart in Google Slides?

hard

A. Organize data with columns for employee and manager, then import or copy to Slides diagram

B. Write complex formulas in Sheets to draw the org chart directly

C. Use Google Docs to type the org chart manually

D. Create a pie chart in Sheets to represent the org chart

Solution

1. Step 1: Structure data clearly in Sheets

   Have one column for employees and one for their managers to show relationships.

2. Step 2: Use Slides to create the org chart

   Import or copy this structured data into Slides or Drawings to build the visual org chart.

3. Final Answer:

   Organize data with columns for employee and manager, then import or copy to Slides diagram -> Option A

4. Quick Check:

   Prepare data in Sheets, create chart in Slides [OK]

Hint: Prepare employee-manager list, then use Slides for org chart [OK]

Common Mistakes:

• Trying to draw org charts only with Sheets formulas
• Using Docs or pie charts which don't show hierarchy
• Not organizing data clearly before creating chart