20 min read

Excel Tables

Insert → Table. Filter buttons, banded rows, and structured references like [Amount].

Why a Table Beats a Plain Range

An Excel table gives banded rows, built-in filters, and structured references like tblExpenses[Amount] . Plain ranges break when you add row 10. A table expands automatically — new expenses join SUM(tblExpenses[Amount]) without editing the formula. James Okonkwo converts the Expenses log on NorthgateBudget.xlsx to tblExpenses so November purchases append cleanly and the PivotTable refreshes.

On Expenses, select A1:E9 (headers plus eight rows). Insert tab \to Tables group \to Table (or press Ctrl+T / Cmd+T). Tick My table has headers . Click OK . Expected result: Banded rows, filter arrows on headers, and a Table Design tab on the Ribbon.

Click any cell in the table. Table Design tab \to Properties \to Table Name . Type tblExpenses and press Enter . Named tables are easier to read in formulas than E2:E9 . Do not name the table tbl Expenses with a space — formulas need awkward quoting; some features reject spaces. Use tblExpenses — no spaces, start with a letter.

In a formula on the same sheet, you can write: =SUM(tblExpenses[Amount]) Excel uses the Amount column of the table — no need to count rows. Add a new expense row at the bottom of the table — the table expands and includes it in tblExpenses[Amount] . Example: =SUMIF(tblExpenses[Department],"Science",tblExpenses[Amount]) totals Science spend without selecting row numbers — the table name survives new rows.

Click any cell in tblExpenses . Table Design \to Table Style Options \to Total Row . Click the new bottom row under Amount — pick Sum from the dropdown. Expected result: A live total of all expenses — updates when rows are added. James Okonkwo compares this quick sum to the PivotTable total as a sanity check. The resize handle at the table's bottom-right corner drags to include blank rows — but typing in the row below the table auto-expands it anyway.

Practice

What appears automatically when you convert a range to a table?

Why a Table Beats a Plain Range

An Excel table gives banded rows, built-in filters, and structured references like tblExpenses[Amount] . Plain ranges break when you add row 10. A table expands automatically — new expenses join SUM(tblExpenses[Amount]) without editing the formula. James Okonkwo converts the Expenses log on NorthgateBudget.xlsx to tblExpenses so November purchases append cleanly and the PivotTable refreshes.

Name the Table

Click any cell in the table. Table Design tab \to Properties \to Table Name . Type tblExpenses and press Enter . Named tables are easier to read in formulas than E2:E9 . Do not name the table tbl Expenses with a space — formulas need awkward quoting; some features reject spaces. Use tblExpenses — no spaces, start with a letter.

Structured References

In a formula on the same sheet, you can write: =SUM(tblExpenses[Amount]) Excel uses the Amount column of the table — no need to count rows. Add a new expense row at the bottom of the table — the table expands and includes it in tblExpenses[Amount] . Example: =SUMIF(tblExpenses[Department],"Science",tblExpenses[Amount]) totals Science spend without selecting row numbers — the table name survives new rows.

Optional Total Row

Click any cell in tblExpenses . Table Design \to Table Style Options \to Total Row . Click the new bottom row under Amount — pick Sum from the dropdown. Expected result: A live total of all expenses — updates when rows are added. James Okonkwo compares this quick sum to the PivotTable total as a sanity check. The resize handle at the table's bottom-right corner drags to include blank rows — but typing in the row below the table auto-expands it anyway.

Practice

What appears automatically when you convert a range to a table?