1

I have pivot table in an Excel workbook that summarises information from a table in the same workbook. I have applied Conditional Formatting (data bars) to the pivot table. I have added Year & Month fields to the Rows of the pivot table so that I can summarise data per month.

Each month goes by and I refresh the pivot table yet the conditional formatting does not get applied to the row that gets added for the new month.

Hence over time I end up with a pivot table that looks like this: enter image description here

Is there a method of ensuring that conditional formatting rules get applied to rows that get added to a pivot table in the future? Failing that, can I retrospectively modify the conditional formatting rule to apply it to the new rows? I have tried messing about with the "Applies to" part of the Conditional Formatting rule, but to no avail thus far.

I'm using a Mac by the way but I don't think that's relevant. The features I'm talking about here seem consistent with Excel on Windows that I have used in the past.

An answer below suggested checking the "Preserve cell formatting on update" option but unfortunately that option is already checked

enter image description here

jamiet
  • 305

3 Answers3

0

I've managed to sort this out by removing the existing conditional formatting and applying it again, including all the new rows this time. Obviously this isn't preferable so I am hoping that Rajesh's suggestion works when I refresh the pivot table on 1st August.

jamiet
  • 305
0

Rather than repeat everything on a new question posted today, see the solution to see if that works for you. Basically, edit your format rules and select the "All Cells Showing .... Values for ...." option.

enter image description here

Isolated
  • 1,535
  • 1
  • 5
  • 6
-1

Do the followings:

  1. Select any cell of your PT, then Right click.
  2. From poped up menu choose PivotTable Options.
  3. Now, from the dialog box click Layout & Format Tab.
  4. Check Preserve cell formatting on update item under the Format section.
  5. Finish with Ok, & refresh the Pivot Table.
Rajesh Sinha
  • 9,403