Excel – Adding multiple value columns to a pivot table

microsoft excelmicrosoft-excel-2010pivot table

I have two value columns that I want to add to a pivot table, one an average, and one a sum.

When I add either one to the table, it look fine. When I try to add the other one, there is new entry in the Column Labels box that says values (essentially, all my averages or sums get turned into individual columns. That is NOT what I want).

I just want the two sets of columns (one is a set, one I want to be ignored by the current column labels) to exist peaceably side by side, resulting in a grand total one more column in the pivot table, as if the second value column was just another column that was part of the table.

Can this be done?

Edit: Assume my data has five columns; Name, Area, Price, Type, and Satisfaction. I want the companies grouped by area, have the prices for all the rows that contain an interaction with a company in an area summed with a breakdown by type shown, and have a satisfaction rating for the company in that area shown.

I use column labels, and a value to do the first part, but then when I try to do the second part, the column labels interfere.

Best Answer

Whenever you have more than one item to the Values box, Excel will need to show them side by side in some way.

The default is to show both values as side-by-side columns, that's why you're seeing this "value" item under column labels. It's there because you can drag it under rows labels to have them show on top of each other.

Example with 2 values as Columns:

Year    Average      Sum of 
       sale price    profit
2011        $50        $300
2012        $60        $300
Total       $55        $600

Edit: Here's how I would approach your example:

enter image description here

Or:

enter image description here

Related Question