0

I have ran into an annoying thing in Excel 2013 for a few times now, and I can't find any reason for this behaviour. Below are the steps to reproduce the weird behaviour: the question is, is this a bug in Excel 2013 or can I prevent this somehow?

Step 1: Create new, empty Excel workbook.

Step 2: Fill the first couple of rows with a colour (or apply bold text, or probably any formatting you want; in my case this happens with colour fill).

Step 3: Type some text in a cell on the bottom row of the coloured area, so that it becomes like a "header" for a column.

After step 3, it looks like this:

Excel workbook after step 3

Step 4: Hit 'Enter' and continue typing text in cells below the 'header', in the non-coloured area. Hit 'Enter' after each cell so that the cell in the next row below is selected.

Result: Suddenly after one or two rows, the cells pick up the colour fill from the 'header' area as I press 'Enter' (see screenshot below).

Excel workbook after step 4

So the formatting kind of spills over from the header area into the rows below. Nothing is done except typing "foo" and pressing 'Enter' after text has been typed in. No conditional formatting rules exist in the workbook.

What is going on and how do I stop this?

Background: I tend to use a coloured 'header area' for some ad-hoc spreadsheets, in which I can put all kinds of general information and column names. It gets very annoying when the header formatting is suddenly applied on the data rows... I also suspect this happened already in Excel 2010, but I cannot check that right now.

  • I've reproduced the problem/feature with Excel 2007. I was a little surprised when all of a sudden Excel started magically applying formatting. There seems to be some logic to when (at which row) Excel start applying the formatting; looks like it depends on how many header rows and how many of the header rows/cells have text. Don't know much else (maybe it's intended behaviour, maybe it can be switched off, don't know), but at least you now know it's not just your installation. – jehad Jun 30 '16 at 10:52
  • Huh, thanks. I was actually a little afraid that this might be some kind of crazy once-in-a-lifetime problem that only ever affects my particular system, since I couldn't find anything from SE or by googling. – Juha K Jun 30 '16 at 10:55
  • Yeah, same here, can't find anything by Googling, nor by looking through Excel's options (I have to believe it's a feature, because it's crazy to believe it's a defect that has been there since 2007). But, definitely not a one-off occurrence, since I'm also seeing the same behaviour on Excel 2010 on another PC. I know one way to stop it... use LibreOffice! :-) No problem there. – jehad Jun 30 '16 at 11:07
2

It is supposed to be a feature, and you can change it in the options.

It is called something like 'copy formulas and formatting into new rows' [I am not at a PC right now, will update later]. The sense is that if you have a certain format and/or formulas in further columns, when you continue typing new rows, it pulls these down.

There are Use Cases where this is quite useful.

Another thing that could happen is that if you start typing exactly the same text as in an existing row, Excel offers you the full text to copy it down (which copies the format down too). So if you type f, it will offer you foo, and if you hit return, it copies. That can also be changed in the options.

  • 1
    Ha, you're right! Under Advanced options there's a checkbox called 'Extend data range formats and formulas'. This was checked by default; when I removed this, the formatting is no longer applied automatically. When it's checked again, the problem re-appears. Thanks! – Juha K Jun 30 '16 at 11:24
  • +1'd thanks for the info. But, what's the logic behind how it works? This feature is applying the formatting after a gap, unlike text auto-fill. Do you know of any reference documentation please? – jehad Jun 30 '16 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.