Excel pivot table exclude some column totals

Question

I am using an Excel 2010 pivot table to display data. I need the sub totals in most columns but some columns display percentages and totaling the percentages is not correct and displays confusing values. Is there any way I can choose which columns not to total.

---

Below is an example (not by the original poster, as they never responded to requests for clarification) of a situation where one might want to hide subtotals and totals for some columns.

In the above, summing or averaging the average price per unit ($̅/unit) is not interesting, and summing the total number of units ordered by region or overall might not be interesting. Note how E20 and G20 are an appropriate sum of how many were ordered and how much was spent, but because F20 is not a weighted average, the math of E20 * F20 does not equal G20. We want to hide the subtotals and totals for the # and $̅/unit columns:

To make the above picture I used the best answer below to manually select each cell I did not want to show up and set the foreground color to be the same as the background. This works until some of the pivot table changes and different formatting is re-applied.

Answer 1

It's hard to make sure I'm accurately addressing your question without more detail about your data, but I will provide a simple example.

Recommendation

I would suggest that you put all measures/values in the Values area of the Pivot Table, and not in the Row Labels. That way, you will not get Row sub-totals for values.

In order to address the non-additive totals and sub-totals for your percentages, I recommend that you remove the pre-calculated percentages from your Pivot Table Values and instead use a Calculated Field that is calculated within the Pivot Table itself from the base data, and will provide correct aggregate totals and sub-totals.

Example

See below:

Method

In order to create a Calculated Field using the Ribbon, select your Pivot Table, and then go to PivotTable Tools --> Options --> Fields, Items, & Sets --> Calculated Field. Then, enter a name for the new Field (CalcPercentSoldUnits in my example below), as well as the formula definition for the field ( =UnitsSold /UnitsProduced in my example below). Click 'Add' to create the field and then 'OK' to exit the dialog box.

Now you have created a derived field not in your base data table, but in your Pivot Table, which can be used just like a normal field. You can also see that it will calculate totals and sub-totals correctly because instead of adding constants, it is calculating based on the sums of other constants from which the percentages are derived.

In some cases depending on how your data is structured, you may need to create a Calculated Item instead of a Calculated Field.

I hope this helps - if this doesn't address your situation, please post additional examples of your data and desired results. Thanks!

Answer 2

I have the same problem. Some Columns should be totaled, and others should not be. The only thing I figured out is to Grand Total all columns, then format those cells in the Grand total so that the font color matches the background color. This hides those selected Grand Totals from being viewed and/or printed. I experimented by expanding the Pivot Table rows, and the formatting for the selected cells followed into the new cell(s) and when I removed some of the rows, the formatting followed as well.