Org charts in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This table shows employees and their direct managers in columns A and B. The org chart will be created in column D using a formula to show the hierarchy starting from the top manager.

Cell	Value
A1	Employee
B1	Manager
A2	Alice
B2	Carol
A3	Bob
B3	Carol
A4	Carol
B4	David
A5	David
B5
D1	Org Chart
D2
D3
D4
D5

Formula Trace

=ARRAYFORMULA(IF(A2:A="", "", A2:A & IF(B2:B="", " (Top Manager)", " reports to " & B2:B)))

Step 1: A2:A

Step 2: B2:B

Step 3: IF(A2:A="", "", ...)

Step 4: IF(B2:B="", " (Top Manager)", " reports to " & B2:B)

Step 5: A2:A & IF(...)

Step 6: ARRAYFORMULA(...)

Cell Reference Map

    A       B       D
1 |Employee|Manager |Org Chart
2 | Alice  | Carol  |Alice reports to Carol
3 | Bob    | Carol  |Bob reports to Carol
4 | Carol  | David  |Carol reports to David
5 | David  |        |David (Top Manager)

Column A and B provide employee and manager data. Column D shows the org chart text created by the formula referencing columns A and B.

Result

    A       B       D
1 |Employee|Manager |Org Chart
2 | Alice  | Carol  |Alice reports to Carol
3 | Bob    | Carol  |Bob reports to Carol
4 | Carol  | David  |Carol reports to David
5 | David  |        |David (Top Manager)

The org chart in column D shows each employee with their manager or labeled as top manager if no manager is listed.

Sheet Trace Quiz - 3 Questions

What does the formula show for an employee with no manager listed?

A The employee is labeled as 'Top Manager'

B The employee is labeled as 'No Manager'

C The cell is left blank

D The formula shows an error

Key Result

ARRAYFORMULA with IF concatenates employee names with their managers or labels top managers when no manager is listed.