Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 How to autofill formula when inserting rows in Excel?

When you insert blank rows between existing data rows, the formula will not copy and paste into the new row, you should drag or fill it manually. But, sometimes, you would like to fill the formula when you insert blank rows between data range automatically as following screenshot shown, this article, I will talk about some quick tricks to solve this task.

doc autofill formula inserting row 1

Auto fill formula when inserting blank rows with creating a table

Auto fill formula when inserting blank rows with VBA code


Auto fill formula when inserting blank rows with creating a table


In Excel, the Table format can help you to fill the above formula into the new inserted blank rows automatically, please do as follows:

1. Select the data range that you want to auto fill formula, and then click Insert > Table, see screenshot:

doc autofill formula inserting row 2

2. In the Create Table dialog, check the My table has headers if there are headers in your data, see screenshot:

doc autofill formula inserting row 3

3. Then click OK button, and the table has been created, and now, when you insert a blank row, the above formula will be inserted into the new row automatically, see screenshot:

doc autofill formula inserting row 4


Auto fill formula when inserting blank rows with VBA code

If you don’t like to change the data to table, the below VBA code also can help you to achieve this problem.

1. Select the worksheet tab which contains the formulas you want to auto fill, and right click to choose View Code from the context menu to go to the Microsoft Visual Basic for Applications window , and then copy and paste the following code into the Module:

VBA code: Auto fill formula when inserting blank rows

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

doc autofill formula inserting row 5

2. Then save and close this code, go back to the worksheet, now, when you double click a cell between the data, a new row will be inserted below the active cell and the formula is auto filled as well.


Demo: Auto fill formula when inserting blank rows


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Marcelo Moreira · 5 months ago
    Excellent simple piece of code!!!!
  • To post as a guest, your comment is unpublished.
    Niket Shet · 11 months ago
    Hi,

    This code is very helpful, however, I have a slightly different requirement. The formulas should be copied when the row is inserted manually, i.e. by "insert row".
    Can you please resend the code after incorporating this change i.e. instead of "Worksheet_BeforeDoubleClick", some other trigger point to be used "insert row/etc"

    Note - I do have any exposure to coding being from the Accounts background.


    Regards
    Niket Shet
  • To post as a guest, your comment is unpublished.
    David Banks · 1 years ago
    I am using the code to insert a line on double-click. However, there is something wrong, Excel does not increment the first reference in my formula in the next but one row. However, it is corrected for all remaining rows.
    I inserted a line at row 144 and this is what I got: (note S144 in 3rd line should be S145)
    row 144 =(S143+D144+E144)-(K144+L144+M144+N144+O144+Q144+R144)
    row 145 =(S144+D145+E145)-(K145+L145+M145+N145+O145+Q145+R145)
    row 146 =(S144+D146+E146)-(K146+L146+M146+N146+O146+Q146+R146)
    row 147 =(S146+D147+E147)-(K147+L147+M147+N147+O147+Q147+R147)
    Any clues as to why it does this, I don't think it is the VBA???
    David
  • To post as a guest, your comment is unpublished.
    gt · 2 years ago
    Does the table method work in excel for xp (2002) and if yes, how?
  • To post as a guest, your comment is unpublished.
    RR · 2 years ago
    The code works very well, but does not update cell reference in first row after the inserted row.See below"
    e.g
    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    RR · 2 years ago
    This code is very good, but it does not update the cell reference in the first following row after the inserted row. Can you please help?
    e.g:

    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 2 years ago
    Above formula is very helpful for me please tell me how to use this code permanently
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 2 years ago
    dear above formula which helps to update the above formula on inserting row by double clicking. I want to know how to use this code permanently because when i close the sheet and reopen code finishes. Please help to solve the issue. This formula is very helpful for me
  • To post as a guest, your comment is unpublished.
    Bhavin · 3 years ago
    I followed the VBA code mentioned above for my xls. I have one .xls with multiple sheets in it , i want if i insert a row in my one sheet at the start or any of the sheet for that matter it should auto insert row in all my sheet in the same worksheet. Also each of my sheet as formulas hence i want the moment auto insert takes place the formula of above row should get autofill in the new row which is not happening totally if i follow the VBA code mentioned , the rows are getting inserted in all my sheets the formula also shows up but it misses one row in it.
    Eg. I have sheet A with 100 rows , sheet B with 100 ros and so on with other sheets. If I insert row in sheet A it shows auto insert in Sheet B , but sheet B values of some column are linked with sheet A and the moment i insert a row eg 10 , row 11 is a new row and the same row 11 in sheet B also shows up but the value it should pull from row 11 of sheet A from column C rather it shows row 12 of sheet A , this happens on any row i insert the value in sheet B is always of the next row rather than same row that i inserted. Another problem that comes here is with my first column Sr. no the moment i insert a row in 5 , sr no 6 with new row shows up but prior to that i already had row 6 as i have a formula for Sr. no A6+1 as my column starts from A6 and so on so how to sort this issue with 2 rows with same sr. no. This is very crucial sheet am working on and i need to get this sorted, i have refereed many VBA codes but none is effective. Hoping for a proper solution to my issue.