A spreadsheet expense tracker is a sheet with one row per transaction and five columns (Date, Description, Amount, Category, Account), plus a summary tab that totals spending by category and month with SUMIFS. Build it once, paste or import your bank's CSV each month, assign categories, and the summary updates itself. It works the same in Google Sheets and Excel.
The part most templates skip is what happens when real bank data arrives: merchant strings like AMAZON.COM*Z12AB, card payments that look like income, and dates in the wrong order. This guide builds the tracker around those problems, with a worked example on an actual card export layout.
The 5-column layout
Create a tab called Transactions with these headers in row 1:
| Column | Header | What goes in it | Example |
|---|
| A | Date | A real date value, not text | 06/15/2025 |
| B | Description | The bank's merchant string, unedited | AMAZON.COM*Z12AB |
| C | Amount | Signed number: spending negative, money in positive | -123.45 |
| D | Category | One name from your category list | Shopping |
| E | Account | Which export the row came from | BofA card |
Why these five and nothing else:
- Keep the raw description. You categorize from it, and you will want to search it later. Clean names belong in a lookup, not in place of the original.
- Keep the bank's sign. Most exports already write spending as negative. Flipping signs by hand is where errors creep in.
- Add Account from day one. The moment you add a second bank or a card, you need to know where each row came from, and to spot transfers between them.
Turn the range into a table (Format → Convert to table in Google Sheets, Insert → Table in Excel) so formulas extend automatically as rows grow.
A category list that holds up
Too many categories and you stop categorizing; too few and the summary tells you nothing. Here is the 14-category default set that Expense Sorted's local categorizer starts from before it has learned from your own labels:
| Category | Typical transactions |
|---|
| Groceries | Supermarkets |
| Shopping | Retail, clothing, electronics |
| DinnerBars | Restaurants, cafes, takeaway, delivery |
| Medical | Pharmacy, GP, dental, physio |
| Transport | Fuel, rideshare, public transport, parking |
| Utility | Electricity, broadband, mobile, water |
| Travel | Flights, hotels, rental cars |
| Kids | Childcare, school costs, toys |
| Business | Office supplies, work software |
| Living | Streaming, music, gym and other subscriptions |
| Tax | Income tax, GST, tax agent fees |
| Transfer | Money between your own accounts |
| Income | Salary, wages, interest |
| Debt Repayment | Mortgage, card debt, student loan |
Put this list on a tab called Categories, then add a dropdown to column D: Data → Data validation in Google Sheets or Data → Data Validation → List in Excel, pointing at the list. A dropdown stops typos like "Grocery" and "Groceries" splitting one category into two.
Two categories matter more than they look. Transfer keeps card payments and moves between accounts out of your spending. Income keeps money in separate from refunds you might otherwise net against spending.
Worked example: a 6-row card export
Here is the layout of a Bank of America credit card CSV export, with scrubbed sample rows:
Posted Date,Reference Number,Payee / Description,Amount
06/15/2025,1234567890,AMAZON.COM*Z12AB,-123.45
06/16/2025,1234567891,UBER *TRIP HELP.UBER.COM,-24.50
06/17/2025,1234567892,WHOLEFDS PRV 10123,-87.34
06/18/2025,1234567893,ONLINE PAYMENT - THANK YOU,500.00
06/19/2025,1234567894,NETFLIX.COM,-15.99
06/20/2025,1234567895,AUTOMATIC PAYMENT - THANK YOU,250.00
Mapped into the five columns, with categories assigned:
| Date | Description | Amount | Category | Account |
|---|
| 06/15/2025 | AMAZON.COM*Z12AB | -123.45 | Shopping | BofA card |
| 06/16/2025 | UBER *TRIP HELP.UBER.COM | -24.50 | Transport | BofA card |
| 06/17/2025 | WHOLEFDS PRV 10123 | -87.34 | Groceries | BofA card |
| 06/18/2025 | ONLINE PAYMENT - THANK YOU | 500.00 | Transfer | BofA card |
| 06/19/2025 | NETFLIX.COM | -15.99 | Living | BofA card |
| 06/20/2025 | AUTOMATIC PAYMENT - THANK YOU | 250.00 | Transfer | BofA card |
What the mapping had to deal with:
- Dropped a column. Reference Number is useful for spotting duplicates but not for the tracker, so it is left out (or kept in a hidden column F).
- Date order. The file uses mm/dd/yyyy. If your spreadsheet locale is day-first,
06/15/2025 imports as text, because there is no 15th month. Set the locale to match the file before importing (File → Settings → Locale in Google Sheets).
- Two positive rows that are not income. The 500.00 and 250.00 rows are payments to the card from a bank account. Categorized as Transfer, they stay out of both spending and income.
The result: spending for the period is 123.45 + 24.50 + 87.34 + 15.99 = 251.28, and the 750.00 of payments is correctly ignored. Had those two rows been left uncategorized or tagged Income, the month would show 750.00 of income that never happened.
The summary formulas
On a Summary tab, put your categories down column A and months across row 1 (as the first day of each month, e.g. 6/1/2025). Then in B2:
=-SUMIFS(Transactions!$C:$C,
Transactions!$D:$D, $A2,
Transactions!$A:$A, ">="&B$1,
Transactions!$A:$A, "<"&EDATE(B$1,1))
This sums one category for one month, and the leading minus turns spending into a positive number for reading. Fill it across and down. For the worked example, the June column reads Shopping 123.45, Transport 24.50, Groceries 87.34 and Living 15.99.
For a total that excludes transfers and income:
=-SUMIFS(Transactions!$C:$C,
Transactions!$C:$C, "<0",
Transactions!$D:$D, "<>Transfer",
Transactions!$A:$A, ">="&B$1,
Transactions!$A:$A, "<"&EDATE(B$1,1))
Add one helper check: count rows with an empty Category.
=COUNTIFS(Transactions!$A:$A, "<>", Transactions!$D:$D, "")
If that number is not 0, your totals are incomplete.
Speeding up categorization
Typing a category for every row is the step that makes people quit. Three ways to cut it down, from simplest to most automatic:
- Keyword lookup. A Rules tab with two columns, a text fragment (
UBER, NETFLIX) and a category, plus a formula that finds the first fragment contained in the description. It handles repeat merchants well and new ones not at all. See how to auto-categorize bank transactions in Excel for the formulas.
- Sort and fill. Sort by Description, then fill a category down a block of identical merchants in one move.
- Learned suggestions. Tools that suggest a category from your past labels, covered in AI transaction categorization: AI vs formulas vs manual.
Spreadsheet vs dedicated tracker
A spreadsheet is the right tool for many people. It stops being one at a predictable point:
| Situation | Spreadsheet | Dedicated importer |
|---|
| One bank, under ~100 rows a month | Fine | Overkill |
| Several banks with different CSV layouts | Manual remapping each month | Layouts mapped once |
| New merchants every month | Manual categorizing | Suggestions from past labels |
| Duplicate rows from overlapping exports | Hand-checked | Detected on import |
| Sharing with a partner | Easy in Google Sheets | Depends on the tool |
| Data stays on your device | Excel file: yes. Google Sheets: stored with Google | Depends on the tool |
If privacy is the reason you chose a spreadsheet, note that a Google Sheet lives on Google's servers. An Excel file or a local database stays on your machine. Local expense tracker vs Google Sheets template compares the two directly, and private expense tracker: 5 tests for who sees your data gives you a checklist for any tool.
Common mistakes
- Dates imported as text. They sort wrong and
SUMIFS ignores them. Check with =ISNUMBER(A2); it should return TRUE.
- Mixing sign conventions. Some banks export spending as positive with a separate debit/credit column. Convert to one convention on import, every time.
- Counting card payments as spending. Use the Transfer category for them, as in the worked example.
- Editing the description. Cleaning
WHOLEFDS PRV 10123 to "Whole Foods" feels tidy, but it breaks keyword rules and searches. Add a clean-name column instead if you want one.
- One giant category. If "Shopping" is 40% of your spending, split it. A category that holds everything tells you nothing.
FAQ
What columns does a spreadsheet expense tracker need?
Five is enough: Date, Description, Amount, Category and Account. Date and Amount drive every total, Description is what you categorize from, Category feeds the summary, and Account lets you combine several exports in one sheet.
Should expenses be negative or positive in the spreadsheet?
Pick one convention and convert every import to it. Keeping the bank's sign (spending negative, money in positive) is the least work, because most CSV exports already use it.
Are credit card payments an expense in my tracker?
No. A payment to your card is money moving between your own accounts. Give it a Transfer category and leave it out of spending totals, or the same purchase is counted twice.
Is Google Sheets or Excel better for an expense tracker?
Both handle the formulas here. Google Sheets is easier to share and works in a browser; Excel works offline and keeps the file on your computer.
When should I stop using a spreadsheet to track expenses?
When categorizing takes longer than reviewing, when you import from several banks with different layouts, or when formulas start breaking as rows grow.
Related guides
