Multiple Range references appearing with Conditional Formatting

[DashboardNovice]

After using a spreadsheet for a while, where I applied a conditional formatting rule to a range of cells, I noticed that if I select a cell or the entire range the conditional formatting should apply to, when I look into the Conditional Formatting Rules Manager window, I see either of the following (and sometimes both of these):

a. Many instances of the original rule, but using different cell references
b. The original rule is still there, but there are multiple cell references in the "Applies to" field, each of which are comma separated.

My questions are the following:

1. What causes situation 'a' above to occur?
2. What causes situation 'b' above to occur?

I would like to figure out how to prevent situations 'a' and 'b' from occurring.

 

[Chihiro]

a. Many instances of the original rule, but using different cell references

This can occur when you copy paste range / formatting.

Conditional format should never by copied & pasted unless that's the specific intention. Use copy, paste special, values only instead. And then apply and/or keep the format as is.

b. The original rule is still there, but there are multiple cell references in the "Applies to" field, each of which are comma separated.

When cut/insert operations are used, this can break up ranges into non-contiguous cell references. Instead of inserting row in middle of range. Add row at end of range, extend CF then sort (or just use Excel Table).

 

[DashboardNovice]

a. Many instances of the original rule, but using different cell references

This can occur when you copy paste range / formatting.

Conditional format should never by copied & pasted unless that's the specific intention. Use copy, paste special, values only instead. And then apply and/or keep the format as is.

b. The original rule is still there, but there are multiple cell references in the "Applies to" field, each of which are comma separated.

When cut/insert operations are used, this can break up ranges into non-contiguous cell references. Instead of inserting row in middle of range. Add row at end of range, extend CF then sort (or just use Excel Table).

Awesome explanation. This makes sense. Thank you.