Make Formulas Continue When Adding a New Row
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.
Related Articles
- How to Fill Down Blanks in Excel (4 Quick Methods)
- AutoFill Ascending Numbers in Excel (5 Quick Ways)
- How to Repeat Number Pattern in Excel (5 Methods)
- Auto Number or Renumber after Filter in Excel (7 Easy Ways)
- Drag Number Increase Not Working in Excel (A Solution with Easy Steps)
Source: https://www.exceldemy.com/excel-autofill-formula-when-inserting-rows/
0 Response to "Make Formulas Continue When Adding a New Row"
Post a Comment