Retaining (or fixing) conditional formatting

Occasional Visitor

Microsoft Excel 2019 MSO 64-bit

Lenovo E490


I have a list (several lists) of daily data for entire years.

Each day has a value and a conditional format – typically the highest and lowest 5 percent.



I want to copy the values and formats to month-by-month lists and retain the appearance.


If the highest 30 entries are RED on the annual list, I want them to retain that redness on copying. I DO NOT WANT TO HIGHLIGHT THE TOP AND BOTTOM OF THE MONTHLY LIST.


This is not what I want to see.



The formatting on the monthly list is not conditional. All it has to do is look identical to the annual list.


As long as the offspring remain on the same sheet, I can drag them around, but I can't copy and paste.


I guess I could do this by manipulating the range (=$D$11:$D$19,$D$1:$D$7)


but I really don't need a complicated workaround unless I can use it reliably every day. Changing the range manually over several years will end in tears for sure.


What I want is a command like "retain original colors" - I feel I must be missing something.

1 Reply

@Monodon Hi there,

I just tried to understand the issue:

1 - You wanna to copy your file where conditional formatting applied. To do so, use Paste Special from where, first select, Format, Then Formulas or Values. Everything remains the same. Regarding colors, make sure that you use the same color pattern (Page Layout - Colors).

2 - If you want to divide table where Conditional Formatting applied into some tables based upon on Months, so Conditional Formatting range should be changed in each table manually.


Once again, I answered based upon on the level of my understanding.

Good luck.