How to autofill formula when inserting rows in Excel?
When working with data in Excel, it's common to insert new rows within an existing dataset, perhaps to add new entries or update existing records. However, inserting blank rows between data can lead to a common issue: the formulas in adjacent columns typically do not copy down automatically into the newly inserted rows. This means you would normally have to manually use the fill handle or drag the formula down to cover the new rows, which can be tedious, especially if you frequently update your data.
This situation may cause calculation inconsistency or omissions, particularly if you overlook filling a formula in a newly added row. To illustrate, consider the following example where columns contain formulas and you insert a blank row between existing data—the formulas in adjacent columns are not carried over automatically:
To address this and ensure your formulas autofill whenever you insert new rows, you can use a few practical solutions. This article provides step-by-step guidance for the following approaches, each suited to different working habits and requirements. You will also find practical tips and troubleshooting suggestions to help you avoid common pitfalls.
➤ Auto fill formula when inserting blank rows with creating a table
➤ Auto fill formula when inserting blank rows with VBA code
➤ Auto fill formula using Excel Fill Down command
Auto fill formula when inserting blank rows with creating a table
One of the most effective ways to have Excel automatically fill formulas into new rows is by converting your data range into a Table. In the Table format, formulas entered into a column are considered part of a structured column and are automatically applied to any row added to the Table—whether you insert a row at the end or in the middle. This can save you the repetitive task of copying formulas and virtually eliminates errors from forgotten formulas.
To use this method, follow the steps below:
1. Select the data range where you need formulas to autofill. Then go to the Insert tab and click Table. Refer to this screenshot:
2. In the Create Table dialog box, make sure to check the option My table has headers if your range includes column headers. This preserves your headings and helps organize your data clearly. See screenshot:
3. Click the OK button to confirm. Now your data is formatted as a Table. From this point on, whenever you insert a blank row within the Table, any existing formulas within the Table’s columns will be automatically extended to the new row, as shown below:
This method is simple and reliable, and is recommended when you manage structured lists or ongoing records.
Tips and Notes:
- Tables apply formula autofill to all Table columns, making it easy to maintain consistency across large datasets.
- You can insert rows by right-clicking on a Table row number and selecting Insert, or simply by pressing Tab when positioned at the end of the Table.
- If a formula relies on cell references outside the Table, ensure those references use absolute addressing if needed, to avoid reference errors.
- If you delete the Table formatting, this autofill behavior will no longer apply.
- Formatting your data as a Table may not be desirable for some users who need freeform layout or advanced cell formatting.
Auto fill formula when inserting blank rows with VBA code
If working with Tables doesn’t fit your workflow or if your data requires a traditional range, you can use VBA to achieve formula autofilling after inserting a row. The VBA solution is flexible and can be customized to automatically copy formulas from above whenever you insert a new row, helping you maintain formula integrity across your dataset.
To use this approach, follow these steps carefully:
1. Firstly, select or open the worksheet tab that contains the formulas you want to autofill. Right-click on the sheet’s tab at the bottom of Excel, and choose View Code from the context menu. This will open the Microsoft Visual Basic for Applications editor. In the opened window, insert a new Module by clicking Insert > Module, and then copy and paste the following code into the Module:
VBA code: Auto fill formula when inserting blank rows
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub
2. Save and close the VBA editor. Return to your worksheet. Now when you double-click a cell within your data range, a new row will be inserted immediately below, and the formulas in adjacent columns will automatically be filled into the newly created row.
Additional notes for this approach:
- If you are prompted about macro security when reopening the workbook, enable macros in order for this code to run properly.
- VBA solutions offer flexibility across varied formatting situations, but require macro-enabled workbooks, which may not be suitable in tightly controlled or restricted Excel environments.
- When applying changes to shared workbooks or cloud-based documents, macro execution may be restricted—ensure that other users are aware and have proper permissions.
- Be mindful that double-clicking in areas outside your intended range may still trigger row insertion, so test thoroughly.
Auto fill formula using Excel Fill Down command
If you occasionally insert new rows and want a quick way to apply existing formulas—without converting your data into a Table or using VBA—the Fill Down command in Excel is a simple and effective option. This command allows you to copy the formula from the cell above into the selected cell(s) below with just a few clicks or a keyboard shortcut.
Operation steps:
- Method A: Drag the fill handle (the small square at the bottom-right corner of the selected cell) down into the blank cell.
- Method B: Use the Fill Down command:
- Go to the Home tab > Editing group > click Fill > Down
- Or press Ctrl + D to fill the formula into the cell below
Pros:
- No need to use Tables or VBA.
- Gives manual control over where and when formulas are applied.
- Quick and effective for one-off edits.
Cons:
- Not ideal for frequent row insertions or large datasets.
- Manual steps may lead to missed rows or inconsistent formulas if not carefully applied.
Troubleshooting tip: After inserting rows and applying Fill Down, review all formulas to ensure they reference the intended ranges—especially if you’re using dynamic references or cumulative totals. If you find yourself repeatedly applying formulas to new rows, consider using a structured Table or a simple VBA macro to automate the process more efficiently.
By using these methods, you can keep your data consistent, minimize manual work, and avoid errors due to missing formulas after adding new rows. Choose the approach that best fits your specific Excel environment and workflow requirements.
Demo: Auto fill formula when inserting blank rows
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in