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
➤ 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.
➤ 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.
➤ 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.
➤ 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.
➤ 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 complete the row.
Practice makes a man perfect. That’s why I have attached a practice sheet so that you can internalize the methods.
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.