3

This drives me loopy. Sometimes (see below for when), Excel gets fixated on some cell formatting and starts applying it every time I fill in any new blank cell.

I don't think this happens on Excel for Windows (though it's ages since I used the Windows version much). I'm looking for some way to stop it doing this.

To replicate:

1: Set up a simple excel sheet with groups of, say, columns, and mark them as groups with formatting e.g. background colour.

enter image description here

2: Fill in a row with some data (just type it in)...

enter image description here

Excel decides that it likes that background colour so much, it's going to apply it everywhere from now on.

It's not just background colour:

enter image description here

It picks up the formatting from the last cell it entered text into, or the last cell formatted, then gets stuck and starts applying it after entering text to any new cell, like trailing mud across a carpet.

It doesn't only wreck things like columns: it also causes big problems any time background colour is used for anything meaningful e.g. to mark the status of a project:

In a blank cell...enter image description here becomes... enter image description here

Is there a way to stop it doing this? To get Excel to stop applying formats to empty cells I enter text into, and just leave their formatting how it is?

I've hunted through options and browsed online, but can't find anything related.

Some observations:

While creating the examples for this question, I noticed it looks like Excel is looking for patterns, then assuming I want to continue what it has decided to think is a pattern.

It seems to have two rules: "One thing, something else, then that one thing again always means start doing stripes and never stop"...

enter image description here ...or... enter image description here

...and "Two or more of something in a row when there has been a total of three of more of that thing always means carry on doing it forever".

enter image description here ...or... enter image description here

...but not... enter image description here

Oh, and it does it even if you skip a row or column:

enter image description here ... enter image description here ... * sigh *

I just want it to stop.

Please, help me make it stop.

Edit - after turning "Autocomplete" off as suggested by Buscar, the same thing still happens, including after restarting Excel after changing the setting (checking the setting is still applied after restart):

enter image description here ... enter image description here ... enter image description here

Improve this question
3
  • The closest I can find so far is to set blank cells to white fill. Excel seems to accept white cells as a valid formatting choice, and treats them with a respect it would never give to unformatted cells. Trouble is, white fill brings certain problems of its own, so I wouldn't call it an answer.
    – user56reinstatemonica8
    Jan 29, 2014 at 16:49
  • you used the word SOMETIMES ?? means what ?
    – Ruskes
    Jan 30, 2014 at 9:35
  • 1
    If you read the rest of the question, you'll see that I describe when it happens in as much detail as I'm able to with the information available, with screenshot examples. I'll edit it to make it extra clear though...
    – user56reinstatemonica8
    Jan 30, 2014 at 9:40

2 Answers 2

Reset to default
6

Found it! It's a setting under Preferences > Edit:

enter image description here

Extend data range formats and formulas

enter image description here

Misread it as "date ranges" on the first trawl through preferences. Turning it off stops this happening.

enter image description here

The comment about formulas seems to only mean that it doesn't fill empty cells with formulas when it thinks it's seen a pattern. After turning it off, it still does other automatic format adjustment e.g. incrementing formulas copied across a range:

enter image description here

Improve this answer
0

It is correct that excel is looking for repeat pattern, and trying to help you by applying the last used = AutoComplete.

If you do not want it to do that turn that feature off.

enter image description here

Improve this answer
1
  • Thanks for the suggestion. Unfortunately it doesn't work, the same thing still happens, even after restarting Excel after disabling autocomplete (autocomplete is still turned off after restart). I just tried it and have included screenshots showing the results at the end of the question.
    – user56reinstatemonica8
    Jan 30, 2014 at 9:47

You must log in to answer this question.

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