Pivot Table with multiple rows/sub rows

[j_cole]

Hello,

I am new to pivot tables and I am trying to create a pivot table with multiple rows/sub rows of text that can be expanded/collapsed but the sub rows aren't populating the way I need them to.

In the attached spreadsheet, the first tab has the source data and the second tab is the results I am looking for. The third tab is the actual pivot of the source data.

When I try to create a pivot table with the source data, it creates a blank row with another blank row underneath and then it lists the sub categories. I know it creates the blank rows because there are empty cells but I am not sure to pivot the source data without selecting the empty cells.

If possible, in the pivot table I would like to be able to expand Identity and see asset mgmt. Then asset mgmt be able to expand and show the sub categories.

Any help is greatly appreciated. Thanks!

[southward]

Hi,

In order to get the results you are looking for, your source data has to be fully populated.

See attachment for example using your data.

Here is the trick to filling in all the blanks:
Select column A and B.
Select "Find & Select" menu, then "Go to Special". Select the "Blanks" button and hit OK.
Select your "equals" sign on the keyboard.
Hold the CTRL and hit Enter.
All the blank cells will be populated with formulas.
Copy all the column A and B data, and paste "values" back into the same cells.

Then you can do your Pivot table.

Hope this makes sense and is helpful.

Cheers

[j_cole]

Thanks! This is exactly what I was looking for. I appreciate the instructions.

I have one question about the procedure. When you mention:

Select your "equals" sign on the keyboard.
Hold the CTRL and hit Enter.
All the blank cells will be populated with formulas.

I did this and the blank cells were populated with the = sign. Then, I am not sure what to do when you mention:

Copy all the column A and B data, and paste "values" back into the same cells.

For an example, do you mean to copy Identity and paste it in cell A2-A7 and do the same with Protect (A9-A13) and so on? This is what I did and the pivot table came out like I intended it to.

[southward]

Opps. I forgot a step.
After entering the "equals" sign, use the up arrow once to select the cell above.
Then, hold the CTRL and hit Enter.

Sorry about that. :D

Cheers

[j_cole]

No worries! The forgotten step was the final piece to the puzzle.


Thanks so much for the help!

[southward]

You are very welcome.
Glad I could be of some assistance