How to Use Tables in Excel

Introduction

You add a row to the bottom of your data and the SUMIFS formula ignores it because the range stops at row 15. You drag the range down, but next week there are more rows and the same thing happens again. Excel Tables turn a static range into a structured object that grows with your data. Formulas, charts, and pivot tables that reference the table expand automatically, so you stop fixing range boundaries every time something changes.

The Dataset

Here's a product inventory for a small warehouse:

	A	B	C	D
3	Product	Category	Qty	Unit Price
4	Desk Lamp	Lighting	45	28
5	Monitor Stand	Furniture	30	55
6	USB Hub	Electronics	120	18
7	Cable Tray	Accessories	80	12
8	Desk Pad	Accessories	65	22

Five products across four categories. The warehouse adds new products regularly, and every formula that references this data needs to keep up.

Creating a Table

Select any cell inside the data and press Ctrl + T. Excel highlights the range it detected and asks if your table has headers. Check the box and click OK.

The range gets alternating row colors and filter dropdowns appear on every header. More importantly, Excel now treats this range as a single object rather than a loose collection of cells.

You can rename the table in the Table Design tab. Click the Table Name box on the left side of the ribbon and type something descriptive like Inventory. This name is what your formulas will reference going forward.

Structured References

Once data lives in a table, formulas can reference it by column name instead of cell address. These are called structured references.

Without a table:

=SUM(C4:C8)

With a table named Inventory:

=SUM(Inventory[Qty])

Both return 340. The table version reads like a sentence: sum the Qty column from the Inventory table.

Here are the structured reference patterns you'll use most:

Reference	Meaning
Inventory[Qty]	The entire Qty column
[@Qty]	The current row's Qty value (used inside the table)
Inventory[#Totals]	The total row (used less often)
Inventory[#Headers]	The header row (used less often)

The @ symbol means "this row." Inside the table, =[@Qty]*[@Unit Price] multiplies the quantity and unit price for whatever row the formula sits in. Type it once and the table fills it down for every row automatically.

Key takeaway: Structured references replace cell addresses with column names. They make formulas easier to read, and they never break when you add or rearrange rows.

Auto-Expanding Ranges

This is the main reason to use tables. Type data in the row directly below the last table row and Excel extends the table to include it.

Add a new product in row 9:

	A	B	C	D
9	Webcam	Electronics	50	40

The table border expands, the alternating colors extend, and =SUM(Inventory[Qty]) updates from 340 to 390 without anyone touching the formula. Any chart, pivot table, or conditional formatting rule that points to the table picks up the new row the same way.

This is the difference between a table and a named range. A named range defined as C4:C8 stays fixed at those five cells until you update it manually. A table handles the bookkeeping for you.

Sorting and Filtering

Every table column gets a filter dropdown by default. Click the arrow on the Category header and you can:

• Sort A to Z or Z to A: Reorders the entire table by that column.
• Filter by value: Uncheck categories you don't need. The rows disappear temporarily.
• Search: Type in the search box to narrow the dropdown list when you have dozens of values.

Filtered rows are hidden, not deleted. The data is still there. Clear the filter and everything comes back.

For multi-level sorting, go to Data > Sort and add levels. Sort by Category first, then by Qty within each category.

The Total Row

Toggle the total row from the Table Design tab by checking Total Row, or right-click anywhere in the table and choose Table > Totals Row. A new row appears at the bottom with a sum of the last numeric column.

Click the dropdown in any total row cell to change the function:

Function	What It Shows
Sum	Total of the column
Average	Mean value
Count	Number of entries
Min / Max	Smallest or largest value
None	Clears the cell

The total row uses the SUBTOTAL function internally, which means it respects active filters. Filter the table to show only Electronics and the total recalculates for Electronics alone:

Product	Category	Qty	Unit Price
USB Hub	Electronics	120	18
Webcam	Electronics	50	40
Total		170

Change the filter to Accessories and the total updates to 145 (80 + 65). No separate formulas needed.

Calculated Columns

Type a formula in a new column inside the table and Excel fills it down to every row automatically. Add a header called "Value" in column E and enter this formula in E4:

=[@Qty]*[@Unit Price]

The formula appears in every row (including the Webcam row added earlier), each one referencing its own values:

Product	Category	Qty	Unit Price	Value
Desk Lamp	Lighting	45	28	1260
Monitor Stand	Furniture	30	55	1650
USB Hub	Electronics	120	18	2160
Cable Tray	Accessories	80	12	960
Desk Pad	Accessories	65	22	1430
Webcam	Electronics	50	40	2000

No copying, no dragging. Add a new row next week and the calculated column fills itself in for that row too.

Key takeaway: Calculated columns auto-fill and auto-expand. One formula covers the entire column, including rows that don't exist yet.

Tables as a Pivot Table Source

Pivot tables built from a table source never go stale. Go to Insert > PivotTable and the source range automatically points to the table name instead of a fixed cell range. When the table grows, the pivot table picks up the new rows on the next refresh.

This solves a common frustration: you build a pivot table from A3:E8, add data in rows 9 and 10, and the pivot table ignores them because the source range is still hardcoded to row 8. With a table as the source, the range expands itself.

When Tables Don't Fit

Tables work well for flat, row-based data. A few situations where they cause more problems than they solve:

• Multi-section layouts. If one sheet holds several separate data blocks with spacing between them, a table can't span all of it. Each block would need its own table.
• Merged cells. Tables don't support merged cells. If your layout depends on merged headers or grouped rows, converting to a table breaks that structure.
• Shared workbooks. Older shared workbook mode (not co-authoring) doesn't support tables. If your team still uses this legacy feature, you'll need to switch to co-authoring before converting ranges to tables.

If you convert data to a table and decide it's not the right fit, you can reverse it. Click inside the table, go to Table Design > Convert to Range, and Excel strips the table structure while leaving your data and formatting intact.

For single flat datasets that grow over time, tables are almost always worth using.

Conclusion

Tables turn a static range into a self-maintaining data structure. Formulas reference column names instead of cell addresses, new rows get included automatically, and the total row adjusts to whatever filter is active.

For labeling ranges without converting to a full table, see our guide on named ranges. For summarizing table data with drag-and-drop layouts, check out our guide on pivot tables. For rules that highlight values based on conditions, see our guide on conditional formatting.