Organisation Chart in Excel

PJVV77

New Member
Joined
Aug 2, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top