Excel Table Not Expanding? Make New Rows Join the Table

When an Excel table is not expanding, new rows sit just below it without its colours, filters or formulas, and anything that uses the table ignores them. The cause is almost always one of five things: a gap between the table and the new data, the total row, a switched-off AutoCorrect setting, a protected sheet, or data that was added in a way Excel doesn't watch. This guide shows how to spot each one and fix it, plus how to resize a table by hand in seconds.

How table expansion is supposed to work#

An Excel table (made with Ctrl+T, or ⌘T on a Mac) grows by itself when you type in the row directly below its last row, or the column directly to its right. Excel adds the new row to the table, copies the formatting and fills any formula columns. If you're new to tables, the guide to Excel tables and Ctrl+T explains what they do.

To see where a table ends, click any cell in it and press Ctrl+A (Mac: ⌘A). Excel selects the table's data, so you can see the last row it includes. Table Design > Resize Table also shows the exact range, such as $A$1:$C$4.

If the new rows aren't inside that range, work through the fixes below in order.

Fix 1: Remove the blank row between the table and your data#

Tables only grow into the row directly below them. If you leave one empty row and start typing in the next, Excel sees a separate list.

Here the table covers A1:C4, and rows 6 and 7 are outside it. To find gaps in a long list, count the empty cells in a column that should always be filled:

=COUNTBLANK(A2:A7)

The result is 1, so there's one blank row. Select row 5 and delete it (right-click the row number > Delete). Rows 6 and 7 move up, but they're still outside the table, so finish with Fix 4 to resize it. Next time, type in the first empty row right under the table.

The same rule applies to columns: a new column must touch the table's right edge.

Fix 2: Add rows above the total row#

When a table has a total row, the row below it isn't the next row of the table. Typing under the totals adds nothing to the table, and the totals don't include it.

To add a row correctly:

1. Click the last cell of the last data row (the row just above the totals).
2. Press Tab. Excel inserts a new row above the total row and moves you to its first cell.

You can also right-click any row in the table and choose Insert > Table Rows Above, or turn the total row off (Table Design > Total Row), add your data, and turn it back on.

Fix 3: Turn automatic expansion back on#

If tables stopped growing in every file, the AutoCorrect setting that does the expanding is probably off. It's easy to switch off by accident: when a table grows, a small lightning-bolt button appears, and one of its choices is Stop Automatically Expanding Tables.

To turn it back on in Windows:

1. Go to File > Options > Proofing.
2. Click AutoCorrect Options and open the AutoFormat As You Type tab.
3. Tick Include new rows and columns in table.
4. Also tick Fill formulas in tables to create calculated columns, so formulas fill down too.
5. Click OK twice.

On a Mac, go to Excel > Preferences (called Settings in newer versions) > AutoCorrect and tick the same options. The setting applies to every workbook you open in that copy of Excel.

Fix 4: Resize the table by hand#

When you just need the right range now, resize the table yourself. This fixes any of the cases above in a few seconds.

• Resize Table: click inside the table, go to Table Design > Resize Table (on a Mac, the tab is called Table), select the full range including the new rows (for example $A$1:$C$7) and click OK.
• Resize handle: look for the small triangle in the bottom-right corner of the table's last cell. Drag it down or right to cover the new data.

The header row must stay in the same place; you can only add or remove rows and columns at the bottom and right.

Fix 5: Unprotect the sheet#

Tables can't grow on a protected sheet, even if the protection allows inserting rows. Go to Review > Unprotect Sheet, add your rows, then protect the sheet again with Review > Protect Sheet.

If you share the file and need people to add rows, consider leaving the table's sheet unprotected and protecting only the sheets with calculations.

When formulas, charts and pivots don't pick up new rows#

Sometimes the table did expand, but something that uses it didn't update. Check what points at the table:

• Formulas with fixed ranges. =SUM(C2:C50) stops at row 50 however big the table gets. Use a structured reference such as =SUM(Expenses[Amount]) instead, which grows with the table. See why an Excel formula isn't updating for other causes.
• Formula columns that don't fill. If a new row has an empty formula cell, the column may have mixed formulas. Re-enter the formula in the whole column, as shown in how to apply a formula to an entire column.
• Pivot tables. A pivot built on the table includes new rows only after a refresh (Windows: Alt+F5). The guide to refreshing a pivot table covers the details.
• Charts. A chart made from the table grows with it. A chart made from a fixed range doesn't, so select the chart and drag its range outline to cover the new rows.

Should it be a table at all?#

Tables are the easiest way to keep a growing list tidy, but a plain range is sometimes simpler, for example in a fixed report that never gets new rows. The comparison of an Excel table vs a normal range explains which to use when.

Practise it#

Tables, filters and sorting all depend on clean, continuous data with no blank rows. Practise filtering a table in a live spreadsheet: you work on a real list and each step is checked instantly. Then try the exercise on this page to find the gap that stops a table from growing.