Organisation Chart in Excel

PJVV77

New Member
Joined
Aug 2, 2019
Messages
6
Hi,

I have been given a task to create an organisation chart for over 2,000 employees in Excel because it can then be presented in a specific format (effectively I am using grouping to expand and collapse rows to show how the hierarchy flows).

This is extremely time consuming and inefficient, albeit the final result is easy for people to work through (the first view for instance show the function head and his direct reports; by expanding to level 2 you see all their direct reports and this continues as you get to the lowest level - 8 levels in my case).

Is there anyway that this can be replicated in a more efficient way in Excel? I was looking at Power Pivot, but I am not sure how data would need to be ordered to achieve being able to see someone as an employee, and then in the next level his reports.

Any thoughts or guidance most welcome.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,233
Office Version
2010
Platform
Windows
I would create a "helper" column which defined what level the employees are at, and then write some vBA to run down this column grouping the numbers and when they change start a new group. using the
Range.rows.Group construct
 

Watch MrExcel Video

Forum statistics

Threads
1,099,000
Messages
5,465,938
Members
406,453
Latest member
MarkB5432

This Week's Hot Topics

Top