Excel pivot table one column per dimension

Question

How can I add multiple fields to the "row" area of a pivot table and have them show up as distinct columns on the pivot table instead of nested under one column only? I essentially need the pivot table to look like a standard table to I may do VLOOKUPS on several fields.

The pivot table below shows what I do not want. Instead, I'd like Field1 and Field2 to remain two columns:

Solution

I'm going to say again what I advised in a comment above - it doesn't sound like a PivotTable is really what you're looking for. However, you can get something like what you're describing by changing the PivotTable layout.

1. Click a cell inside your PivotTable to activate the PivotTable tools tabs

2. Go to the Design tab

3. On the left of the ribbon, look for Report Layout. Click it to open the dropdown menu.

4. In the dropdown, you'll want two options: Show in Tabular Form and Repeat All Item Labels

This will create the structure you describe, with each label in a different column.

Comments

[Werrf]

A PivotTable may not be the solution you're looking for. The whole idea of a PivotTable is to group data at several levels. Why are you using a PivotTable - what's the end goal of the overall process?

[heyydrien]

@Werrf I need to summarize several fields, sorry the minimum viable example I included does not show that. My actual data has 5+ categorical fields and 10+ numerical variables.

[Werrf]

Are you going to have multiple Marks, or multiple Firefighters, or both, or neither? If there's a 1:1 matchup of names to roles, you don't need to include both values in the PivotTable - you can just use whatever unique identifier you have and group by that. If the idea is to create a matrix-type data set, you may have more luck with formulae than Pivots.

[heyydrien]

The data has multiple observations for each Field1 Field2 combination because of additional fields in the data set.

[allquixotic]

It's not clear to me what you want, since Field1 and Field2 are already in two separate columns in the screenshot you provided. Can you manually create a table with a small set of the desired results and share that in addition to a small amount of input data? I don't have a good sense of how you want to structure the output.

[heyydrien]

@allquixotic I need Field1 and Field2 to be in two separate columns in the pivot table. Picture shows both the original data and the pivot.

[Werrf]

I understand that there's extra data that we're not seeing; my question is whether you really need BOTH Field1 and Field2 in the Pivot.

[heyydrien]

@Werrf yes, absolutely