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 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
- In the AutoCorrect options section, click 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
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.
Excel Table Does Not Expand Automatically to Include New Data
Original source: https://contexturesblog.com/archives/2021/11/25/excel-table-does-not-expand-automatically-to-include-new-data/?utm_source=rss&utm_medium=rss&utm_campaign=excel-table-does-not-expand-automatically-to-include-new-data