Okay, this one isn't actually an Excel tool, and from what I've read I picked a pretty out-dated programming language to write this in. I originally wrote this in Python, but didn't share it because I knew not everyone had Python on their machines. Not being familiar with a lot of my other options, the project was shelved for nearly a year until an unrelated article lead me to Visual Basic - which works fine for my purposes.
Anyway, back to the file... It was inspired after my former boss told us about the Pomodoro technique for cycling between times of work and taking breaks. I wanted something more customizable, so I built this. When you first open the file, there are 3 prompts.
There isn't an OFF switch, so if you decide to kill it, you'll need to do so from the Task Manager.
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.
I'm happy to report this one went off without any major hitches on my end. The client ran into some data-related issues at multiple points in this project, which highlights the importance of having good data and maintaining consistency.
• Training Program Consolidation - Training and certifications are housed in half a dozen different source systems. Export those reports and save them all into a folder. From there we designed a master template and built code to cycle through all of those files, opening each, clean the data, and pull completion dates and statuses into our master - taking what was a multi-day task and automating it down to click a button, get your coffee, and when it'll be done by the time you return.
• Once our master list was built, we designed code to generate separate files by manager. We end up generating about 3,000 Excel files for managers which include their direct + indirect reports; "John Doe - EE ID.xlsx". We then send these managers an email and attach their file. If we need to password protect the files we could have, but not having any PII we opted to skip this step.
• Data Consolidation - Continuing with the above example, if the managers had to make changes or give feedback and then return all of these files to us. We can drag these files into a single folder, and cycle through that folder to copy / paste their data back into an updated master. Part of this process, we read the headers to ensure they align with our master, and we can flag any files where the headers appear wrong and we don't copy that data. Again, automating this consolidation saves hours of copying and pasting.
Lessons Learned: The only part I would have probably changes was the data distribution. Generating thousands of files took a couple of hours (granted you go to lunch and the computer keeps cranking) but then distributing thousands of files took even longer. It was about 5 hours before the emails cleared the outbox. In hindsight, I probably should have used our password-prompted encryption / decryption process. This way we could have only sent the one file out to all of the managers. Though we'd still have to communicate the passwords to the managers, these emails wouldn't have contained attachments and would have cleared the outbox much faster.