Spreadsheets

Build an Excel expense tracker from scratch in six steps

Build an Excel expense tracker from scratch in six steps: log sheet, table, category dropdown, SUMIFS summary, chart and sheet protection, with the final structure.

Editorial illustration for Build an Excel expense tracker from scratch in six steps

You can build a working Excel expense tracker in six steps: a log sheet, a table, a category list with a dropdown, a SUMIFS summary, a chart and light protection. It ends up as a workbook with three sheets, Log, Lists and Summary, and takes about half an hour the first time.

Step 1: What goes on the log sheet?

Rename Sheet1 to Log. In row 1 type five headers: Date, Category, Amount, Payment mode and Notes. Every purchase will be one row beneath that. Format the Date column as a date and the Amount column as a number with two decimals. Freeze the top row through View, Freeze Panes so the headers stay visible.

Step 2: How do you turn it into a table?

Click any header cell and press Ctrl+T, tick My table has headers, then rename the table to Expenses on the Table Design tab. Now every new row extends the table, and your formulas can point at columns by name.

Step 3: How do you set up the category dropdown?

  1. Add a sheet called Lists. In A1 type Categories, then list your categories beneath it: Rent, Groceries, Utilities, Transport, EMI, Dining out, Medical, Shopping, Other.
  2. Go back to Log and select the Category cells in the first rows of the table.
  3. Choose Data, Data Validation, set Allow to List, and in Source enter =Lists!$A$2:$A$20.

Leave a few blank cells below your list so you can add categories later. Because the table extends validation down for new rows, every future entry has the dropdown.

Step 4: What does the Summary sheet need?

Add a sheet called Summary. Put the first day of the month you are viewing in B1, formatted as a date. List the categories in A3 downward, or link them to the Lists sheet with =Lists!A2. In B3, total each category for the month:

=SUMIFS(Expenses[Amount], Expenses[Category], $A3, Expenses[Date], ">="&$B$1, Expenses[Date], "<"&EDATE($B$1,1))

Fill it down. At the bottom, add =SUM(B3:B11) for the month's total. To show each category's share, put =IF($B$12>0, B3/$B$12, 0) in C3 and format it as a percentage.

CategoryMonth totalShare
Rent₹18,00036.0%
Groceries₹9,50019.0%
EMI₹8,00016.0%
Everything else₹14,50029.0%
Total₹50,000100.0%

These are invented figures to check your arithmetic: 18,000 plus 9,500 plus 8,000 plus 14,500 is 50,000, and 18,000 divided by 50,000 is 36%.

Step 5: How do you add a chart?

Select the category names and month totals, then choose Insert, Recommended Charts, and pick a bar or column chart. Bars suit long category names. Pie charts get hard to read after five or six slices. Place the chart beside the table on the Summary sheet. Because it reads the summary cells, it updates when you change B1.

Step 6: What should you protect?

The danger is overtyping a formula by accident. On the Summary sheet, choose Review, Protect Sheet. Leave only B1 unlocked first by selecting that cell, opening Format Cells, Protection, and unticking Locked. You can add a lock phrase if you like, but keep a record of it, because Excel cannot recover it. Leave the Log sheet unprotected so that the table can grow.

What does the final workbook look like?

  • Log: the Expenses table with Date, Category, Amount, Payment mode and Notes.
  • Lists: your categories, and anything else you want in dropdowns.
  • Summary: month picker, SUMIFS by category, total, percentage share and one chart.

Open the workbook once a week, enter or paste the entries, and glance at the Summary. If keeping it up by hand becomes the bottleneck, SpendixAI can import a statement in PDF, CSV or Excel, with a review step before saving, so you are not retyping what the bank already knows.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports