Excel Table Does Not Expand Automatically to Include New Data

editor
November 26, 2021
Articles
0 Comments

If you notice that Excel tables don’t automatically expand on your computer, to include new data, there might be a problem with the Excel Option settings. See how to change those settings manually, or use a macro to make the change.

Excel Table Ignores New Data

When you add new data on a worksheet, in the first blank row below a formatted Excel table, usually the table will expand automatically, to include that new data.

Sometimes, though, that doesn’t happen. Instead, the table just sits there, ignoring the new data.

Why does that happen, and how can you fix it?

Excel Table Does Not Expand Automatically for New Data

Excel Option Settings

If this happens to you, when you add new data below a table, here’s the first thing to check – the Excel AutoCorrect option settings.

Those settings are well hidden, so follow the steps below, to find them.

But first, here’s a warning:

These are application settings, not workbook settings.
Changing these settings will affect all workbooks that you open in Excel, on your computer.

Steps to Change Settings

Here are the steps to manually change the Excel AutoCorrect option settings:

At the top left of the Excel window, click the File tab
In the list at the left, click Options
- If you don’t see Options listed there, click the More... command
In the Excel Options window, at the left, click Proofing

Excel Options Proofing

In the AutoCorrect options section, click AutoCorrect Options

Excel AutoCorrect Options

Click the AutoFormat As You Type tab
Add check marks to these two settings:
- Include new rows and columns in table
- Fill formulas in tables to create calculated columns
Click OK, twice, to return to Excel

Excel AutoFormat Options

AutoFormat Settings Macro

To turn the Excel Table AutoFormat settings on with a macro, put this code into a regular code module in a workbook.

Then, run the macro when you need it.

Tip: Add a macro button to your Quick Access Toolbar

Sub ListAutoSetOn()

With Application.AutoCorrect
  .AutoExpandListRange = True
  .AutoFillFormulasInLists = True
End With

End Sub

Table Still Doesn’t Expand Automatically

Adding check marks to those Excel AutoCorrect settings should fix the problem, most of the time.

But sometimes that solution doesn’t work.

Maybe those settings already had check marks
Or, you added check marks, and nothing changed

If the check marks didn’t fix the problem:

Clear the rows below the table – data there might block the table expansion
Unhide rows below the table – something might be in those hidden rows

There are more details on the Excel Tables page on my Contextures site.

Also, see the comments on my previous blog post about this table problem. There are some good suggestions there!

Video: Excel Table Doesn’t Expand Automatically

To see the steps for setting up an Excel table, and checking the AutoCorrect options so it expands automatically, you can watch this short video.

Download the Sample File

To experiment with Excel tables, and to get the macro to change the AutoCorrect settings programmatically, please go to the Excel Table page on my Contextures website.

_________________________