Select your expense data, press Ctrl+T, tick My table has headers and give the table a name such as Expenses. Then write formulas like =SUMIFS(Expenses[Amount], Expenses[Category], "Groceries"). The formula refers to whole columns of the table by name, so it grows by itself whenever you type a new row and you never edit a range again.
Why is a table better than a plain range?
With an ordinary range, a formula such as =SUM($C$2:$C$500) stops at row 500. Row 501 gets ignored without any warning, and your totals quietly drift away from reality. A table expands when you type in the row directly beneath it, copies formulas down, extends the dropdown lists and formats alternate rows. Formulas that point at table columns follow all of that.
How do you create and name the table?
- Make sure the first row holds headers with no blanks, for example Date, Category, Amount, Merchant.
- Click any cell in the data and press Ctrl+T.
- Confirm the range and tick My table has headers.
- On the Table Design tab, change Table Name from Table1 to Expenses. Names cannot contain spaces.
If you would like a total at the bottom, tick Total Row on the same tab. It shows a SUBTOTAL of the column, which respects any filter you apply.
How do you write formulas with structured references?
Click a cell and type =SUMIFS(Expenses[ and Excel offers the column names. These formulas then become possible:
- Total for one category: =SUMIFS(Expenses[Amount], Expenses[Category], "Groceries")
- Category total from a list cell: =SUMIFS(Expenses[Amount], Expenses[Category], $A2)
- One month, with the first day of the month in B1: =SUMIFS(Expenses[Amount], Expenses[Category], $A2, Expenses[Date], ">="&$B$1, Expenses[Date], "<"&EDATE($B$1,1))
- Number of entries: =COUNTA(Expenses[Amount])
Inside the table itself, a calculated column can use the @ symbol for the current row. In a new column called Month, enter =TEXT([@Date], "yyyy-mm") and Excel fills it for every row, including ones you add later.
What happens when you add a row?
Say your Groceries formula currently returns ₹14,250 with 38 rows in the table. You buy vegetables for ₹320 and type the date, category and amount in the first empty row below the table. The table grows to 39 rows, the formula picks up the new row at once, and it now returns ₹14,570. No range edits, no dragging. The same holds for charts built on the summary cells.
| Event | Plain range to row 500 | Table named Expenses |
|---|---|---|
| You type row 39 | Included if within the range | Included |
| Your log passes row 500 | New rows ignored | Included |
| You insert a row in the middle | Range may or may not stretch | Included |
| You rename a column header | No effect | Formulas update to the new name |
What should you watch out for?
- Header names must be unique text. Changing one renames it in every formula that uses it, which is helpful, but your old formulas typed by hand elsewhere will not fix themselves.
- A table can have blank cells in the middle. SUMIFS ignores them, but a blank Category will never be counted by any category total, so give uncategorised rows a category named Unsorted.
- Structured references are an Excel feature. Google Sheets uses its own table feature and different syntax, so copy formulas across carefully.
What is the next step?
Once the log is a table, add a Category dropdown through Data, Data Validation, List so spelling stays consistent. Then build the summary on a separate sheet with SUMIFS pointing at the table columns. If you are building from nothing, that whole sequence is covered step by step in a separate guide on creating an Excel expense tracker.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
