Converting your data to an Excel Table is one of the highest-ROI actions you can take in Excel. It takes 2 seconds and immediately gives you auto-expansion, structured formula references, built-in filters, and better compatibility with pivot tables and Power Query.
Create a Table
- Click any cell inside your data range
- Press Ctrl+T (or Insert → Table)
- Confirm the range in the dialog — check "My table has headers" if your top row has column names
- Click OK
Why Use Tables Instead of Plain Ranges?
Auto-Expand
Type anything in the row immediately below the table or the column immediately to the right — the table automatically extends to include it. Formulas in calculated columns copy down. Named ranges and pivot table data sources update automatically.
Built-in Filter and Sort
Every column header gets a dropdown arrow. Click to sort ascending/descending or filter to specific values. No need to select the range and apply Data → Filter separately. The filters are persistent — navigating away and back doesn't clear them.
Consistent Formulas
Type a formula in any cell of a blank column and press Enter — Excel fills the entire column with the same formula automatically. No copying and pasting needed.
Structured References
Structured references are table-aware formula syntax. Instead of =SUM(D2:D500), you write =SUM(SalesData[Revenue]). This reference always points to the Revenue column of SalesData, regardless of how many rows the table has.
Reference syntax:- SalesData[Revenue] — the Revenue column (excludes header)
- SalesData[[#Headers],[Revenue]] — just the header cell
- SalesData[[#Totals],[Revenue]] — the totals row for Revenue
- SalesData[#All] — the entire table including headers and totals
- [@Revenue] — within the table, refers to the Revenue value in the current row (like a relative row reference)
This formula automatically uses the values from the same row, making it equivalent to =D2-E2 but much more readable.
Total Row
Table Design → Total Row adds a summary row at the bottom of the table.Each cell in the Total Row has a dropdown to choose: Sum, Average, Count, Max, Min, StdDev, or none.
The formulas use SUBTOTAL() under the hood, which means totals automatically reflect any active filters — only visible rows are counted.
Slicers on Tables
Slicers are visual filter buttons. You can add them to plain tables (not just pivot tables).
Table Design → Insert Slicer → select the column(s) to filter by → click OKEach slicer is a floating panel of buttons. Click a button to filter the table to that value. Hold Ctrl to select multiple values. Click the X button on the slicer to clear the filter.
Slicers are especially useful in shared workbooks or dashboards where non-Excel-savvy users need to filter data without touching the column dropdowns.
Convert Back to a Range
Sometimes you need a plain range — for certain advanced formula techniques, or before deleting rows programmatically.
- Click inside the table
- Table Design → Convert to Range
- Confirm
Frequently Asked Questions
Can I have a table on the same sheet as a pivot table?
Yes. In fact, using an Excel Table as the source for a pivot table is the recommended approach. The pivot table automatically includes new rows when you refresh, because the table expands and the pivot table's data source expands with it.
Why do my table formulas show @-sign warnings?
When you reference a structured reference like SalesData[Revenue] in a cell outside the table, Excel may show an implicit intersection warning with @. This is Excel 365 behavior — in older versions this was implicit. Add @ explicitly (SalesData[@Revenue]) or use SUM(SalesData[Revenue]) for column-wide calculations to resolve the warning cleanly.
Can I merge cells inside a table?
No. Excel does not allow merged cells inside a Table. If you need to merge for visual presentation, convert the table back to a range first. Generally, merged cells and structured data don't mix — keep tables for data and use regular cells for report layouts.
How do I remove the alternating row colors but keep the table functionality?
Click inside the table → Table Design tab → in the Table Styles gallery, choose the style called None (first option in the Light section). This removes formatting while keeping all table features. Alternatively, check/uncheck Banded Rows in the Table Style Options group.