Group in a Pivot table but keep several text data columns as columns..

[NunesJunior]

Hello All,

I have a table which has several text columns and some numbers columns.

I am trying to group it as you can see on the image, but I cannot do it in a manner that would allow me to keep the text data in columns rather than below the main group. In this example, the columns "Pais" and "IGPM+6% " supposed to stay in columns rather than in the breakdown. I found some suggestions that would be convert the text to numbers, put it in a pivot table, apply conditional formatting and replace the numbers with the former text value. It does work but I have several data and to maintain this kind of approach would be almost impossible.

Does anyone has some suggestion to do it in another simple way?

Excel.PNG

Thanks in advance.

[csmith-han]

I am not sure if I understand your question. Can you please upload a made-up spread sheet using the same datasets to your display; and I can see if I can help.

[NunesJunior]

Yes, sure. Please find attached the spreadsheets.

I am trying do make that the data (with red circle) appear as a column in the pivot table rather than as a breakdown.

Thanks a lot.

Excel.PNG

[csmith-han]

Is this what you are thinking of getting pivot table to do?

[NunesJunior]

It is almost it but I would like to repeat the values where is blank (like grouping in excel) and do not have the breakdown option for the columns other than the first and the second.

Excel.PNG

[csmith-han]

How about this pivot table? Is this what you want the pivot table to look like?

[sandy666]

I didn't see any file.
You can try
PivotTable Tools - Design - Report Layout -Show in Tabular Form,
and again
PivotTable Tools - Design - Report Layout - Repeat All Item Labels


If that takes care of your original question, please click Add Reputation then select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[csmith-han]

Over and above what sandy666 said, add to it the last step (in red):

PivotTable Tools - Design - Report Layout -Show in Tabular Form,
and again
PivotTable Tools - Design - Report Layout - Repeat All Item Labels,
and again
PivotTable Tools - Options - Show - +/- Buttons

If that takes care of your original question, please click Add Reputation then select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[sandy666]

But I like expand/collapse buttons