Org charts in Google Sheets - Dashboard Guide

Dashboard Mode - Org charts

Dashboard Goal

Show the company hierarchy clearly so anyone can see who reports to whom and understand the team structure.

Sample Data

Employee ID	Name	Title	Manager ID
1	Alice	CEO
2	Bob	CTO	1
3	Carol	CFO	1
4	David	Lead Engineer	2
5	Eve	Engineer	4
6	Frank	Accountant	3
7	Grace	HR Manager	1

Place in A1:D8.

Dashboard Components

• Filter Dropdown: A10: "Filter Manager", B10 data validation List source: =TRANSPOSE(UNIQUE({"All";B2:B100}))
  C10: =IF(B10="All","",XLOOKUP(B10,B2:B100,A2:A100)) (selected ID)
  D10: =IF(B10="All",0,DEPTH(C10)) (root depth)
• Named Functions: Data > Named functions
  DEPTH(emp_id)=LAMBDA(emp_id,LET(mgr_id,XLOOKUP(emp_id,A2:A100,D2:D100),IF(ISBLANK(mgr_id),0,1+DEPTH(mgr_id))))
SUBTREE(root_id)=LAMBDA(root_id,REDUCE(TOCOL(root_id,1),FILTER(A2:A100,D2:D100=root_id),LAMBDA(acc,next,VSTACK(acc,SUBTREE(next)))))
• KPI Card: F2: "Total Employees", G2: =ROWS(F12#)
  Counts employees in filtered subtree.
• Filtered Data (hidden helper): F12 (spills):
  =LET(sel_id,IF(B10="All","",XLOOKUP(B10,B2:B100,A2:A100)),sub_ids,IF(sel_id="",A2:A100,SUBTREE(sel_id)),FILTER(A2:C100,ISNUMBER(MATCH(A2:A100,sub_ids,0))))
  Spills filtered ID, Name, Title.
• Org Chart Table: I11: "Org Chart", I12#:
  =ARRAYFORMULA(REPT("  ",DEPTH(F12#)-D10)&INDEX(F12#,0,2)&" - "&INDEX(F12#,0,3))
  Indented hierarchy (relative to root).
• Manager Lookup: K11: "Employee", L11: "Manager"
  K12#: =INDEX(F12#,0,2)
  L12#: =ARRAYFORMULA(IF(VLOOKUP(A2:A100,A2:D100,4,0)="","Top Level",VLOOKUP(VLOOKUP(F12#,A2:D100,4,0),A2:B100,2,0)))

Dashboard Layout

+---------------------+-------------------------+
| Filter: [All v]     | Total Employees [ 7 ]   |
| Data Table ...      | Org Chart Table         |
+---------------------| - Alice - CEO           |
|                     |   - Bob - CTO           |
|                     |     - David - Lead Eng. |
|                     |       - Eve - Engineer  |
|                     |   - Carol - CFO         |
|                     |     - Frank - Accountant|
|                     |   - Grace - HR Manager  |
+---------------------+-------------------------+
| Manager Lookup Table                          |
| Employee  | Manager                           |
| Alice     | Top Level                         |
| ...       | ...                               |
+----------------------------------------------+

Adjust positions as needed. Filtered tables update below.

Interactivity

Select manager in B10 dropdown. Tables and KPI update to show subtree (including selected manager) with relative indentation. Uses recursive LAMBDA functions.

Self Check

Select "Bob" in filter:

• Total Employees: 3 (Bob, David, Eve).
• Org Chart: - Bob - CTO
    - David - Lead Engineer
      - Eve - Engineer
• Manager Lookup: Bob - Alice
  David - Bob
  Eve - David

Key Result

Interactive org chart dashboard with filtering by manager subtree, indented hierarchy using recursive formulas, total count KPI, and manager lookup.