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?


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

  • 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 Aug 17 '13 at 8:21
  • 1
    Not sure, @nixda. I have 2010, and the Insert Options button shows up right after I've done the insertion. – chuff Aug 17 '13 at 16:13

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

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.