How to Stop Excel from Automatically Extending Table Formatting to New Rows

When you format a range of data as an official “Excel Table” (by pressing Ctrl+T or using the Format as Table button), Excel assumes you want to maintain a consistent look. Therefore, whenever you type new data into the empty row immediately below the table, Excel automatically expands the table boundaries to swallow that new row, applying all the alternating row colors, borders, and complex data validation rules to your new entry.

While this is brilliant for maintaining a growing database, it can be incredibly annoying if you are simply trying to use the row beneath the table for a random scratchpad calculation, a summary note, or a completely unrelated dataset.

If you want Excel tables to remain exactly the size they are unless you manually resize them, you need to turn off automatic table expansion.

How to Turn Off Automatic Table Expansion

This setting is controlled in the global AutoCorrect options, meaning once you turn it off, it will apply to all tables in all your future workbooks.

1. Open Microsoft Excel.
2. Click on File in the top-left corner of the ribbon.
3. Click Options at the very bottom of the left sidebar.
4. In the Excel Options window, select Proofing from the left-hand navigation pane.
5. Click the AutoCorrect Options… button near the top of the window.
6. In the new dialog box, click on the AutoFormat As You Type tab.
7. Look under the section labeled “Apply as you work.”
8. Uncheck the box labeled Include new rows and columns in table.
9. Click OK twice to close both option windows.

How to Manually Add a Row Now

With this setting disabled, typing beneath a table will just result in normal, unformatted text. If you do want to add a new row to the table in the future, you have two simple options:

• The Tab Key: Click in the very last cell of the table (the bottom right corner) and press the Tab key. This will always generate a new, properly formatted row.
• The Resize Handle: Look for the tiny blue triangle in the bottom-right corner of the table. Click and drag this triangle down to manually expand the table formatting over your new data.