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

Excel is one of the most useful tools when it comes to dealing with a large dataset. We often need to insert rows in a dataset. But normally Excel does not AutoFill the formulas when we insert new rows. You have to follow some steps. In this article, I am going to illustrate 4 methods in Excel to AutoFill formula when inserting rows.


Download Workbook

This is the workbook I am going to work with. We have some salespersons along with their Sales amount and Sales Commission. The Commission is 10%.

excel autofill formula when inserting rows


4 Methods to AutoFill Formula When Inserting Rows in Excel

1. Creating Table in Excel to AutoFill Formula When Inserting Rows

We can create a table  in Excel to AutoFill formula  when inserting new rows .

STEPS:

➤ Select the range B4:D10.

excel autofill formula when inserting rows

➤ Press CTRL + T. Create Table dialog box will pop up. Tick the My table has headers box and then click OK.

 ➤ Excel will create a table.
Now select a row and right-click your mouse to bring the Context Bar. Select Insert to insert a row. I am going to select the 8th row here.

excel autofill formula when inserting rows

➤ You will see that Excel has inserted a new row above the one selected earlier and has AutoFilled the formula too.excel autofill formula when inserting rows

➤ Now, complete the row.

Read More: How to Use Autofill Formula in Excel (6 Ways)


2. Applying Keyboard Shortcut in Excel to AutoFill Formula When Inserting Rows

Now I will explain another easy method to AutoFill formulas when inserting a new row. I will be using keyboard shortcuts.

STEPS:

➤ Select a row. I am going to select the 8th row.

excel autofill formula when inserting rows

➤ Now, Press ALT + I
➤ Then press R
Excel will insert a new row above the one I selected earlier.

excel autofill formula when inserting rows

➤ Now, select D8. Then press CTRL + D.
You will see that Excel has AutoFilled the formula.

➤ Now, complete the row.

excel autofill formula when inserting rows

Read More: How to Apply AutoFill Shortcut in Excel (7 Methods)


Similar Readings


3. Using VBA in Excel to AutoFill Formula When Inserting Rows

You can also use VBA to AutoFill a formula when you insert new rows.

STEPS:

➤ Go to Developer tab >> select Visual Basic.

excel autofill formula when inserting rows

➤ Microsoft Visual Basic for Applications window pops up. Select Worksheet in the dropdown list (See image)

➤ In Declarations dropdown list, select BeforeDoubleClick.

excel autofill formula when inserting rows

➤ Then edit the VBA code. The code will be like this.

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

excel autofill formula when inserting rows

Here, I have created a Private Sub Procedure with 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.
Then, I have 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.

➤ Now close the window. Then, go back to the original dataset and double click any cell. You will see that Excel has added a new row. As I selected a cell from the 6th row, I have a new row at the 8th position.

excel autofill formula when inserting rows

➤ Now complete the row.

excel autofill formula when inserting rows

Related Content: How to Number Rows Automatically in Excel (8 Methods)


4. Using Options to AutoFill Formula When Inserting Rows

Now I will show you another method by using Options to AutoFill a formula step by step.

STEPS:

➤ Go to the File tab.

➤Select More >> select Options.

excel autofill formula when inserting rows

➤After that, select the Advanced option.

excel autofill formula when inserting rows

➤ Then, tick the Exchange data range formats and formulas box. After that, click OK.

➤ Now insert a new row following method-1 or method-2.
➤ This time, you will see that Excel has automatically calculated the result applying the corresponding formula.

Now complete the row.

excel autofill formula when inserting rows


Practice Workbook

Practice makes a man perfect. That’s why I have attached a practice sheet so that you can internalize the methods.

Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)


Conclusion

In this article, I have explained 4 methods in Excel to AutoFill formula when inserting rows. I hope this will be helpful to the readers. Lastly, if you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.


Related Articles

Akib Bin Rashid

Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo