Akib Bin Rashid
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%.
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.
➤ 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 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.
➤ 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 Apply AutoFill Shortcut in Excel (7 Methods)
Similar Readings
- How to Create Automatic Rolling Months in Excel (3 Quick Ways)
- Excel Increment Month by 1 [6 Handy Ways]
- How to Auto Populate Cells in Excel Based on Another Cell
- Automatic Numbering in Excel (9 Approaches)
- How to Autocomplete Cells or Columns From List in Excel
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 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 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.
➤ Now complete the row.
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.
➤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 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.
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.
