What is an Excel table? Definition, features, and table vs range

Microsoft's framing is that you "turn a range of cells into an Excel table" to make managing and analysing a group of related data easier. The word "table" is overloaded, so it is worth ruling out the other two: this is not a PivotTable, and not a what-if data table. It is the object you get from Insert → Table or Ctrl+T — the one Excel names Table1 and draws with a coloured header.

What converting a range actually does

Ten seconds of work changes several behaviours at once. Exceljet's summary of the shortcut is the practical version: "put the cursor anywhere in the data and use the keyboard shortcut Control + T."

The range stops being fixed. "When new rows or columns are added to an Excel Table, the table expands to enclose them." That single property is what makes tables the answer to a stale named range: every formula, chart and PivotTable pointing at the table follows the data instead of being redefined by hand.

Columns get names, and formulas use them. Microsoft: "Instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula." =SUM(Orders[Amount]) says what it totals; =SUM(D2:D5000) says only where to look. Structured references are a topic of their own, but they are the reason most people convert in the first place.

One formula per column. "By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column." No fill-down, and — more usefully on a shared file — no chance of row 412 quietly holding a different formula from every other row.

Filtering, banding and a totals row come for free. "By default, a table has a header row. Every table column has filtering enabled in the header row." Banded rows are cosmetic — "Alternate shading or banding in rows helps to better distinguish the data" — but the total row is not: it inserts a SUBTOTAL rather than a SUM, so the number respects the filter you just applied.

Ablebits puts the whole case in one sentence: converting data to a table "can spare you the headache of creating dynamic named ranges, updating formula references, copying formulas across columns, formatting, filtering and sorting your data."

Table vs range

The right default for a rectangle of records with headers is a table. The exceptions are real, and Ablebits lists the ones that catch people out: "It is not possible to insert a table in a shared file because the table functionality is not supported in shared workbooks", and features such as custom views and manual subtotals are unavailable inside one. A table is also the wrong shape for a report layout with merged cells, spacer rows or several blocks stacked in the same columns — it wants one header row and one record per row.

Getting out is non-destructive: "If you want to remove a table without losing the table data or table formatting, go to the Design tab > Tools group, and click Convert to Range." Structured references turn back into ordinary cell references at that moment, which is worth knowing before doing it on a model.

Tables and dynamic arrays

One boundary surprises people who adopt both features. Microsoft is direct: "Spilled array formulas are not supported in Excel tables themselves, so you should place them in the grid outside of the Table." A table column already has its own rule about what every cell contains, which is incompatible with one formula filling a variable number of them. Tables can feed a dynamic array formula happily — it is putting the spilling formula inside one that fails.

The Google Sheets side

Sheets has tables now, and they are a different object with an overlapping purpose. Ben Collins introduces them as "a new feature in Google Sheets" that "make it quick and easy to apply formatting and structural rules to a plain range of data", created from Format → Convert to table rather than Insert → Table. The emphasis differs: a Sheets table leans on column data types — you "set the datatype for the column (i.e. is it text? A number?)" — which gives you validation Excel tables do not provide.

References work, with their own spelling. Collins documents Properties[#All] for the entire table including headers, bare Properties for the data without headers, and Properties[Column Name] for one column. Close enough to read; not close enough to assume a formula ported from Excel will land unchanged.

How Formula Foundry handles this

Set expectations first: Formula Foundry does not create, convert or manage tables. Converting a range is a spreadsheet operation and stays one — Ctrl+T in Excel, Format → Convert to table in Sheets. The add-on is a formula editor, and what it does is make the formulas that reference a table readable.

That matters more than it sounds, because structured references are long. =SUMIFS(Orders[Amount], Orders[Region], "EMEA", Orders[Date], ">="&@@CutOff) is a single line in the formula bar and four separate ideas. In the Formula Foundry sidebar it opens across multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour and matching parentheses highlighted — so a bracket closed one column early is visible rather than deduced. Auto-suggest covers range names as well as function names as you type.

Two other pieces fit the table workflow. The visual builder renders a nested formula's call tree as a form with every argument named — useful when a SUMIFS over a table has grown to six arguments and you need to see which pair is misaligned. And global @@variables handle the part a table cannot: the cut-off date or rate typed into the formula rather than stored in a column, defined once and updated everywhere at once. The features overview shows the editor, the variables panel and the snippet library in both applications.

FAQ