Excel Tables: Why You Should Stop Using Plain Ranges

An Excel Table is a proper structured object you create with Insert > Table, and it automatically expands to include new rows, carries formulas and formatting down automatically, and lets you reference columns by name instead of by cell range. A plain range of data with headers looks similar on screen, but none of that happens automatically — you're managing all of it by hand, and that's usually where problems creep in.

I built a monthly sales dashboard years ago referencing a fixed range, A2:A200, across a dozen different formulas. It worked fine for months, until the business grew past row 200 and new entries started getting added below the range my formulas actually covered. Nobody noticed for a few weeks, because the dashboard didn't error out, it just quietly stopped counting the newest data. I only caught it because a monthly total looked suspiciously low compared to what I remembered from a meeting. Rebuilding that same dashboard on an actual Excel Table, instead of a fixed range, meant it never had that problem again, because the Table simply grows to include whatever's added.

How do I turn my data into an Excel Table?

1. Click anywhere inside your data
2. Go to Insert > Table (or press Ctrl+T)
3. Confirm the range Excel has detected, and tick "My table has headers" if your first row is column names
4. Click OK

Your data will now have filter arrows on the headers and alternating row shading by default, both signs it's a genuine Table object rather than a plain range.

What actually happens automatically once it's a Table?

• New rows added directly below the Table are automatically included, and any formulas or formatting in the row above copy down to the new row without you doing anything
• Charts, pivot tables, and formulas referencing the Table update their range automatically as it grows, rather than needing a manual range update
• Column headers become named references you can use in formulas instead of cell ranges
• Filter and sort arrows appear automatically on every header

What's a structured reference, and why is it better than a normal cell range?

Once your data is a Table, you can reference a whole column by its header name instead of typing out a cell range like A2:A200.

If your Table is named "SalesTable" with a "Region" column, this:

=SUM(SalesTable[Sales Amount])

replaces this:

=SUM(B2:B200)

The structured version automatically adjusts as rows are added or removed, and it's also just easier to read — anyone looking at the formula can immediately tell it's summing a "Sales Amount" column, rather than having to go check what's actually sitting in B2:B200.

Do formulas outside the Table still work if I reference it?

Yes, and this is one of the genuinely useful parts. A formula sitting outside the Table entirely can still reference a Table column using structured references, and it'll keep working correctly as rows are added, without you ever updating the range manually.

If you've read my post on "how to make a pivot table", this solves one of the more annoying issues that comes up there — a pivot table built on a Table (rather than a plain range) expands its source data automatically, so you're far less likely to need to manually adjust the data source every time new rows get added.

How do I rename a Table, and why would I bother?

By default, Excel names your first Table "Table1," second one "Table2," and so on, which isn't very useful once you're referencing it in formulas.

1. Click anywhere inside the Table
2. Go to Table Design (in the ribbon, appears only while a cell inside the Table is selected)
3. In the Table Name box on the left, type a meaningful name, like "SalesTable" or "ClientList"
4. Press Enter

A clear name makes structured references far more readable, especially in a workbook with several Tables, where "Table3" tells you nothing useful about what it actually contains.

Does this work with dynamic array functions like FILTER?

Yes, and it's a solid pairing. If you've read my post on "FILTER, SORT and UNIQUE", those functions work perfectly well referencing a Table column instead of a fixed range:

=FILTER(SalesTable, SalesTable[Region]="NSW")

As new rows get added to the Table, this formula automatically includes them in future results, without you ever needing to widen the range reference by hand.

FAQ

Does converting my data to a Table change the actual values? No — it only changes how Excel manages and references the range. Your values, formulas, and data stay exactly the same; you just gain automatic expansion, structured references, and built-in filtering.

Can I still sort and filter a Table like a normal range? Yes, and it's actually easier — every column header gets a built-in filter dropdown automatically, without you needing to apply filters manually the way you would on a plain range.

Will my existing formulas break if I convert a range to a Table? Generally no, existing formulas referencing the original cell addresses (like B2:B200) will keep working exactly as before. You only get the benefit of structured references in new formulas you write after converting, unless you go back and rewrite the old ones.

Can I convert a Table back into a plain range if I don't want it anymore? Yes — click inside the Table, go to Table Design > Convert to Range. This keeps your formatting and data but removes the Table's automatic behaviours.

Does having multiple Tables in one workbook cause any problems? Not inherently — plenty of workbooks have several Tables for different datasets. Just make sure each one has a clear, distinct name, since referencing the wrong Table by mistake is an easy error to make once you've got three or four of them sitting in the same file.