Issues with a Pivot Table that has us stumped :(

[Vladski888]

Hey Guys, We have been using Excel for at least a decade now and would not consider ourselves to be beginners buttttt we have come across something that has had us stumped for a few days and were hoping to reach out to the true experts for advice. Basically, we need to create a simple Pivot Table that we have done in the past on previous Excel versions but does not seem to be working now. We have Four Categories (Category A, Category B, Category C, Category D) - that are represented in a single Column in the Data Source.We have Users at Row level (a few hundred of them) We have about 15 Columns with a number punch from 1-5. We would like to visualize the categories across the top in the pivot table We would like the count on Users as the Values And we would like the columns each to have a row and stacked on top of each other - without interlocking between each other with of course a total at the very bottom. The issue we face when we do this is that the columns get interlocked with each other and we can not get them to be independent of each other. We have tried going through every single tutorial we could find to fix the settings by adjusting the Report Layout, going to Classic Pivot Table and anything else we could find. But unfortunately have failed.If anyone could help us out it would be greatlyyyyyy appreciated!!!!!!!

 

[Vladski888]

Im really sorry as I think I'm not starting on the best foot with the shameless bump but is there any chance one of the Excel Gurus would be able to help me out as Im under a bit of pressure to get this done?

 

[tonyyy]

Vladski888,

Welcome to the Board.

It would help us help you if you post your source data (or at least a sample)...

By the way, did you try the Tabular Form for the Report Layout?

Cheers,

tonyyy

p.s. I'm not one of the "true experts," but have created my share of PivotTables.

 

[Vladski888]

Hi Tony,

I sure did but didnt work. I really appreciate your assistance in advance!

Here is the Raw Data:

Users	Categories	Selection 1	Selection 2	Selection 3	Selection 4	Selection 5	Selection 6	Selection 7	Selection 8	Selection 9	Selection 10
User 1	Category A	4	3	4	3	4	3	4	3	4	3
User 2	Category A	4	4	4	4	4	4	4	4	4	4
User 3	Category C	5	3	5	3	5	3	5	3	5	3
User 4	Category B	4	4	4	4	4	4	4	4	4	4
User 5	Category D	4	4	4	4	4	4	4	4	4	4
User 6	Category A	4	3	4	3	4	3	4	3	4	3
User 7	Category A	4	4	4	4	4	4	4	4	4	4
User 8	Category C	5	3	5	3	5	3	5	3	5	3
User 9	Category B	4	4	4	4	4	4	4	4	4	4
User 10	Category D	4	4	4	4	4	4	4	4	4	4

<colgroup><col width="48" style="width:36pt"> <col width="76" style="width:57pt"> <col width="75" style="width:56pt"> <col width="99" style="width:74pt"> <col width="91" style="width:68pt"> <col width="101" style="width:76pt"> <col width="89" style="width:67pt" span="4"> <col width="79" style="width:59pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Here is what comes out:

Count of Users			Categories
Selection 1	Selection 2	Selection 1 2	Category A	Category B	Category C	Category D	Grand Total
4	3	4	2				2
	3 Total		2				2
	4	4	2	2		2	6
	4 Total		2	2		2	6
4 Total			4	2		2	8
5	3	5			2		2
	3 Total				2		2
5 Total					2		2
Grand Total			4	2	2	2	10

<colgroup><col><col><col><col span="4"><col></colgroup><tbody>
</tbody>

Here is what I would need it to look like:

	Category A	Category B	Category C	Category D
Selection 1
Selection 2
Selection 3
Selection 4
Selection 5
Selection 6
Selection 7
Selection 8
Selection 9
Selection 10
Total

<colgroup><col width="99" style="width:74pt"> <col width="91" style="width:68pt"> <col width="101" style="width:76pt"> <col width="89" style="width:67pt" span="2"> </colgroup><tbody>
</tbody>

 

[tonyyy]

So... not sure if this will achieve the layout/results you're after, but you might try...

1. Drag the Categories field to the Column Labels area
2. Drag the Selection 1 field to the Values area
3. Drag the Selection 2 field to the Values area
4. A new field will pop in - a sum value field - drag it to the Row Labels area
5. Continue dragging the remaining Selections to the Values area