5

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?

Improve this question

3 Answers 3

Reset to default
3

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

Improve this answer
2
  • 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, 2013 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, 2013 at 16:13
2

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
Improve this answer
1

Copied this from another forum.

The most easy solution which comes to my mind is to insert an extra hidden row.

So the hidden row will have the correct format you want. Then when you click on Insert, both the row above (the hidden row) or row below will have the correct format. Then you can even remove the Insert Options button if no longer needed and in the way.

Improve this answer

You must log in to answer this question.

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