Excel Tables (Ctrl+T): Why and How to Use Them

An Excel table turns a plain list into something Excel understands as one block of data. Press Ctrl+T (⌘T on a Mac) and your list gets filter buttons, banded rows, a total row and formulas that grow as you add rows. If you keep any list that changes, like orders, expenses or staff, a table saves you from fixing ranges by hand.

What is an Excel table?#

An Excel table is a named range with built-in features. It is not just formatting: Excel tracks where the table starts and ends, which row holds the headers and which formula belongs to each column.

That matters because ordinary ranges are fixed. If =SUM(D2:D7) adds your orders and you type a new order in row 8, the formula ignores it. Inside a table, the new row becomes part of the table, and anything that points at the table includes it.

For a deeper look at the trade-offs, see Excel table vs normal range.

How to create a table with Ctrl+T#

Creating a table takes three clicks or one shortcut. Your data needs a single header row and no completely blank rows or columns in the middle.

1. Click any cell inside your list.
2. Press Ctrl+T on Windows or ⌘T on a Mac. (On Windows, Ctrl+L also works.) You can also go to Insert > Table.
3. Check that the range in the Create Table box is right.
4. Make sure My table has headers is ticked, then click OK.

Excel applies a style with banded rows, adds a filter button to every header and opens the Table Design tab (called Table on a Mac). If your list had no header row, leave the box unticked and Excel adds headers named Column1, Column2 and so on, which you can then rename.

Give the table a name#

Excel calls your first table Table1. A clear name makes formulas easier to read. On the Table Design tab, click in the Table Name box on the left, type a name such as Sales (no spaces) and press Enter.

Why use an Excel table? Six things you get for free#

Tables remove most of the routine upkeep that lists need. Here is what changes as soon as you press Ctrl+T:

1. Automatic expansion. Type in the row directly below the table and it grows to include the new row. Formatting and formulas come with it.
2. Calculated columns. Type a formula in one cell of a new column and Excel fills it down the entire column. Add a row later and the formula is already there.
3. Filter and sort buttons on every header, ready to use. Our lesson on filtering a table walks through them step by step.
4. Headers that stay visible. When you scroll down inside a long table, the column letters at the top of the sheet change to show your header names.
5. A one-click total row (more on this below).
6. Reliable sources for charts, drop-down lists and pivot tables, because the table name always points at all of the data.

Structured references: formulas that use column names#

Inside a table, Excel writes formulas with column names instead of cell addresses. These are called structured references.

Say your Sales table has Units and Price columns. In a new column, type = and click the Units cell, type *, then click the Price cell. Excel writes:

=[@Units]*[@Price]

[@Units] means "the Units value in this row". Press Enter and the formula fills the whole column.

From outside the table, you refer to a whole column with the table name:

=SUM(Sales[Amount])

This total includes every row in the table, today and after you add more. You don't have to type these names: click or select the cells and Excel writes the reference for you. If you prefer normal references such as D2, type the address instead of clicking.

How to add a total row to a table#

The total row is one checkbox. Click inside the table, then on the Table Design tab tick Total Row. A new row appears at the bottom.

Click any cell in the total row and a drop-down arrow lets you choose Sum, Average, Count, Max, Min and more. Behind the scenes Excel writes a SUBTOTAL formula, such as:

=SUBTOTAL(109,[Amount])

The 109 means "sum, but skip hidden rows". So if you filter the table to show only the North region, the total shows only North's orders. A plain SUM would still add everything. The exercise on this page lets you write that formula yourself.

Tables and pivot tables#

Tables are the best source for a pivot table. When you build a pivot from a table, the pivot's source is the table name rather than a fixed range like A1:D7.

Add ten new orders to the table, click inside the pivot and choose Refresh (on the PivotTable Analyze tab), and the new orders appear. With a fixed range you would have to change the source by hand. Our beginner's guide to pivot tables covers building one, and the pivot tables lesson lets you practise in a live sheet.

The same idea helps with other features. A chart built on a table extends when the table grows, and so does a drop-down list that uses a table column as its source.

Limits of Excel tables and how to undo one#

Tables have a few rules that can surprise you:

• No merged cells inside a table.
• Dynamic array formulas such as SORT, FILTER and UNIQUE return #SPILL! inside a table. Put them in normal cells next to the table.
• Each header must be unique. If two columns are both called "Date", Excel renames one "Date2".
• Sorting is per table. Sorting data inside a table sorts whole rows of the table only, which is usually what you want.

To go back to a normal range, click inside the table and choose Table Design > Convert to Range, then confirm. The data and colors stay, but the filters, automatic expansion and structured references are gone. Formulas that used structured references are rewritten with normal cell addresses.

Excel tables in job tests and real work#

Tables come up often in office work because they keep shared lists tidy. Admin roles use them for trackers and registers, as covered in Excel skills for admin and office jobs. Finance teams use them as clean sources for pivots and reports, one of the Excel skills for finance and accounting jobs.

In a test, pressing Ctrl+T before you filter, sort or build a pivot is a quick way to show you know how to keep data organised.

Practise it#

Start with the exercise on this page: write the SUBTOTAL formula that a table's total row uses. Then practise filtering a table in a live spreadsheet, and move on to building your first pivot table from a list of orders.