I construct following table in Excel 365: enter image description here

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: enter image description here

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?

  • 1
    When you paste a cell, there are options : Paste contents only, *paste formulas and formatting, etc. Try checking that?
    – NVZ
    Jan 27, 2016 at 12:53

3 Answers 3


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.

  • I just had a similar issue. I have a spreadsheet with several rows that have a static background color and bold font applied to them. When I type in the row underneath them, it stays with the default formatting except where there are two consecutive rows with the bold/background format. When typing in the row beneath that pair, Excel applies the same format to the cell I type in unless I press Ctrl-Enter. The most annoying part is that it doesn't even seem to use the AutoFormat mechanism, so there's no separate Undo entry for this format change! Nov 2, 2021 at 16:56

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



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

  • This still cannot explain the strange behaviour. I copied rows 1-2 to rows 6-7. I suppose that this does not influence the fact that I had applied conditional formatting to column D. But then cell E8 was suddenly coloured, although no conditional formatting had been applied to it, nor was anything (including cell formatting) copied to that particular cell.
    – Karlo
    Jan 28, 2016 at 21:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .