Why does Excel automatically apply conditional formatting to other cells?

Question

I construct following table in Excel 365:

I select the entire column D and add conditional formatting.

Whenever I fill out an additional row, the cell in column D is coloured and the other ones remain white. This is the desired behaviour.

Now I want to copy header rows 1:2 underneath and add data rows to it:

When I enter a data row underneath, Excel automatically applies the rule defined for column D to the cells in column E. A quick fix is of course to select column E and click on 'Clear Rules from Selected Cells'. But how can I avoid this unwanted behaviour from happening?

Answer 1

I just ran into this myself. Excel is trying to be helpful, but this particular instance of 'help' is not something I normally appreciate. I found a way to avoid it - Press Control+Enter instead. That applies your edit to all selected cells, so make sure you've only got one cell selected (unless you actually want to edit multiple cells!).

Note that when you press Control+Enter, the selected cell doesn't move down like normal, but you can safely press plain-old Enter or arrow keys afterwards to move like normal.

Answer 2

I had a similar problem. I tried to reproduce it but couldn't find how.

Here is what I did to solve it:

1. Select all cell with no conditional formatting.
2. Click on "Cell styles" (beside "Conditional formatting).
3. Click on "Explanatory"
4. remove the italic and grey font manually

It's not a good solution but at least it seems to work

Greetings

Answer 3

Conditional formatting is part of the cell formatting. Paste your cells without formatting and you will not bring over the conditional formatting.