PIVOT Disable Subtotal for Certain Value Fields

[DomSza]

Hi All,

I'm unsure if my problem is solvable - but I'm hoping someone might have a work around.

I have a Pivot Table, which is currently showing a Sub Total for 'Customer Name', which shows me Sub Totals for all of the information I have in the Values section of my Pivot Table.

However, I have multiple Cumulative columns in the Values section, and the subtotals are useless and unnecessary (see example below why this is useless). To avoid any confusion for my colleagues, I'd like to disable the Sub totals for these columns.

I've included an image below to make explaining easier - I'd like to turn off the subtotals in the red boxes, while keeping the other subtotals turned on. Any ideas?

PivotHelp.PNG

Thanks,
Dom

[clooysen]

Hope this little step-by-step helps a little bit. Select the pivot chart in question, select the drop down menu in your values section, select "Value Field Settings" the change the selection to "Count".
Example.docx

[romperstomper]

The short answer is no, you can't turn off individual subtotals. However, you could use conditional formatting to set the number format of all total rows for those fields to use a custom number format of ;;; which would suppress the display of the values.

[DomSza]

Hi All,

Thank you for your replies. Clooysen's solution only displayed the figures as 1, and subtotals as 12 - not really what I was looking for. Romperstomer seemed to have hit a winner though. Used a conditional format to check "=true" and set format to ;;; - which blanked them out nicely.

The next problem I've run in to, is the grand totals. Anyone any ideas?

PivotHelp.PNG

[romperstomper]

Since the first column should be fixed, select the first data cell in the relevant field use a conditional format applied to all Sum cells for that data field, using =$A11="Grand Total" and apply the same number format.

[DomSza]

You're a star, thank you for your help.

[romperstomper]

You're welcome. Have a good one.