Generate Reporting Hierarchy

I'll need to post a video for how to use this file, but for now I can still share it and talk through how it works.

Start by pasting your data into A:E; Employee ID, Employee Name, a Group label for your data, then Manager ID, and lastly Manager Name.  Then you'll enter your most senior leader into G1.  This is the employee who all other employees should roll through.

After you click "Generate Hierarchy" the file will prompt you to select which group you're generating a hierarchy for.  Typing 1 will generate the hierarchy for the entire population, but the option is there to only generate the hierarchy for employees within a specific group.  The group option is faster - especially when we're talking about organizations with tens of thousands of employees.  99% of the time I use option 1 here.

When the file finishes, you'll have 2 new tabs; PIVOT and Hierarchy.  The hierarchy tab is where we've looped through 20 levels of supervisor reporting relationships showing who reports to who up through your organization. Column AT is the "Status" which identifies "Infinite Loops"  (employees reporting to themselves or lower level direct reports - causing these loops) and "Dead Ends" which is an manager who isn't on your list - often a terminated employee.  Either of these reasons will cause the hierarchy to not reach the top person you identified.  Anyone else is labeled as "Okay".

The remaining columns build the hierarchy structure to make it work nicely with the pivot table.  M0 is the leader you identified.  M1 is their direct employees. M2 is 2-levels down...

The PIVOT tab is where we roll up the hierarchy.  If you add a COUNT of Employee Name, you can easily see how many people report to who at each level of your organization, or perhaps you add salary data and create SUM of Salary to show the total salary base rolling up to each leader through the organization.

Play with the data to determine what's meaningful for you and your organization, but this file should save you loads of time having to manually build all of these formulas.

In custom files, we can generate the hierarchy as a part of a larger process and identify which employees roll up through which leaders, or we can breakout data so that managers at a certain level are able to view all direct and indirect reports.  The possibilities are limitless, and it's always fun to find creative solutions to help achieve whatever the end goal is.

generate_hierarchy.xlsb
File Size:	54 kb
File Type:	xlsb

Download File

---