Excel Table formulas

Paste-ready Table[Column] formulas.

Name the Table and columns. Pick sum visible, COUNTIFS, or a lookup. Copy structured refs or the A1 equivalent.

Paste-ready formulas

Copy into the formula bar. Structured refs use Table[Column] and [@Column].

Totals row or cell outside the Table

Table[Column]

=SUBTOTAL(109,Sales[Amount])

109 is SUM of visible cells. AutoFilter and hidden rows are skipped. Paste in the Totals row or in a cell outside the Table.

AGGREGATE alternative

Table[Column]

=AGGREGATE(9,5,Sales[Amount])

Function 9 is SUM. Option 5 ignores hidden rows. Use this when you also need error-handling options SUBTOTAL does not offer.

A1 equivalent

Same job with letter-and-number ranges. Use this when the list is not an Excel Table, or when you must freeze a snapshot.

Same job with A1 ranges

Table[Column]

=SUBTOTAL(109,C2:C500)

Assumes Amount starts at column C, rows 2:500. This range will not grow when the Table gains rows.

Generator prompt: Excel Table Sales with columns Region, Status, Amount, SKU. Write a formula that sums visible Amount values after AutoFilter using structured references (SUBTOTAL 109 on Sales[Amount]).

Structured refs vs A1

Table[Column] names the whole data column. [@Column] is this row inside a calculated column. A1 addresses such as D2:D500 stay put when someone inserts a Table row; structured refs follow the Table.

Use structured refs

Use A1

The range is an Excel Table that can grow.

The block must stay a fixed snapshot.

You are filling a calculated column with [@Column].

You need one cell, another sheet, or a non-table range.

Filters and a Totals row should follow the Table.

The file is Google Sheets, or Tables are not in use.

Sales[Amount] is easier to read than $D$2:$D$500.

You must lock a header or a range Tables cannot name.

Syntax cheatsheet

Sales[Amount]All data in the Amount column
[@Amount]This row, Amount, inside the Table
Sales[[Unit Price]]Column name with a space or punctuation
SUBTOTAL(109, Sales[Amount])Sum visible Amount after AutoFilter

Excel Table references FAQ

What is an Excel Table structured reference?

After you convert a range to a Table (Ctrl+T), Excel names the Table and each column. Formulas can then use Sales[Amount] for the whole column and [@Amount] for this row, instead of D2:D500.

When should I use Table[Column] instead of A1?

Use structured refs when the data is a Table that can grow, when you want a calculated column, or when a Totals row should follow filters. Use A1 when the range is not a Table, must stay a fixed snapshot, or the file is Google Sheets.

Does SUBTOTAL 109 ignore filtered rows?

Yes. Function 109 is SUM of visible cells. AutoFilter and hidden rows drop out. COUNTIFS does not ignore filters; use the visible-row SUMPRODUCT formula on this page when a filter is on.

Can I send this to the formula generator?

Yes. Adapt in generator opens the Excel generator with a prompt that already names your Table, columns, and job so you can ask for a variant without rewriting the layout.