Locking Conditional Formatting Rules

Question

Hi,

I created a template with various conditional formatting rules that are only applied to columns.

The rule is meant to flag me for duplicates within the same column as shown. In this example I had to type in "MS" into each cell. I would like to be able to type it into the first cell and drag the text across, BUT....

I noticed that if I enter something into a cell and horizontally drag the text from the cell to populate the rest of that row it will also add the conditional formatting that was in the first cell to the row. In this example, I dragged the first "MS" horizontally to the right and it also copied the conditional formatting that was in the first cell therefore flagging me for duplicates across the row.

How can I avoid the conditional formatting from being dragged along with the text?

Is there a way to lock all my rules so that any user errors wouldn't cause the rules to bleed into other cells?

I have tried protecting the sheet but noticed that it has no effect on the conditional formatting rules.

Thanks

Answer 1

My apologies.

You can not avoid this as Excel is designed to work like this. If you are dragging a cell, you will be copying all attributes including formatting (and CF as well)

Only option is to copy them as values or if they were having formulas, copy only the formulas part.