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


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.
|improve this answer|||||
  • Thank you Rajesh unfortunately that option is already checked. – jamiet Jul 26 '19 at 9:59
  • I updated the question accordingly – jamiet Jul 26 '19 at 10:02
  • @jamiet thanks for the feedback,, this was the only possibility,,, is it possible to edit post & add GIF of the Sheet so I & others could find what is happening with the Sheet,,, principally my post is the solution !! – Rajesh S Jul 26 '19 at 10:04

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.

|improve this answer|||||
  • ,,, reapplying CF on regular basis is the tedious job,,, especially when you are working with large data set to the option,, Preserve the Format,,,, & Refresh PT is technically correct & useful too. ☺ – Rajesh S Jul 26 '19 at 10:31

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.