Excel – How to get excel to auto-outline hierarchy into groups

microsoft excelmicrosoft-excel-2010outline

So I have a bunch of data in an excel spreadsheet that looks something like this:

Level   Hierarchy       Name
0       1               Sam
1       1.1             Bill
2       1.1.1           George
3       1.1.1.1         Fred
4       1.1.1.1.1       Richard
4       1.1.1.1.2       Steve
4       1.1.1.1.3       Max
4       1.1.1.1.4       Sven
3       1.1.1.2         Mike
4       1.1.1.2.1       John
4       1.1.1.2.2       Isaac
4       1.1.1.2.3       Zack
2       1.1.2           James
3       1.1.2.1         Henry
4       1.1.2.1.1       Greg
            .
            .
            .

I'd like to automatically create groups in the worksheet such that Richard, Steve, Max, and Sven are grouped under Fred; John, Isaac, and Zack are under Mike; etc…; and they all roll up under Sam.

I tried to use the Excel auto-outline feature, but I get the message "Cannot create an outline." Can anyone tell me how to make this work or suggest another way of doing this?

The data comes from another system, but I can transform the data before it's imported into Excel — if that makes it easier.

Best Answer

If you have a finite number of levels, then I would do the following. If your data ever has to be refreshed, it has the advantage of being a simple pivot refresh (i.e. you set it up just once):

  1. Create new columns ( number=max level) to represent the person's boss at each level of the hierarchy (note, it assumes it is sorted by hiearchy field) enter image description here

  2. Formula in D3 and copied over and down: =IF($B3=D$2,$A3,IF($B3<D$2,"",D2))

  3. Create a pivot table (compact or outline view depending on your preference), setting each level boss as a row label
  4. Filter blank level bosses out at each level

enter image description here

Related Question