# 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%.

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

âž¤ 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.

âž¤ You will see that Excel has inserted a new row above the one selected earlier and has AutoFilled the formula too.

âž¤ Now, complete the row.

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

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

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

âž¤ Now, complete the row.

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

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

âž¤ In the Declarations dropdown list, select BeforeDoubleClick.

âž¤ 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``````

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.

âž¤ Now complete the row.

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

âž¤After that, select the Advanced option.

âž¤ 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.

## Practice Workbook

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

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

## 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

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

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.

Maurits

• Hi Maurits