How to AutoFill Formula When Inserting Rows in Excel (4 Methods)

We have some salespersons along with their Sales amount and Sales Commission. The Commission is 10%. We’ll use it to demonstrate how to AutoFill rows.

Method 1 – Creating a Table in Excel to AutoFill Formula When Inserting Rows

Steps:

• Select the range B4:D10.

• Press Ctrl + T. A Create Table dialog box will pop up. Tick the My table has headers box and click OK.

• Excel will create a table.
• Select a row and right-click your mouse to bring the Context Bar. Select Insert to insert a row. We will select the 8th row here.

• Excel has inserted a new row above the one selected earlier and has AutoFilled the formula, too.

• Complete the row.

Read More: How to Fill Down to Last Row with Data in Excel

Method 2 – Applying a Keyboard Shortcut in Excel to AutoFill Formula When Inserting Rows

Steps:

• Select a row. We chose the 8th row.

• Press ALT + I.
• Press R.
• Excel will insert a new row above the selected one.

• Select D8 and press CTRL + D.
• You will see that Excel has AutoFilled the formula.

• Complete the row.

Read More: How to Fill Column in Excel with Same Value

Method 3 – Using VBA in Excel to AutoFill Formula When Inserting Rows

Steps:

• Go to Developer tab and select Visual Basic.

• The Microsoft Visual Basic for Applications window pops up. Select Worksheet in the dropdown list.

• In the Declarations dropdown list, select BeforeDoubleClick.

• Insert the following code into the window:

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

We have created a Private Sub Procedure with a BeforeDoubleClick event with Target as Range and Cancel as Boolean. I have used the EntireRow.Insert property and Offset method. The Offset (2) determines that Excel will insert a new row after one row of the cell that is going to be selected.
We also used the Copy method to copy the formula from the previous cell. Also used the Range.SpecialCells method and ClearContents method.

• Save the code.

• Close the window.
• Go back to the original dataset and double-click any cell.
• Excel has added a new row. As we selected a cell from the 6th row, we have a new row at the 8th position.

• Complete the row.

Method 4 – Using Options to AutoFill Formula When Inserting Rows

Steps:

• Go to the File tab.

• Select More and choose Options.

• Select the Advanced tab.

• Check the Exchange data range formats and formulas box.
• Click OK.

• Insert a new row by following method 1 or method 2.
• Excel has automatically calculated the result by applying the corresponding formula.

• Complete the row.

Practice Workbook

We have attached a practice sheet to the workbook so you can experiment with these methods.

Download the Workbook

Related Articles

• How to Autofill Dates in Excel 
• How to Auto Populate from Another Worksheet in Excel
• Filling a Certain Number of Rows in Excel Automatically
• Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows

<< Go Back to Excel Autofill | Learn Excel