Forum Discussion
Constant range in conditional formatting
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-copy-and-then-pas, however I'm not sure it works on latest versions of Excel.
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 :)
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.