The Accidental Table Expansion
Microsoft Excel’s “Format as Table” feature is incredibly powerful, providing automatic filtering and color banding. However, it comes with a highly aggressive automation feature: if you click on the empty cell immediately to the right of your table and type a note, or click the empty cell immediately below your table and type a summary number, Excel assumes you are trying to add a new column or row to the database. It instantly expands the blue table formatting to swallow your new data, applying unwanted formulas and ruining the visual structure of your spreadsheet.
How to Disable Automatic Table Expansion
You can stop Excel from swallowing adjacent cells by disabling the “AutoFormat As You Type” rule governing tables.
1. Open Microsoft Excel.
2. Click on the File tab in the top-left corner of the ribbon.
3. Select Options at the bottom of the left-hand green sidebar.
4. In the Excel Options window, click on Proofing in the left menu.
5. Click the large button near the top labelled AutoCorrect Options…
6. A new, smaller window will appear. Click the tab at the top labelled AutoFormat As You Type.
7. Look in the “Apply as you work” section at the bottom.
8. Untick the checkbox labelled Include new rows and columns in table.
9. Click OK on both windows to save your changes.
How to Handle Exceptional Cases
Once this setting is disabled, you can safely type notes directly beneath or beside your table, and the table borders will remain exactly where they are. But what if you actually do want to add a row to the database later? Without the automatic expansion, you must manually resize the table. Click anywhere inside the table to reveal a tiny blue triangle in the absolute bottom-right corner of the table boundary. Click and drag that tiny blue triangle downward to manually expand the table to cover your new data rows.