Changing line insert default formatting

When you insert a new row in a current spreadsheet, it automatically formats that row with the formatting from the line above. Is there a way of changing this so that it enters the formatting from the row below instead?

WALLY

Posted 2013-08-16T13:06:56.640

Reputation: 41

Answers

The Insert Options button - which is displayed when you insert a row and the adjacent cells have different formatting - is the only mechanism that Excel provides for controlling how inserted rows are formatted.

enter image description here

If the button is not being displayed, activate it by clicking the File tab on the ribbon and then clicking Excel Options. Click the Advanced tab, then check-mark the Show Insert Options buttons in the Cut, copy, and paste section and click OK.

enter image description here

chuff

Posted 2013-08-16T13:06:56.640

Reputation: 3 244

Despite the KB article states that this applies to Office 2013 too, I highly doubt that. My insert button looks like this.. Any ideas what might be wrong? (sry for the non-english screenshot, but there is no option like Format Same as Below, only Clear formatting)

– nixda – 2013-08-17T08:21:55.687

1Not sure, @nixda. I have 2010, and the Insert Options button shows up right after I've done the insertion. – chuff – 2013-08-17T16:13:02.510

Unless you want to venture into VBA programming, it's not possible to change the default.

FYI: in VBA you can use code like:

Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 
' default is xlFormatFromLeftOrAbove

SeanC

Posted 2013-08-16T13:06:56.640

Reputation: 3 439