Formula Errors

How to Make Excel Formulas Fill Automatically in New Rows

Adam Davis Oct 9, 2026 869 views

Question details

The user needs formulas to automatically populate into newly added rows in a spreadsheet, especially when those formulas reference values from the previous row.

How to Make Excel Formulas Fill Automatically in New Rows

Product: Excel
Device & OS: not provided
Scenario: Inserting or adding new rows into an existing dataset where formulas must seamlessly calculate based on prior row values without manual intervention.
Observed behavior: Formulas do not automatically copy down to newly added rows, forcing the user to manually copy and paste the formulas each time a row is inserted.

Before you start

Ensure your dataset is organized in contiguous rows and columns, with clear column headers and no completely blank rows breaking the data.

Solution 1Recommended

Convert the Range to a Table and Use Structured References

Converting your standard data range into an official Excel Table enables the auto-expansion feature, allowing formulas to automatically copy to new rows.

Excel Tables have a built-in feature called 'Calculated Columns'. When you add a new row to the bottom or insert one in the middle of a table, formatting and formulas are automatically applied.

If your formula relies on the previous row's value (like a running total), using the OFFSET function combined with structured table references ensures calculations remain dynamic and accurate even when rows are inserted.

Select your data range

Click any cell containing data within your dataset.

Insert a Table

Go to the Insert tab on the ribbon and click 'Table', or press the Ctrl+T keyboard shortcut. Ensure the 'My table has headers' box is checked, then click OK.

Apply a structured formula

Enter your formula using table column names instead of standard cell references. For example, to reference a previous row dynamically, use a formula like: =IF([@Column2]>=4,1,[@Column2]/4)+OFFSET([@Column5],-1,0)

Test the auto-fill functionality

Press the Tab key at the end of the last row, or right-click to insert a new row in the middle. The formula will now automatically populate into the new row.

Convert the Range to a Table and Use Structured References

Formula Tip: In the provided example, replace 'Column2' and 'Column5' with your actual table header names. You can type '[' while writing the formula to see a dropdown list of your available column headers.

Manage Data Seamlessly with WPS Office

Auto-Fill Formulas in Spreadsheets Using WPS Office

WPS Spreadsheet fully supports data tables, structured references, and dynamic formulas. You can easily manage large datasets and automate calculations without manual copying.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
2. Create a Table: Select your data, go to the Insert tab, and click Table, or simply use the Ctrl+T shortcut.
3. Input your formula: Type your formula into the desired column. WPS Spreadsheet will automatically apply it to every row in that column.
4. Add new rows: Type new data in the row immediately below the table, or insert a row in the middle. WPS will automatically expand the table and copy the formula for you.

Automatically expand formulas to new rows using WPS TablesFully compatible with Microsoft Excel file formats (.xlsx)Lightweight, fast, and free to useAdvanced formula support including OFFSET, IF, and structured references

Frequently Asked Questions

Why did my table stop auto-filling formulas?

This usually happens if you manually edited or overwrote a formula in a specific cell, which breaks the consistency of the 'Calculated Column'. To fix it, rewrite or copy the correct formula into one cell and press Enter; the application should prompt you or automatically reapply it to the entire column.

Can I reference the previous row without using the OFFSET function?

Yes, you can use standard relative references (like A2 referencing A1) within a table. However, using the OFFSET function is generally safer for calculations like running totals, because if you insert a new row in the middle of the dataset, standard relative references might break or point to the wrong cell.

How do I find the actual names of my table columns?

Column names are simply the text located in your header row. When typing a formula inside a table, you can type an opening bracket '[' to reveal a dropdown menu of all available column names for easy selection.

Do structured references work in older spreadsheet files?

Structured references require the file to be saved in modern formats like .xlsx. If you are working in an older .xls compatibility mode, table features and structured referencing may be limited or disabled.

Excel & Data Problems