Home › Excel basics › Excel tables

How to Create a Table in Excel (Ctrl+T)

To create a table in Excel, click anywhere in your data and press Ctrl+T. A table is more than a nice colour scheme: it grows automatically when you add rows, fills formulas down by itself and gives you filters and a total row in one click.

Excel CSE · 3 min read

Create a table

1. Make sure your data has one row of headers and no completely empty rows or columns.
2. Click any cell in the data.
3. Press Ctrl+T (or Insert › Table).
4. Check the range, keep My table has headers ticked and click OK.

The Table Design tab appears whenever you click inside the table. Give the table a clear name on the left of that tab, such as Sales. You'll use that name in formulas.

Why use a table?

• It grows by itself. Type in the row below and the table expands, including formatting and formulas.
• Formulas fill down. Enter a formula in one cell of a column and Excel fills the whole column.
• Charts, pivot tables and drop-down lists update. Anything based on the table picks up new rows automatically.
• Filters are built in, and headers stay visible when you scroll down.
• Banded rows make long lists easier to read, and stay correct after sorting.

Formulas with structured references

Click D2 in the table, type =, click B2, type * and click C2. Instead of =B2*C2, Excel writes:

[@Qty] means "Qty on this row". Press Enter and the whole column fills. Outside the table you refer to entire columns by name:

This returns 40 and keeps working when rows are added, without ever adjusting a range. The same goes for =AVERAGE(Sales[Price]) or =SUMIF(Sales[Product], "Pens", Sales[Amount]).

Add a total row

Tick Total Row on the Table Design tab (or press Ctrl+Shift+T). A row appears below the table; click a cell in it to choose Sum, Average, Count, Max and more. The total uses SUBTOTAL, so it only counts the rows that are visible after filtering.

Filter with slicers

On the Table Design tab, choose Insert Slicer and tick a column. You get buttons to filter with one click, which is easier for others than the filter arrows. More on filtering in sort and filter.

Change or remove the style

• Pick another look under Table Design › Table Styles.
• Turn off banded rows or the filter button with the tick boxes on the same tab.
• Want the formatting without the table? Choose the style, then Convert to Range.

Convert a table back to a normal range

Click in the table and choose Table Design › Convert to Range. The colours stay; structured references turn back into normal cell references. To remove the colours too, first choose the "None" style (top left under Table Styles).

Things tables can't do

• Dynamic array formulas such as FILTER and UNIQUE can't spill inside a table (#SPILL!). Put them next to it.
• You can't merge cells in a table.
• Headers are always text; a heading like 2026 becomes "2026".