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.
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.
➤ 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.
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.
➤ 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.
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
- 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
Get FREE Advanced Excel Exercises with Solutions!
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