Excel – create tree structure in excel 2010 pivot table

microsoft excelmicrosoft-excel-2010pivot table

I have a employee details data as follows:

Name Reporting To
Emp 1 Emp 10
Emp 2 Emp 10
Emp 3 Emp 1
Emp 4 Emp 2

Now, I want to pivot the data like

Emp 10
-Emp 1
–Emp 3

-Emp 2
–Emp 4

Query 1 : Excel pivot table allows to group for two level and I am not able to achieve this requirement. Could anyone share your idea how to achieve this?

Query 2 : Also, one more report I want to display like below.
Emp Name Total Number of people Reporting to
======== ===================================
Emp 10 4
Emp 1 1
Emp 2 1
Emp 3 0
Emp 4 0

Best Answer

I found an answer by myself. This is not possible directly with excel pivot table. So, I added one more column as Top Level Manager and had put "Emp 10" for all other employees too. When I group with the Top Level Manager first & Reporting To next, I got the count which I expected.

Related Question