Skip to content
Tutorials Center
My learning

Topics

Lesson 82 of 93 ยท Real-World Projects

Project: Monthly Budget Tracker

Build a monthly budget tracker in Excel: an income and expense table, SUMIFS summaries, data validation, conditional formatting and a budget chart.

In this project you build a monthly budget tracker from scratch. It has one table where you log every income and expense, a summary that adds things up by category with SUMIFS, validation so you cannot type nonsense, conditional formatting that flags overspending, and a chart. Along the way you reuse ideas from earlier lessons: Excel Tables, conditional aggregates, data validation, conditional formatting and chart basics.

Excel cannot run on this site, so the steps and formulas are shown as text, and the TryIts use a small JavaScript spreadsheet engine that really evaluates the formulas: cell references, ranges, SUM, SUMIFS, IF, ROUND and a few more. It is a teaching stand-in, much simpler than Excel's engine, but the formulas you see are the formulas you would type in Excel.

Build it in five parts

Log One row per transaction.
Summarize SUMIFS by category.
Validate Dropdowns and limits.
Flag Red when over budget.

Then add a chart. Each part works alone, so test them one at a time.

1 / 2

Step 1: the transaction log

Create a sheet with these headers in row 1 and one transaction per row, never mixing summaries into the same block:

A B C D
Date Type Category Amount

Type is either Income or Expense, and Amount is always a positive number. The Type column says which way the money flows. This beats using negative numbers for expenses because each column has a single meaning, and a typo in a sign cannot silently turn rent into income.

Select the data and press Ctrl+T to make it an Excel Table, and name it Transactions on the Table Design tab. A table grows when you type below it, and formulas can use readable names such as Transactions[Amount]. The formulas in this lesson use plain ranges like D2:D13 so the stand-in engine can evaluate them; in Excel you would write the structured version and gain automatic expansion.

Step 2: the summaries

Put the summary to the right of the log, with a gap column between them. The headline numbers use SUMIFS(sum_range, criteria_range, criterion):

TEXT
Total income    =SUMIFS(D2:D13,B2:B13,"Income")
Total expenses  =SUMIFS(D2:D13,B2:B13,"Expense")
Net             =ROUND(M1-M2,2)
Savings rate    =ROUND(M3/M1,3)

Then a category table with a budget you decide in advance:

F G H I J
Category Budget Spent Left Status
Groceries 300 =ROUND(SUMIFS($D$2:$D$13,$C$2:$C$13,F2,$B$2:$B$13,"Expense"),2) =ROUND(G2-H2,2) =IF(I2<0,"OVER","ok")

The dollar signs in $D$2:$D$13 lock the ranges, so the formula can be copied down the category list while only F2 moves (see cell references). ROUND(...,2) keeps cents tidy and avoids floating-point crumbs such as 14.050000000000011.

Run it: the budget engine

Here is the whole workbook as data. Keys are cell addresses; strings starting with = are formulas. The little engine converts each formula to JavaScript, resolves references and ranges, and caches results.

JS A budget tracker with a working formula engine

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Try changing a budget amount or adding a row to log and re-running; every dependent cell follows, just like a real sheet. (If you extend the log, widen the D2:D13 ranges too: that is exactly the problem an Excel Table solves for you.)

Step 3: validation

Bad entries are the main cause of a broken budget: a mistyped category does not match any budget row, and its money silently vanishes from the summary. Use Data > Data Validation on the log's input columns:

  • Type (column B): List with the source Income,Expense.
  • Category (column C): List pointing at the category names in your summary (=$F$2:$F$6), so only known categories are accepted. Add "Salary" and "Freelance" as income categories, or use a second list.
  • Amount (column D): Decimal greater than 0, with an error alert such as "Enter a positive amount. Use the Type column for the direction."
  • Date (column A): Date between the first and last day of the month.

Add an Input Message to each, so the rule is shown when a cell is selected. A final safety net is a check cell, comparing the sum of the category table with the total of all expenses: if they differ, a category is missing or misspelled.

TEXT
Check   =IF(ROUND(SUM(H2:H6)-M2,2)=0,"All expenses categorized","Some expenses have no budget category!")

Step 4: conditional formatting

Select the Left column and add a rule from Home > Conditional Formatting > Highlight Cells Rules > Less Than with the value 0 and a red fill. Or use a formula rule over the whole category row so the line is easy to spot (use $ before the column, as in Conditional Formatting):

TEXT
Applies to  F2:J6
Formula     =$I2<0
Format      light red fill, dark red text

For a progress feel, add data bars to Spent, or an icon set that turns yellow at 80% of budget with a rule like =$H2>=0.8*$G2. Always pair color with the Status word ("OVER") so the warning survives printing in black and white and helps color-blind readers.

Run it: validation and flags

Validation is easier to reason about as a list of rules. This stand-in tests a few new entries the way the dialog would, and then shows how a "Left < 0" rule picks rows to highlight.

JS Validation rules and an overspend flag

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Step 5: the chart

Select the category names, Budget and Spent (hold Ctrl to select non-adjacent columns), then choose Insert > Recommended Charts and pick a clustered bar (or column) chart. A bar chart suits this comparison because the category names are long and categories have no natural order (see choosing the right chart type). Then:

  1. Add a clear title, such as "Budget vs spent, March".
  2. Delete the gridlines if you add data labels, and keep the axis starting at zero.
  3. Use two calm colors: one for Budget, one for Spent.
  4. Put the chart beside the summary so a glance answers "where am I over?".

Because the chart reads the summary cells, it updates every time a transaction changes. For a spending-by-category share, a PivotTable on the log (see PivotTable basics) with Category in rows and Amount in values gives the same totals without formulas.

Extending the tracker

  • More months: add a Month column, or use one sheet per month and a yearly sheet with 3D references.
  • Pivot-based summary: replace the SUMIFS block with a PivotTable and a slicer for Month (see the dashboards lesson).
  • Protect the formulas: unlock only the input cells and protect the sheet, as described in Collaboration, Comments and Protecting Work.
  • Recurring entries: copy last month's sheet, clear the log, keep the summary.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the tracker store expenses as positive amounts with a Type column?

    A Type column keeps the direction explicit. SUMIFS handles negatives fine, but mixing meaning into the sign makes mistakes harder to notice.
  2. 2. In =SUMIFS($D$2:$D$13,$C$2:$C$13,F2,$B$2:$B$13,"Expense"), why are the ranges written with $ signs?

    Absolute references lock the ranges. Only F2 is relative, so each copy checks the next category.
  3. 3. A transaction is typed with the category "Grocries". What happens in the summary, and how can you prevent it?

    SUMIFS matches text exactly (ignoring case), so the misspelled row matches no category and its money disappears from the breakdown. Dropdown validation prevents the typo, and a check cell reveals gaps.
  4. 4. Which chart type fits "budget vs spent for five categories"?

    Two values per category are easiest to compare side by side as bars. Pie charts show parts of one whole and are poor for comparing two measures.

Summary

  • Keep one clean transaction table (Date, Type, Category, Amount) and turn it into an Excel Table.
  • Summarize with SUMIFS, lock ranges with $, and wrap money in ROUND(...,2).
  • Add a budget, a Left column and a Status word, so overspending is visible even without color.
  • Use data validation for Type, Category, Amount and Date, and a check cell to catch uncategorized money.
  • Conditional formatting flags rows where Left is below zero; a clustered bar chart shows budget vs spent.

Next: Project: Sales Analysis Report.