Org charts in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are an HR analyst at a mid-sized company.

📋 Request: Your manager wants a clear organizational chart showing each employee and their manager to understand reporting lines.

📊 Data: You have a list of employees with their unique ID, name, and the ID of their manager.

🎯 Deliverable: Create a spreadsheet that shows each employee's name, their manager's name, and a simple org chart visualization using indentation.

Sample Data

Employee ID	Employee Name	Manager ID
1	Alice
2	Bob	1
3	Charlie	1
4	Diana	2
5	Eva	2
6	Frank	3
7	Grace	3
8	Hank	4

1

Step 1: Add a new column 'Manager Name' next to 'Manager ID'. Use a formula to find the manager's name by matching Manager ID with Employee ID.

=IF(C2="", "", VLOOKUP(C2, A$2:B$9, 2, FALSE))

Expected Result

For Bob (row 3), Manager Name shows 'Alice'; for Alice (row 2), it is blank.

2

Step 2: Add a new column 'Level' to show the hierarchy level. Top-level employees have level 1, their direct reports level 2, and so on.

=IF(C2="", 1, 1 + VLOOKUP(C2, A$2:E$9, 5, FALSE))

Expected Result

Alice has level 1; Bob and Charlie have level 2; Diana and Eva have level 3.

3

Step 3: Create a new column 'Indented Name' to visually show the org chart by adding spaces before the employee name based on their level.

=REPT(" ", E2 - 1) & B2

Expected Result

Alice appears with no indent; Bob and Charlie indented by 2 spaces; Diana indented by 4 spaces.

4

Step 4: Sort the data by 'Level' ascending and then by 'Manager Name' ascending to group employees under their managers.

Use Data > Sort range by 'Level' ascending, then 'Manager Name' ascending

Expected Result

Employees are grouped by their hierarchy level and manager.

5

Step 5: Create a simple org chart view by listing the 'Indented Name' column in order to show the reporting structure clearly.

Copy the 'Indented Name' column to a new sheet or area for presentation.

Expected Result

A clear list showing Alice at top, then Bob and Charlie indented, then their reports further indented.

Final Result

Alice
  Bob
    Diana
      Hank
    Eva
  Charlie
    Frank
    Grace

✓Alice is the top-level manager with no manager above her.

✓Bob and Charlie report directly to Alice.

✓Diana and Eva report to Bob; Frank and Grace report to Charlie.

✓Hank reports to Diana, showing a deeper level in the hierarchy.

Bonus Challenge

Create a dynamic org chart using Google Sheets' built-in drawing or chart tools that updates automatically when data changes.

Show Hint

Use the 'Indented Name' column and explore Google Sheets' 'Insert > Chart' options or use Apps Script to draw lines connecting employees to managers.