How to Track Expenses in Excel or Google Sheets (Free Template)

You can build a working expense tracker in Excel or Google Sheets in about fifteen minutes: a log of what you spend, a dropdown so categories stay consistent, and a summary table that totals each category automatically. This guide builds it step by step, using formulas that work in both programs.

If you would rather skip the setup, our free expense tracker template already has all of this built in, with the same structure this guide walks through.

What you're building

Two tables, side by side on one sheet:

1. The expense log: one row per purchase, with a date, description, category, amount and payment method.
2. The summary table: one row per category, with a budget, a total pulled automatically from the log, and what's left.

That's the whole structure. Everything below is set-up.

Step 1: Set up the expense log

In your first sheet, put these headers across row 1:

Leave the rest of row 1 for now. You'll add the summary table a few columns to the right, so the two tables can sit next to each other without overlapping.

Step 2: Add a category dropdown

Before you log a single expense, list your categories somewhere on the sheet, for example in column H starting at H2: Housing, Food, Utilities, Transportation, Entertainment, Savings, Healthcare, Other. Six to ten categories is enough. More than that and you'll stop bothering to pick the right one.

Now turn column C into a dropdown that points at that list:

• Google Sheets: select column C (or just C2:C500), then Data > Data validation. Under Criteria choose Dropdown (from a range), and select your category list.
• Excel: select the same range, then Data > Data Validation. Under Allow, choose List, and set Source to your category range.

From now on, clicking a cell in column C shows a dropdown instead of a blank cell to type in. This one step prevents almost every problem people hit later, because the formulas in the next section only work if "Food" is always spelled and capitalised the same way.

Step 3: Build the summary table

Next to your category list, add three more columns: Monthly budget, Spent and Remaining.

In the Spent column, use SUMIF to add up every row in the expense log that matches that category:

=SUMIF($C$2:$C$500, H2, $D$2:$D$500)

This says: look through the category column (C2 to C500), find every row that matches the category in H2, and add up the matching amounts from column D. The dollar signs keep the ranges fixed when you copy the formula down to the next category; only H2 changes to H3, H4, and so on.

In the Remaining column, subtract what's spent from the budget:

=I2-J2

Copy both formulas down one row per category. The range is set to row 500 on purpose, well past however many expenses you have now, so new rows you add later are automatically included without editing the formula again.

This is the same layout our free template uses, so if any of this feels fiddly, downloading it gets you the same result without typing a formula.

Step 4: Add your income and a "left this month" total

Below the category table, add three more rows:

"Total spent" adds up the whole expense log, not just one category, which is a useful check: it should equal the sum of every category's "Spent" figure.

A worked example

Here's the tracker after logging nine expenses against a $4,000 income, using the same numbers as our free template:

Total spent across every category is $1,844.84. With a $4,000 income, that leaves $2,155.16 for the rest of the month. Housing is exactly on budget, and every other category still has room, which is the kind of thing this table makes obvious at a glance instead of something you'd have to add up by hand.

Step 5: Make problems visible with conditional formatting

Add a rule so the Remaining column turns red the moment a category goes over budget.

• Google Sheets: select the Remaining column, then Format > Conditional formatting. Choose Custom formula is, enter =K2<0, and set a red background.
• Excel: select the same column, then Home > Conditional Formatting > New Rule > Format cells that contain, set the condition to "less than 0", and pick a red fill.

Now an overspent category is visible without reading a single number.

Step 6: Add a chart

Select your category table (the Category and Spent columns) and insert a pie or column chart: Insert > Chart in either program. A pie chart makes it obvious which one or two categories are eating most of your budget, which a column of numbers doesn't show as clearly.

Keeping it accurate month to month

A few habits make the difference between a tracker you keep using and one you abandon after a few weeks:

• Log expenses the same day. Small purchases are the first ones you forget, and a spreadsheet you update once a week from memory will be wrong.
• Freeze the header row (View > Freeze > 1 row in Sheets, View > Freeze Panes in Excel) so column names stay visible as your log grows.
• One tab per month, or one running log. A new sheet tab each month keeps totals simple and easy to compare side by side. A single log across the whole year works better with a pivot table if you want to see trends without opening twelve tabs.
• Protect the formula cells. In Google Sheets, select the summary table and use Data > Protect sheets and ranges so a stray keystroke can't overwrite a formula. In Excel, unlock the input cells first (Format Cells > Protection), then use Review > Protect Sheet.
• Start a fresh copy each month rather than deleting last month's rows, so you can look back and compare.

Google Sheets only: log expenses from your phone without opening the sheet

Google Forms can write straight into a Google Sheet, which is a fast way to log a purchase without opening the spreadsheet app at all:

1. In your Google Sheet, go to Insert > Form. This creates a linked Google Form with one question per column you select.
2. Add the form to your phone's home screen (open it in your phone's browser and use Add to Home Screen).
3. Submitting the form adds a new row to your expense log automatically, already in the right columns.

This is a reasonable middle ground if you want to keep everything in a spreadsheet but don't want to open the full sheet every time you buy coffee.

Excel only: turning your log into a table

In Excel, select your expense log and use Insert > Table (or Ctrl+T). This gives the log a name, extends formatting and dropdowns automatically as you add rows, and lets formulas elsewhere reference the table by name instead of a fixed range like $D$2:$D$500. It's optional, but it removes the "did I extend the range far enough" question entirely.

When a spreadsheet stops being enough

A spreadsheet works well until you want it on your phone without opening an app, or you want a budget bar that fills up as you spend, or you keep forgetting to log the same recurring bill every month. At that point, either add the Google Forms shortcut above, or use an app instead. Our budget calculator can set your category budgets before you start, and Salary Left tracks the same thing as this template, on your phone, with recurring expenses and budgets built in, and nothing to maintain.

Sources

Best practices in this guide (category count, logging habits, keeping formula ranges dynamic) reflect common personal-finance and spreadsheet advice rather than any single source. The formulas and menu paths were checked against the current Google Sheets and Microsoft Excel help documentation for SUMIF and data validation.