How to Keep Track of Expenses in Excel

To keep track of expenses in Excel, create a table with one row per purchase, record the date, category, and amount consistently, then use formulas to total spending and summarize it by month or category. Start with a blank workbook and a few recent receipts or bank transactions so you can build and check the tracker before relying on it.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. The steps also work in recent desktop versions of Excel; a few menu or shortcut differences are noted for Mac.
A blank workbook and a way to check transaction details, such as receipts, bank statements, or card statements.
A short list of spending categories that fit your needs, such as Groceries, Housing, Transport, Utilities, and Dining.

Step-by-Step

Set up the transaction columns. In a new worksheet, enter these headings in row 1: Date, Description, Category, Payment Method, Amount, and Notes. Keep each field in its own column. For example, put Date in A1, Description in B1, Category in C1, Payment Method in D1, Amount in E1, and Notes in F1. A separate row for every purchase makes it possible to sort, filter, and summarize the data later. Do not combine multiple purchases in one row, even if they happened at the same store.
Make the range an Excel Table. Select the headings and a few blank rows beneath them, then choose Insert > Table. Check My table has headers and select OK. On Windows, Ctrl+T is a shortcut for creating a table from a selected range; on Mac, use Command+T. A Table gives the data filter buttons and normally extends formatting and formulas when you add new rows. To give it a clear name, click in the Table, open Table Design, and enter Expenses in the Table Name box. If you do not see Table Design, select a cell inside the Table.
Choose a consistent way to enter amounts. Enter expenses as positive numbers, such as 12.50, and use the same currency throughout the sheet. Format the Amount column as currency or accounting: select the column, then use Home > Number and choose Currency or Accounting. Apply a suitable date format to the Date column from the same Number area. Formatting changes how values look, not the underlying numbers; enter dates as real Excel dates rather than text such as “last Friday.” If you need to track refunds, record them consistently as negative amounts or in a separate Refunds column, and make that convention clear in Notes.
Add a small, controlled category list. Write your approved categories in an unused area of the sheet or on a separate worksheet named Lists. Use broad labels that you can apply repeatedly; an overly long list makes logging slower and summaries harder to read. To reduce spelling variations, select the cells under Category and choose Data > Data Validation. Set Allow to List, then select the category cells for Source. If the list is on another sheet, you may need to define a named range first or keep the list on the same sheet, depending on your Excel setup. Select OK. On Mac, the Data Validation command is also on the Data tab, though the dialog can look different.
Enter transactions as they happen. Add one transaction per row, using the purchase date, a recognizable description, one category, the payment method, and the full amount paid. For example, a grocery purchase could be dated March 3, described as “Market,” categorized as Groceries, paid by Debit, and entered as 48.20. Add a note only when it helps explain an unusual transaction. Record the purchase date rather than the statement date unless you have chosen to track statement dates consistently.
Calculate total spending. Put a label such as Total expenses in an empty cell outside the Table, for example D2, and enter the total formula in E2. With amounts in E2:E1000 and the header in E1, use =SUM(E2:E1000). If the example entries are 12.50, 48.20, and 6.30, the result is 67.00. The formula ignores blank cells, but remember to increase the range if you expect to enter more than 999 transactions. Keep summary formulas outside the transaction Table so they do not become mixed in with individual purchases.
Make a monthly total. Enter the first day of the month you want to review in G2 as a real date, such as March 1, 2026, and format it to display as March 2026 if you prefer. With transaction dates in A2:A1000, amounts in E2:E1000, and the month start date in G2, enter =SUMIFS(E2:E1000,A2:A1000,">="&G2,A2:A1000,"<"&EDATE(G2,1)) in H2. For the three March transactions above, it returns 67.00. This includes dates on or after the first of the selected month and excludes the first day of the next month, so it works for months of different lengths. Change G2 to the first day of another month to review that period.
Summarize spending by category. List each category you want to compare in G5:G10. With transaction categories in C2:C1000, amounts in E2:E1000, and the category name in G5, enter =SUMIF(C2:C1000,G5,E2:E1000) in H5, then copy the formula down beside the other category names. If G5 says Groceries and the only matching example purchase is 48.20, H5 returns 48.20. Check that category names match the entries in the transaction list; even an extra space can prevent a match.
Review and protect the tracker. Use the filter arrows in the Table headings to show one month, category, or payment method at a time. Check that the filtered transactions make sense and that the totals agree with your statements. Save the workbook in a location you can find, such as a dedicated folder, and make a regular backup. If you use OneDrive, save the workbook there to use its cloud saving and version history features. Avoid putting sensitive account numbers or card security codes in the sheet.

