Adjacent cells replicate format of previous cell

I have a basic spreadsheet set up to monitor my outgoings. When something clears my account I color the relevant cell in so I know its accounted for. However since I upgraded my Office, whenever I insert a line in my spreadsheet & then write in the new inserted line it automatically changes the cell format to match that of the previous coloured cells.

I don't want it to color them as those monies haven't cleared my account. This is driving me insane, there doesn't appear to be any conditional formatting set up. I don't know why it is doing this.

SamP

Posted 2013-10-11T17:09:00.527

Reputation: 21

1Actually, if I read right (Inserting a line after the active one), I'm also having that feature in Excel 2003. From what Office did you update? – Doktoro Reichard – 2013-10-11T17:11:51.380

Sorry, I can't remember what I had before only know that I hate 2010!! – SamP – 2013-10-11T20:22:46.110

Answers

Well, this is not an elegant solution... but create a no-color cell. Afterwards Excel should pick up the change and any lines inserted after will not have any color in the cells that haven't (i.e. it matches the previous line's format settings).

Doktoro Reichard

Posted 2013-10-11T17:09:00.527

Reputation: 4 896

hmmm...I think this may be the only way.... – SamP – 2013-10-12T19:19:11.327

I think what you're looking for is AutoFormat - specifically "Include new rows and columns in table". The settings for this are in options under Proofing then AutoCorrect Options:

enter image description here

Graham Wager

Posted 2013-10-11T17:09:00.527

Reputation: 10 589

thanks but it does it regardless of wether those boxes are ticked or unticked - I've tried that already! – SamP – 2013-10-12T19:18:01.837

When you insert a new row or column, you should see an "Insert Options" menu appear.

Insert Options

If you click on it, you can choose to "Clear Formatting".

Clear Formatting

If you can't see this menu, check the "Show Insert Options buttons" setting under Excel Options > Advanced.

enter image description here

bdr9

Posted 2013-10-11T17:09:00.527

Reputation: 2 508

Sounds like a brilliant answer however it doesn't show me the 'Insert Options' menu & on checking the Advanced tab the 'Show insert options' box is already selected! I can enable/disable 'Paste options' but it won't enable 'Insert Options'????! – SamP – 2013-10-14T10:04:52.727

I agree it is very annoying. But I found the setting that turns this off: Untick the "Extend data range formats and formulas" under Excel Options / Advanced settings. Actually, bdr9’s answer contains a picture where something is marked with a red box. Go half way up this pic. There it is.

David Strand

Posted 2013-10-11T17:09:00.527

Reputation: 1