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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to AutoFill Formula When Inserting Rows in Excel: 4 Methods 

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

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 Fill Down to Last Row with Data in Excel


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 Fill Column in Excel with Same Value


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 the 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 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.
Then, I 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


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 by 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.


Download Workbook


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 below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

2 Comments
  1. Reply
    Maurits Meijndert Jun 16, 2023 at 12:53 AM

    Dear Akib,

    many thanks for the above info but I have the following issue.

    excel table is protected
    excel cells with formula are protected
    users are able to add or delete full rows by right clicking on the left side of the row, choose add (or delete) row.
    but when adding rows the locked formula’s in protected table are gone.

    throughout the table there are many protected cells with formula’s.

    would you be able to give me the right VBA code to add rows and keep format and protected formulas or deleted rows when options is selected.

    Many thanks in advance
    Maurits

    • Hi Maurits
      Thank you for your query.
      I have worked on your problem.
      From your comment, it seems like you have a table and you want to add rows to this table.
      However, it is not clear whether you have protected the entire worksheet or only the table.
      It will be helpful for us if you can send us the dataset at [email protected] and explain your problem in detail.
      Regards
      Akib
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo