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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,302
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,102,578
Messages
5,487,668
Members
407,609
Latest member
stellaa9x

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top