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:
Or:
Use can use a Value Filter for this purpose. Assign a value filter on a Row Label column, usually one that contains one distinct value per row, such as ACC_NO in your example.
Here's a simple example that will hide all negative totals (the 3rd row). I picked the Quarter column to hold the filter (more on this below).
To enter a Value Filter, simply go in the filter drop-down on the column you want to apply your filter on:
How to choose which column to apply a total filter on?
Note that I chose to do the filter on QUARTER instead of ITEM because the elements of this column are not grouped. Therefore, in this case, the filter will be applied to each individual row.
If instead I did the same value filter on the Item column (where we can see Item B is grouped), then the filter would apply on the subtotal for each group. For example, since the sum of Q1 and Q2 for Item B is negative (-5), both Item B rows would be filtered out.
In your sample, all your rows are unique because of the leftmost ACC_NO, so you would get the same result by placing the filter in any column.
Final thoughts:
- If you have multiple totals in your Values area (eg. a count and a sum), you can pick which one to filter against in the dialog.
- If you have a Column Label to split your totals, the Value Filter will be applied against the grand total only.
- Applying a Value Filter on a Column Label instead of a Row Label will perform the filter against the vertical totals at the bottom.
Best Answer
This issue is identified by Microsoft... see http://support.microsoft.com/kb/211470/en-us.
But I think a way to achieve this is to use an calculated item in the pivot. For example, if you have a dataset like this :
A calculated item with this formula :
=SUM(ABS(id[1])+ABS(id[2])+ABS(id[3])+ABS(id[4]))
will produce a result of 1800, instead of 0 with the pivot table grand total. Notice that you need to have an "id" column in your data set. In the calculated field,id[i]
, withi
indicate the item number as appears in the pivot table. For your case you have to iterate the formula until 20.If you have a lot of rows or if the number of rows can vary, you could look to a macro for build automatically the calculated item.