Bird
Raised Fist0
Google Sheetsspreadsheet~15 mins

Org charts in Google Sheets - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
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.
Progress0 / 5 steps
Sample Data
Employee IDEmployee NameManager ID
1Alice
2Bob1
3Charlie1
4Diana2
5Eva2
6Frank3
7Grace3
8Hank4
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.

Practice

(1/5)
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