Skip to content
Tutorials Center
My learning

Topics

Lesson 40 of 93 ยท Tables, Sorting and Filtering

Excel Tables

Turn a range into an Excel Table with Ctrl+T: structured references, calculated columns, auto-expansion, the total row, table styles and slicers, step by step.

A list of data on a worksheet is just cells. Excel does not know that A1:D50 is "one list with headers", so formulas, charts and PivotTables that point at it do not grow when you add a row. An Excel Table is the fix. You select your list, press Ctrl+T, and Excel treats it as a single named object with a header row, filter buttons, banded styling, formulas that fill themselves and references that expand automatically.

If you take one habit from this tutorial, make it this one: put data in Tables. Almost every other feature (sorting, filtering, PivotTables, charts, data validation lists, Power Query) works better on a Table. Excel cannot run on this site, so the TryIts use a small JavaScript table model that mimics how structured references and auto-fill behave. It is our stand-in, not Excel's engine; the formulas shown in plain blocks are what you type in Excel.

Why a Table beats a range

Grows itself New rows join the table.
Fills formulas Calculated columns copy down.
Readable names Refer to columns, not cell addresses.
Built-in tools Filters, total row, slicers.
1 / 2

Creating a Table

Click any cell in your data and press Ctrl+T (or Insert > Table; Ctrl+L also works). A dialog shows the detected range and a checkbox My table has headers. Keep it ticked. Excel applies a style, adds filter buttons to the header cells and shows a new Table Design tab on the ribbon.

Prepare the data first:

  • Exactly one header row, and every header must be unique and non-empty (Excel renames duplicates by adding a number).
  • No completely blank rows or columns inside the list, and no merged cells.
  • One kind of value per column: all dates, or all numbers, or all text.

On Table Design, give the table a good name in Table Name, such as Sales or tblOrders. A name must start with a letter or underscore, contain no spaces and must not look like a cell address (Q1 is not allowed). Short, clear names make formulas easy to read.

To go back, use Table Design > Convert to Range. The data stays, the formatting stays, but structured references turn into normal cell references.

Structured references

Inside a table, you refer to data by table and column name:

