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.


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


enter image description here

  • I want to accomplish exactly what your first table looks like. The problem is that it creates two stacked column labels (i.e. for one value it creates four columns (that is what I want for that), but for the second, it tries to create all possible combinations of the two different value columns as the columns (I do not want that). – soandos Jan 25 '12 at 4:05
  • More accurately, I want one of the value columns to ignore the column labels – soandos Jan 25 '12 at 4:06
  • You will achieve the same result as my first example if you always drag and drop fields to the VALUES box. Even if you have a dozen of sums and averages and percentages (a dozen of columns), there will still be only ONE item in the column label area, that is "Values". In other words, never add something to COLUMN LABELS. – mtone Jan 25 '12 at 4:13
  • The best way to look at it is this: You add stuff to Row or Column LABELS when you want to SEE each individual item shown (such as companies, cities, years). You add stuff to the VALUES box when you want to do calculations: sums/counts/averages/etc. – mtone Jan 25 '12 at 4:14
  • 1
    @soandos If you click on a field, "Field Settings", "Layout" tab, you can pick Tabular form so the next item will show on a new column, or "Outline" + check "Compact Form" to have it show under (like my 2nd screenshot, where Type is below the Company name). – mtone Jan 25 '12 at 5:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.