Excel Tables

Excel Guides / Dashboard Building

Dashboard Building

Turn a plain range into a table and your formulas, charts and PivotTables grow with the data.

Updated 12 Oct 2026 · 5 min read · By Sky Dashboards

An Excel Table is a range that knows it is a list. It has a name, it knows where its headers and last row are, and it grows when you add data below it. That one change fixes most problems with growing logs: charts that stop at last month, SUMIFS ranges that miss new rows and formulas someone forgot to fill down.

Create a table

1. Click any cell in your data. It needs a single header row and no completely blank rows or columns.
2. Press Ctrl + T, or go to Insert > Table.
3. Check the range Excel has picked and that My table has headers is ticked.
4. Click OK.

Excel adds banded rows, filter buttons and a Table Design tab that appears whenever a cell in the table is selected. Keep headers short and unique, because they become the names you use in formulas.

Name the table first

New tables are called Table1, Table2 and so on. Rename yours before writing formulas against it, because the name appears in every reference.

1. Click inside the table.
2. On Table Design, type a new name in the Table Name box at the far left.
3. Press Enter.

• Names must start with a letter, an underscore or a backslash, and cannot contain spaces.
• They must be unique in the workbook and must not look like a cell address, so FY26 is out.
• A prefix such as tbl keeps them together in formula autocomplete: tblSales, tblClients, tblProducts.

Structured references

Formulas that point at a table use its name and column headers instead of cell addresses. Excel writes them for you when you click table cells while building a formula.

Type a formula into one cell of an empty column and it becomes a calculated column: Excel fills it to the bottom, and fills every new row too.

From outside the table, references read almost like plain English:

Ranges that grow on their own

Type in the row directly under a table and the table extends to include it. Paste ten rows there and all ten join. Everything built on the table follows:

• Formulas that use tblBookings[Amount] include the new rows straight away.
• Calculated columns, formatting and drop-down lists carry down to each new row.
• Charts built from the table extend their series automatically.
• PivotTables using the table as their source pick up new rows when refreshed: Alt + F5 for the selected PivotTable, Ctrl + Alt + F5 for everything.

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

The total row

Tick Total Row on the Table Design tab, or press Ctrl + Shift + T. A row appears under the data with a total in the last column. Click any cell in that row for a drop-down of Sum, Average, Count, Max, Min and more.

The totals are SUBTOTAL formulas, such as =SUBTOTAL(109,[Amount]). The 109 means sum while ignoring hidden rows, so when you filter the table the total shows only what is visible.

Tables behind charts, PivotTables and slicers

Every Sky Dashboards template keeps its raw data in tables, because a table is the simplest dynamic range Excel has: no OFFSET formulas and no named ranges to keep up to date.

• Charts: select a cell in the table and insert a chart. New rows appear in the chart as they are added.
• PivotTables: with a cell in the table selected, choose Insert > PivotTable. The source is stored as the table name rather than a cell range, so it never needs editing. More in PivotTables.
• Slicers: Table Design > Insert Slicer adds clickable filter buttons for any column. They filter the table itself, which makes a lightweight dashboard possible without a PivotTable.

What tables cannot do

• Spill formulas. Dynamic array formulas such as UNIQUE or FILTER return #SPILL! inside a table. Put them in a normal range beside it.
• Merged cells. Not allowed anywhere inside a table.
• Multi-row headers. A table has exactly one header row. Put a title above the table instead.
• Custom Views. The command is unavailable in any workbook that contains a table.

Convert a table back to a range

Click inside the table and choose Table Design > Convert to Range, then confirm. The data and formatting stay, the filter buttons go, and structured references in formulas are rewritten as ordinary cell references.

Frequently asked questions

What is the difference between a table and a formatted range?

The formatting is the least of it. A table has a name, grows when you add rows, fills formulas down automatically and can be referred to by column name. A range with the same colours does none of that.

How do I add a row to an Excel table?

Type in the first empty row directly under the table, or press Tab in the last cell of the last row. Both extend the table.

How do I stop Excel writing structured references?

Untick File > Options > Formulas > Use table names in formulas. Excel then writes ordinary cell references when you click table cells.

Can I use a table column as a drop-down source?

Not by typing the structured reference into Data Validation directly. Point a named range at the column, or use INDIRECT with the reference as text. The drop-down guide covers both.

Related guides

All 32 Excel guides