Formula Errors

How to Fix Excel Table Formulas Not Autofilling in New Rows

Maira Mehtab Sep 28, 2026 870 views

Question details

Formulas and conditional formatting in an Excel table do not automatically extend when new text or data is pasted into the rows below the existing table.

Product: Excel
Device & OS: not provided
Scenario: Pasting additional rows of data at the bottom of an existing structured table.
Observed behavior: The table does not automatically expand to include the pasted data, preventing existing formulas and conditional formatting from autofilling into the new rows.

Before you start

Ensure your data is formatted as a formal Excel Table (Insert > Table), rather than just a standard data range with borders, as automatic formula filling only works within structured tables.

Solution 1Recommended

Enable Automatic Formula Filling in AutoCorrect Options

The most common reason table formulas do not extend is that the specific AutoFormat setting has been disabled in Excel's options.

Excel has built-in features to automatically create calculated columns and expand tables. If these settings are turned off, pasting new rows will bypass your existing formulas.

Open Excel Options

Click on 'File' in the top ribbon, then select 'Options' at the bottom left to open the Excel Options dialog box.

Navigate to AutoCorrect Options

Select 'Proofing' from the left-hand menu, then click the 'AutoCorrect Options...' button.

Enable the Autofill Features

Go to the 'AutoFormat As You Type' tab. Check the boxes for 'Include new rows and columns in table' and 'Fill formulas in tables to create calculated columns'.

Apply and Test

Click 'OK' to save your settings, then try pasting new data below your table to see if the formulas now expand automatically.

Solution 2

Clear Complex Formatting and Recreate the Table

Sometimes corrupted or conflicting conditional formatting rules prevent the table from expanding dynamically.

Work Smarter with WPS

Use WPS Spreadsheet to Automatically Manage Table Formulas

WPS Spreadsheet is a powerful, lightweight alternative that handles structured data tables flawlessly. It automatically expands formatting and formulas when you paste new data, ensuring your datasets remain accurate with zero manual configuration.

1. Open Your File in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet.
2. Format Data as a Table: Highlight your dataset and press Ctrl+T, or go to the Insert tab and select 'Table'.
3. Enter Your Formula: Type your desired formula in the top row of a column. WPS Spreadsheet will instantly autofill it down to the last row.
4. Paste New Data: Paste your new rows directly below the table. The table will automatically expand, applying all formulas and conditional formatting to the newly added data.

Fully compatible with Microsoft Excel (.xlsx) formats and table structures.Smart table features automatically expand formulas for newly pasted rows.Lightweight software optimized for quickly processing large datasets.Familiar user interface makes it easy to switch without a learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel table not expand when I paste data?

This usually happens if the 'Include new rows and columns in table' option is disabled in the AutoCorrect settings, or if you paste the data leaving a blank row between the existing table and the new data. Ensure there are no gaps when pasting.

How do I manually expand an Excel table to include new rows?

You can manually expand a table by clicking and dragging the small sizing handle (a small angle icon) located at the bottom-right corner of the last cell in the table down over the new rows.

Does conditional formatting automatically apply to new rows in a table?

Yes, as long as the data is formatted as an official Excel Table and the AutoFormat settings are enabled, any conditional formatting rules applied to the table columns will automatically extend to new rows added at the bottom.

Why did my calculated column stop autofilling?

A calculated column can stop autofilling if you manually typed different formulas or hard-coded values into some of the cells within that column, breaking the uniform formula rule. Re-entering the correct formula in the first row and hitting 'Enter' usually restores the autofill behavior.

Excel & Data Problems