Common Mistakes

Typing dates or amounts as text: A value that looks like a date may actually be text, which can keep date-based summaries from including it. Enter dates in a recognizable format, such as 3/3/2026, and check that Excel treats them as dates. For amounts, enter numbers without typing a currency symbol into a text-formatted cell; apply currency formatting afterward.

Using inconsistent categories: Labels like “Food,” “Groceries,” and “Grocery” may refer to the same kind of spending but appear as three different categories in a summary. Decide on the category list in advance and use Data Validation where practical.

Overwriting formulas or putting totals inside the data: A total inserted among the expense rows can be mistaken for a purchase or included in a later calculation. Place summaries outside the Table, label them clearly, and avoid typing over formula cells. If a formula is accidentally replaced, use Ctrl+Z on Windows or Command+Z on Mac immediately if you have not made other changes.

Forgetting small or cash purchases: A tracker is only as complete as its entries. Set a regular time to add transactions from receipts and statements, and check cash spending separately if you use cash often. Do not assume that a bank feed includes every purchase or that pending transactions are final.

Tips

Keep the entry process quick: use a short description, a stable category list, and notes only when necessary. A tracker you can update in a few minutes is easier to maintain than a complicated one.
Freeze the headings if the list becomes long. Select a cell in row 2, then choose View > Freeze Panes > Freeze Panes. The header row stays visible as you scroll. On Mac, the Freeze Panes command is also on the View tab.
Use the Table filters for quick checks, but clear filters before deciding that a total is the full-period total. A filtered view can hide rows even though the SUM formula still includes them.
Make a monthly review part of your routine. Compare the Excel entries with statements, investigate missing or duplicated transactions, and correct errors while the details are easy to find.
Keep the workbook simple at first. Add separate sheets for budgets, income, or reimbursements only when you have a clear reason and can explain how each sheet relates to the transaction log.

Troubleshooting

The total shows 0 or is too low. Check whether amounts are numeric and whether the formula range covers all transaction rows. Select a suspicious amount and look at the formula bar; if it is stored as text, re-enter it as a number or use the warning option Excel provides to convert text to a number. Also check for filters if you are using a subtotal formula instead of SUM.

The monthly formula misses a transaction. Confirm that the transaction date is a real date and falls within the month in G2. The month cell must also be a valid date set to the first of the month. If dates were imported as text, use Excel’s date conversion tools or re-enter them as dates, then recalculate. Check that the formula ranges include the row in question.

A category total is zero when it should not be. Compare the category spelling in G5 with the value in column C, including spaces. Select the category from the validation list instead of typing it. Confirm that the amount and category ranges in the formula cover the same rows.

The Table does not include a new row. Add the transaction in the first blank row directly below the Table, or select the last Table cell and press Tab to create another row. If you placed a blank row between the Table and the new entry, Excel may not extend it automatically. Check Table Design to confirm the new row is inside the Table.

The sheet is hard to read or prints badly. Widen narrow columns by double-clicking the boundary between column letters to AutoFit. For printing, use Page Layout to set orientation or scaling, and preview the result before printing. Avoid shrinking a very long transaction list to a single page if that makes the text unreadable.

Frequently Asked Questions

Can Excel automatically import my bank transactions?
Some Microsoft 365 setups offer data import options, but availability depends on your account, region, and bank. You can also download a CSV statement from your bank and import or open it in Excel. Review imported dates, descriptions, duplicate rows, and amounts before adding the data to your tracker.

Should I enter income in the same expense table?
For a simple expense tracker, keep the table focused on spending. If you want to track income too, add a clearly labeled Type column and use a consistent positive/negative convention, or maintain a separate income table. Do not mix conventions without adjusting the formulas, or totals can be misleading.

How do I track a recurring bill?
Enter each payment as its own transaction when it is actually paid, with its date and amount. This preserves a useful history if the bill changes. You can add a note such as “monthly rent,” but avoid pre-entering future payments as completed expenses.

Can I share the expense workbook?
Yes. Save it to OneDrive or SharePoint and use Excel’s Share controls to invite collaborators. Agree on categories and who will enter or edit transactions to reduce duplicate entries. If the workbook contains personal financial details, share it only with people who need access.

Is an Excel expense tracker suitable for tax records?
It can help organize transactions, but a spreadsheet is not a substitute for complete records or professional guidance. Keep original receipts and supporting documents as appropriate, and check recordkeeping requirements with a qualified tax professional for your situation.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.