Click any cell in your statement data, press Ctrl+T, tick My table has headers, and press OK. The range becomes an Excel Table that grows with new rows, keeps formulas consistent and gives you filter buttons. Then freeze the header row from the View tab so headings stay visible as you scroll.
Why use a Table instead of a plain range?
- Formulas fill automatically. Add a calculated column once and it covers every existing and future row.
- Charts, pivots and formulas that refer to the table extend when you paste new rows below it.
- Every column has a filter and sort button without further setup.
- Names replace cell addresses, so formulas explain themselves.
- Banded rows and a total row are one click away.
How do you create and name it?
- Make sure row 1 holds one clean heading per column, each unique and non-empty. Blank or repeated headings become Column1, Column2 and confuse formulas.
- Press Ctrl+T with a cell inside the data selected. Confirm the range shown is right and that My table has headers is ticked.
- On the Table Design tab, change the Table Name box (top left) from Table1 to something readable such as Txns.
If Excel will not create the table, look for fully blank rows in the data or merged cells in the header, which block range detection.
What do structured references look like?
Instead of $C$2:$C$500, you refer to a column by name. With a table called Txns and columns Debit, Credit and Category:
| Purpose | Formula |
|---|---|
| Total debits | =SUM(Txns[Debit]) |
| Food spend | =SUMIFS(Txns[Debit],Txns[Category],"Food") |
| Net per row (calculated column) | =[@Credit]-[@Debit] |
| Count of rows | =ROWS(Txns[Date]) |
The at-sign means this row. Type a formula in the first row of a new column and Excel fills the whole column with it. When you paste a fresh month directly under the last row, the table extends by itself and every formula that points to Txns picks the new rows up, with no range editing.
If you ever need to go back, right-click inside the table and choose Table, then Convert to Range. The data and formatting stay, but formulas using table names change to ordinary cell references. You rarely need this, but it is reassuring to know the step is reversible.
How do you freeze the header row?
Go to View, Freeze Panes, Freeze Top Row. If your header is not row 1, because you left a title line above, click the first cell below the header and choose View, Freeze Panes, Freeze Panes instead; everything above that cell stays locked in place. Note that when your active cell is inside a table and you scroll down, Excel also shows the table headings in place of the column letters, which helps, but freezing is the more reliable option for long lists.
If a wide narration column forces you to scroll sideways to reach Category, also try View, Freeze Panes, Freeze First Column, so the date stays on screen as you move right.
What should you avoid?
Do not leave dates as text, because a Table will not turn them into real dates; format them first. Avoid merging cells inside a table, and do not leave an empty row in the middle of your data. If you work from several months, paste each new month under the last row, not beside it, and keep the same column order.
Quick checklist
- One heading row with unique names.
- Ctrl+T pressed with the headers box ticked.
- Table renamed, with a short name without spaces.
- Header row frozen.
- One calculated column tested on the first row.
Later, when you want ready-made reports and per-account views without building them yourself, SpendixAI offers a library of 25 ready-made reports and a report builder for filtering and grouping.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

