Home > Data > Tables > Expand Fix Excel Table - Does Not Auto Expand How to fix the problem when a named Excel Table does not expand automatically, if you add new data at the end of the list. Video shows a common cause and solution. There are other solutions shown below too, if needed.

Table Does Not Expand Automatically

After you create a named Excel table on a worksheet, the table should expand automatically, if you add new data at the end, or at the right side. Also, any formulas that you add to the table should automatically fill down to the last row. For example:

• Add new data in the row immediately below a table, or in the column to its immediate right, and the table expands automatically, to include that new data.
• Enter a formula in the first row of a blank column, that formula fills down to all the remaining rows, as soon as you press Enter

However, if an Excel table does NOT expand automatically, try to fix the problem with one of the solutions shown below:

--1) Change AutoFormat Settings

--2) Clear Rows Below Table

--3) Check for Hidden Rows

1) Change AutoFormat Settings To fix an Excel table that doesn't expand automatically, you can change the Excel Table AutoFormat settings. This is a common cuase for the problem, so I usually check these settings first. NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer. Watch the video, to see me go through the steps, and there are written steps below the video.

2) Clear Rows Below Table

Another cause for this problem is data on the worksheet, below the Excel table. That can prevent tables from automatically expanding. The data could be space characters, or hidden characters that were copied from a website.

To fix the problem, try deleting or clearing the rows below your Excel Table

Here is a comment from Tully, on my Contextures Blog, explaining this type of problem, and how he fixed it:

• I had some unseen data lurking below my table. (Cells with a single space character in them which had been pasted in during some ad hoc testing long ago.)
• I assume that Excel sees populated cells below the table and doesn’t add them to a new listRow in the table.
• Cleared those values and it was working as expected again

3) Hidden Rows Below Table

Be sure that there aren't any hidden rows on the worksheet, below the Excel table. Those hidden rows could contain data that prevents tables from automatically expanding.

To unhide all the rows:

• Click the Select All button, at the top left of the Excel sheet
• Right-click on any of the Row buttons
• Click the Unhide command

Then, if necessary, clear the rows that were hidden, to remove any data. Or, move the data to a different location in the workbook.

Turn Table AutoFormat Settings On

If Excel tables are not expanding automatically on your computer, check the following settings, in the Excel Options window.

NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.

1. At the left end of the Ribbon, click the File tab, then click Options
2. In the Excel Options window, at the left, click Proofing

   

3. In the AutoCorrect options section, click AutoCorrect Options

   

4. Click the AutoFormat As You Type tab
5. Add check marks to "Include new rows and columns in table" and "Fill formulas in tables to create calculated columns"

   

6. Click OK, twice, to return to Excel

Now, when you add new data, or create a calculated column, the Excel table should adjust automatically.

Macro: Excel Table AutoFormat Settings

For a quick way to change the Excel Table AutoFormat settings, use a macro to turn these 2 settings or or off:

• Include new rows and columns in table
• Fill formulas in tables to create calculated columns

NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.

Macro 1: Turn AutoFormat Settings ON

To turn the Excel Table AutoFormat settings ON, use the following macro.

Sub ListAutoSetOn()

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

End Sub

Macro 2: Turn AutoFormat Settings OFF

To turn the Excel Table AutoFormat settings OFF, use the following macro:

Sub ListAutoSetOff()

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

End Sub

Expand Drop Downs in New Rows

In some Excel tables, where there are data validation drop down lists, those lists might not automatically expand to new rows at the bottom of the table.

Usually, this problem happens because one or more of the drop down lists have been removed from the existing rows in the Excel table.

• Perhaps someone copied a cell, without a drop down list, from another part of the workbook.
• Then, they pasted that cell into the Excel table, overwriting one of the drop down list cells.
• The inconsistent formatting confuses Excel, so the new row does not get a drop down list.

How to Fix the Drop Downs

To fix the missing drop-down list problem, try the following steps, to copy and paste the data validation settings (you will not be pasting over the data):

• In the Excel Table, select one of the cells that has a drop down list
• Press Ctrl + C, to copy the cell
• Select all of the data cells in the column where you copied the cell (do not include the heading cell)
• Right-click on one of the selected cells, and click Paste Special
  • Be sure to use Paste Special, so you don't paste over the existing data
• In the Paste Special dialog box, in the Paste section, click Validation
• Click OK, to apply the data validation settings to all the selected cells
  • The existing data in the cells will not be changed.

Get the Sample Files

1. Basic: To download the sample file with the tables for this tutorial, click here: Excel Table Sample File. The zipped file is in xlsx format, and does NOT contain macros.
2. Macros: To download the sample file with the tables and macros, click here: Excel Table Files With Macros. The zipped file is in xlsm format, and DOES contain macros

Related Excel Tutorials

Excel Tables

Excel Table Macros

Show Table Name on Sheet

Excel Table Slicers

Macro: Copy Table Rows