TEXT
=SUM(Sales[Amount])            the whole Amount column (without header and total row)
=AVERAGE(Sales[Price])
=Sales[@Qty] * Sales[@Price]   the Qty and Price of THIS row
=[@Qty] * [@Price]             the same, written inside the table
=COUNTA(Sales[Customer])       how many customers are listed
=Sales[[#Headers],[Amount]]    the header cell itself
=Sales[#Totals]                the total row
=Sales[[Qty]:[Price]]          several adjacent columns

The @ means "this row". When you build a formula by clicking cells inside a table, Excel writes the structured reference for you. Column names that contain special characters (such as #, a square bracket or a quote) need an extra apostrophe in the formula: Excel inserts it when you click, so let it do the typing.

Structured references have a big advantage: they do not change when rows are added or removed, and they update when you rename a column. Rename Amount to Revenue and every formula that mentions Sales[Amount] now says Sales[Revenue].

Calculated columns

Type a formula into one cell of an empty table column, say =[@Qty]*[@Price] in a new column called Amount, and press Enter. Excel fills the whole column with the same formula. That is a calculated column. New rows added later get the formula automatically.

If you change the formula in one cell, Excel offers to apply it to the entire column, and a small icon lets you undo the auto-fill. A cell whose formula differs from the rest is an exception and is marked with a small green triangle; fix it unless the difference is intentional. If auto-fill does not happen, check File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type > Fill formulas in tables to create calculated columns.

Auto-expansion

Type directly under the last row, or in the column to the right of the last header, and the table grows to include it, taking the style, formulas and data validation with it. Pasting data below the table works too. Everything that points to the table by name follows: formulas such as SUM(Sales[Amount]), PivotTables (after a refresh), charts, data validation lists and Power Query. This is what a plain range cannot do.

To resize by hand, drag the small handle in the table's bottom-right corner, or use Table Design > Resize Table.

Run it: a table model with calculated columns

The model keeps a list of rows, knows the calculated column formulas and evaluates [@Column] and Table[Column] the way the lesson describes. A few functions (SUM, AVERAGE, COUNTA, MAX) are built in. It handles only simple formulas.

JS Calculated columns and structured references

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A plain range versus a Table

The classic bug with ranges: =SUM(D2:D4) covers three rows. When someone adds row 5, the total silently leaves it out. With a Table, =SUM(Sales[Amount]) has no addresses to go stale. This stand-in shows the difference, adds a total row and a slicer filter, and renames a column.

JS Plain range vs Table, total row, slicer and column rename

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The total row (Table Design > Total Row) adds a last row with a dropdown in each cell: Sum, Average, Count, Max, Min and more. It writes SUBTOTAL(109, [Amount]), which ignores rows hidden by a filter. That is why the total changes with filters, unlike a plain SUM. The first argument 109 means "sum, ignoring hidden rows"; 101 to 111 are the matching "ignore hidden" versions of AVERAGE, COUNT and others.

Slicers on tables

A slicer is a set of buttons that filters a table. Click in the table, choose Table Design > Insert Slicer, tick the columns you want and Excel shows one slicer per column. Click a button to show only those rows (Ctrl+click for several) and use the funnel with a red cross to clear. Slicers work on tables in Excel 2013 and later, and they also work on PivotTables. A slicer is a nicer filter than the header dropdown for dashboards, because the state of the filter is always visible. The regular filter buttons are covered in Filtering Data.

Table styles and options

On Table Design, the Table Styles gallery changes colors. The Table Style Options group toggles the Header Row, Total Row, First Column, Last Column, Banded Rows and Banded Columns, and the Filter Button. Styles are cosmetic: sorting a table keeps the banding in place and does not copy colors you applied by hand.

Use Clear at the bottom of the gallery to remove the style and keep the table behavior. For consistent workbooks, choose one style and set it as the default with a right-click on it.

Things to know

  • Sorting and filtering work on the table only, so there is no risk of sorting just one column. See Sorting Data.
  • Formulas outside the table can use Sales[Amount]; formulas inside can also use the shorter [@Amount].
  • Names in charts and PivotTables. A chart built on a table extends with new rows. A PivotTable needs Refresh (Alt+F5).
  • Data validation lists can point at a table column through a named range, so a list of products grows when you add a product. See Data Validation and Dropdown Lists.
  • Limits. A table cannot contain array formulas that span rows, cannot be shared in a legacy shared workbook, and a sheet can hold many tables as long as they do not overlap. Merged cells are not allowed.
  • Cell references still work. Click a table cell from outside and you may get Sales[[#This Row],[Qty]], the long form of [@Qty].

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the keyboard shortcut to turn a selected list into an Excel Table?

    Ctrl+T (or Ctrl+L) opens the Create Table dialog. Ctrl+Shift+L toggles filter buttons instead.
  2. 2. What does =[@Qty]*[@Price] mean in a table?

    The @ sign means "this row", so each row multiplies its own Qty and Price. Typed once in a column it fills down as a calculated column.
  3. 3. Why is =SUM(Sales[Amount]) safer than =SUM(D2:D40) for a growing list?

    A structured reference refers to the table column, which grows with the table, while a fixed address stays at D2:D40.
  4. 4. The total row of a table shows a different sum after you apply a filter. Why?

    SUBTOTAL with function number 109 sums only visible rows, so it follows the filter, unlike a plain SUM.
  5. 5. Which of these is NOT a valid table name?

    Q1 looks like a cell address, so Excel does not allow it. Names must start with a letter or underscore and contain no spaces.

Summary

  • Press Ctrl+T on a clean list (one header row, unique headers, no blank rows) to make a Table, and give it a clear name.
  • Structured references such as Sales[Amount] and [@Qty] use names, follow the table as it grows and update on renames.
  • A calculated column fills one formula down the column, including future rows.
  • Tables expand when you type below them; charts, formulas and validation lists tied to the table follow.
  • The total row uses SUBTOTAL(109, ...), and slicers filter tables with visible buttons.

Next: Sorting Data.