Function Problems

How to Automatically Copy Excel Formulas to a New Row

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to automatically fill down formulas when adding a new row to an existing dataset in Excel, avoiding the need to manually copy or drag the fill handle.

Product: Excel
Device & OS: not provided
Scenario: Expanding an existing dataset by entering new data below the current range.
Observed behavior: By default, adding a new row may require manual intervention to drag and copy formulas from the row above. The goal is to make these formulas inherit automatically.

Before you start

Ensure your dataset is contiguous and does not have completely blank columns or rows separating the data, as this can prevent the software from properly recognizing the data range.

Solution 1Recommended

Convert the Data Range to an Excel Table

Using an official Excel Table is the most robust way to ensure formulas automatically copy down when you add new rows.

When you convert a standard data range into an official Excel Table, Excel activates a feature called 'calculated columns'. Any formula entered or already existing in a column will automatically expand to accommodate newly added rows without needing to drag the fill handle.

Select your data

Click on any single cell within your existing dataset that contains your data and formulas.

Format as Table

Press the keyboard shortcut 'Ctrl + T', or go to the 'Home' tab on the ribbon and click 'Format as Table'.

Confirm table range

In the Create Table pop-up dialog box, verify the selected range is correct. Ensure the 'My table has headers' box is checked if your data has titles, then click 'OK'.

Add a new row

Type new data in the row immediately below your table and press Enter. The formulas, formatting, and data validation from above will automatically apply to the new row.

Calculated Columns Feature: If you ever overwrite a formula in an Excel Table, an autocorrect options button will appear nearby, allowing you to instantly apply the updated formula to the entire column.

WPS Spreadsheet

Automatically Fill Formulas in WPS Spreadsheet

WPS Spreadsheet makes it incredibly easy to manage dynamic datasets. By formatting your data as a table, WPS will automatically extend your formulas to new rows, saving you from manual dragging and minimizing errors.

1. Open your worksheet in WPS: Launch WPS Spreadsheet and open the document containing your dataset.
2. Select the data: Highlight the range of cells or click any single cell inside your continuous data range.
3. Insert Table: Go to the 'Insert' tab and click on 'Table', or press 'Ctrl + T' on your keyboard.
4. Verify and apply: Confirm the data range in the dialog box, ensure 'My table has headers' is checked, and click 'OK'. Your formulas will now auto-fill for any new rows added at the bottom.

100% compatible with Microsoft Excel (.xlsx) formats and table featuresLightweight application that opens large data files quicklyFamiliar user interface makes switching completely seamlessFree to download and use for your daily office and calculation needs

microsoft office alternative - wps office

Frequently Asked Questions

Why did my Excel table stop automatically copying formulas to new rows?

This usually happens if you manually overwrite a formula cell with a hardcoded value, breaking the calculated column consistency. To fix it, rewrite the correct formula in one cell of that column and let the application automatically overwrite all other cells in that column.

Can I automatically copy formulas without converting my data to a Table?

Yes, you can rely on the 'Extend data range formats and formulas' feature. This requires having at least three preceding rows with the identical formula before adding the new row.

How do I turn on 'Extend data range formats and formulas'?

Go to File > Options > Advanced. Scroll down to the 'Editing options' section, check the box for 'Extend data range formats and formulas', and click OK.

Does Auto-fill work with dropdowns and formatting in new rows?

Yes, when you convert your range to an official Table using Ctrl+T, not only formulas but also data validation rules (like dropdown lists) and cell styling automatically apply to any newly added rows.

Excel & Data Problems