Constant range in conditional formatting

[Arek Pściuk]

Hi,

I have a problem with conditional formatting. I set formatting by my own formula and applied it to column A. It worked perfectly until I started editing data in column A. After a few times I pasted some data and deleted other data I found out that my rule no longer applies to column A but to set of ranges in column A (it looks like this"=$A$1:$C$49;$A$75:$C$1048576;$B$50:$C$74"). I understand why it happend but I would like to know if there is a way to keep it "=$A:$A" no matter what I do with the data inside. It's quite troublesome to change it every time I change my data.

[#2]

Hi Arek,

With Copy/Paste you paste formatting of cells as well, that includes conditional formatting - ranges are adjusted accordingly.

To avoid moving cell formatting you may use Paste->Formulas and Number Formatting instead of Paste (through ribbon menu, Ctrl menu, perhaps shortcut, Quick Access toolbar icon).

Another option is to change registry as here https://support.microsoft.com/en-us/help/973823/conditional-formatting-rules-are-duplicated-when-you..., however I'm not sure it works on latest versions of Excel.

[#3]

Thanks for quick response.

I figured it works properly wit "Paste values" option (or whatever it is called in english version) however it is quite annoying to search for that in ctrl menu every time and it takes more time than a simple ctrl+c and ctrl+v.

I will try the second method though and I will let you know if it worked.

Thank you for your help Sergei :)

[#4]

Arek,

Yes, it takes some time, but that's not something significant. In my daily work I quite often Copy (Ctrl+Ins) and Paste (Shift+Ins) as values (Ctrl, Down, Down, Enter). The latest takes less than a second and you don't need to search any icons, just use your keyboard.

If to paste formulas and number formatting as well, avoiding only cell formatting, that will be (Ctrl, Right, Right, Enter), etc.