Monthly Expense Tracker with Categories & AI Tips

An expense tracker records what you spend, groups it by category, and shows where your money goes each month. You build it in Excel and let Copilot and ChatGPT write the tricky formulas.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It logs every expense with date, category, and amount, then totals spending per category and per month.
• Where it is used: Personal finance, freelancers, and small teams tracking costs.
• What you learn: data validation, SUMIFS, running balance, charts, and AI prompt writing for formulas.

Features

• Add expenses by date, category, and amount
• Monthly total and per-category totals
• A pie chart of spending by category
• A running balance if you also enter income
• AI suggestions on where to cut costs

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account (free or paid)
• Basic skill with tables and simple formulas
• A category list such as Food, Rent, Transport, Bills

Folder Structure

expense-tracker.xlsx
│
├── Sheet: Expenses   (Date, Category, Amount, Note)
├── Sheet: Summary    (monthly total, category totals, chart)
├── Sheet: Categories (valid category list)
└── Module: ExpMacros (optional VBA to add a row)

• Expenses: one row per spend.
• Summary: the totals and chart.
• Categories: the list used for the dropdown.
• ExpMacros: a small form to add expenses fast.

Step-by-Step Development

Step 1 – Build the expense table and dropdown

What are we building?

A table named Exp with a Category column limited to a fixed list.

Why do we need it?

A dropdown stops typos like "food" vs "Food" that break later totals.

How do we build it?

Make a table with Ctrl + T, name it Exp. Then select the Category column and use Data > Data Validation > List pointing at Categories!A:A.

=SUM(Exp[Amount])

• What it does: adds every amount in the table.
• Why needed: this is the monthly spend total shown on the Summary sheet.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"In Excel, how do I add a dropdown to a column that pulls values from another sheet's list?"

Step 2 – Total by category with SUMIFS

What are we building?

A small table of category vs total spent.

Why do we need it?

It shows which category costs the most.

How do we build it?

=SUMIFS(Exp[Amount], Exp[Category], A2)

• What it does: sums Amount only where Category equals the label in A2.
• Important line: the two pairs (range, criteria) filter the sum.
• Why needed: each category gets its own total for the chart.

Use AI (Copilot & ChatGPT)

Ask Copilot in the pane:

"Add a formula that totals the Amount column of the Exp table where Category matches A2."

Step 3 – Add a running balance

What are we building?

A Balance column that subtracts each expense from a starting amount.

Why do we need it?

It tells you how much is left after each spend.

How do we build it?

Put a starting balance in B1. In the first data row:

=$B$1 - SUM($C$2:C2)

Assuming Amount is column C.

• What it does: starting balance minus the running sum of amounts so far.
• Important line: $C$2:C2 grows as you copy down, so the sum expands row by row.
• Why needed: shows money left at each step.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel formula for a running balance that subtracts each row's amount from a fixed starting value in B1."

Step 4 – Chart and AI insight

What are we building?

A pie chart of category totals and a short AI note.

Why do we need it?

A chart is faster to read than a table.

How do we build it?

Select the category totals and choose Insert > Pie Chart.

Use AI (Copilot & ChatGPT)

Copy the category totals into ChatGPT:

"Which two categories should I cut to save the most money? Answer in two sentences."

Paste the reply near the chart.

Complete Source Code

Expenses table

Date | Category | Amount | Note

Summary formulas

=SUM(Exp[Amount])
=SUMIFS(Exp[Amount], Exp[Category], A2)
=$B$1 - SUM($C$2:C2)

AI prompts

ChatGPT: "How do I add a dropdown from another sheet's list?"
Copilot: "Total Amount where Category matches A2."
ChatGPT: "Running balance subtracting each row from B1."
ChatGPT: "Which two categories should I cut to save the most?"

Running the Project

1. Open expense-tracker.xlsx.
2. Enter expenses in the Exp table; pick a category from the dropdown.
3. The Summary sheet updates totals and the pie chart.
4. Expected output: monthly total at top, category list with amounts, and a pie chart.

Testing

Test Case 1

Input: Food 10, Rent 100, Food 20. Expected Output: Food = 30, Rent = 100, Total = 130. Explanation: SUMIFS groups by category; SUM adds all.

Test Case 2

Input: Starting balance 200, then expenses 50, 30. Expected Output: Balance after row 1 = 150, after row 2 = 120. Explanation: The running sum expands with each row.

Test Case 3

Input: Type "fod" (not in list). Expected Output: Excel blocks the entry with a validation warning. Explanation: Data validation only allows listed categories.

Common Errors

SUMIFS returns 0

Problem: Category total is zero. Reason: The typed category does not exactly match the list. Solution: Use the dropdown so spelling and case always match.

Balance shows negative

Problem: Balance drops below zero. Reason: Spending passed the starting amount. Solution: Raise the starting balance in B1 or check for a wrong amount.

Pie chart missing a slice

Problem: One category does not appear. Reason: Its total is 0 because of a typo in the category name. Solution: Fix the category spelling so SUMIFS picks it up.

Improvements

• Add a monthly comparison chart (this month vs last)
• Set a budget per category and flag overspend in red
• Use Power Automate to add bank exports automatically
• Ask ChatGPT for a weekly saving tip based on your data
• Export the Summary sheet to PDF for records

Challenge Exercises

1. Add an Income column and show net balance (income minus expenses).
2. Build a formula that flags any category over 30% of total spending.
3. Use ChatGPT to write a VBA form that adds an expense with one click.
4. Create a second sheet comparing this month to last month by category.
5. Connect your bank CSV export and let ChatGPT clean and categorize it.