
In this guide
- Step 1: Create the transaction sheet
- Step 2: Choose your categories
- Step 3: Use dropdown lists
- Step 4: Build the summary sheet
- Step 5: Add monthly views
- Step 6: Set budget targets
- Step 7: Make a habit of updating
- Step 8: Protect and back up your data
- If you run a side business
- Common mistakes
- Frequently asked questions
- Final thoughts
A simple spreadsheet can show you where your money comes from and where it goes. It is free, flexible and works for personal budgets, side hustles and small businesses. You do not need advanced skills, only a handful of formulas.
This guide shows how to build an income and expense tracker in Excel or Google Sheets, step by step, with the formulas you need and tips for keeping it accurate.
Key takeaways
- One table of transactions plus one summary sheet is enough to start.
- Use consistent categories so your totals make sense.
- Update it weekly, not once a year.
- Keep receipts and records, especially for side income and taxes.
Step 1: Create the transaction sheet
Open a new spreadsheet and name the first sheet “Transactions.” Add these column headings in row 1:
| Column | What goes in it |
|---|---|
| A: Date | The date of the transaction |
| B: Description | A short note, such as “Groceries” or “Client payment” |
| C: Category | For example Food, Transport, Bills, Income |
| D: Type | Income or Expense |
| E: Amount | The amount as a positive number |
Step 2: Choose your categories
Keep the list short, around 8 to 12 categories, so it is easy to use. Typical expense categories are housing, food, transport, bills, health, fun, savings and debt. For income, use categories such as salary, side income and other. Consistent spelling matters, because formulas treat “Food” and “food ” differently in some tools.
Step 3: Use dropdown lists
Add dropdown lists to the Category and Type columns using the data-validation feature. This avoids typing mistakes and speeds up entry. Create a small “Lists” sheet with your category names and point the dropdown at it.
Step 4: Build the summary sheet
Add a second sheet called “Summary.” In one column list your categories. Next to each, use the SUMIFS formula to add up amounts:
=SUMIFS(Transactions!E:E, Transactions!C:C, A2, Transactions!D:D, "Expense")
This adds all expense amounts in Transactions where the category matches the name in cell A2. Copy the formula down the list. For a total income cell, use:
=SUMIFS(Transactions!E:E, Transactions!D:D, "Income")
Subtract total expenses from total income to see what is left. Our guide on Excel formulas for beginners explains SUM, IF and other formulas you may want.
Step 5: Add monthly views
To see one month at a time, add a column that extracts the month from the date. In a helper column, use =TEXT(A2,"yyyy-mm"), then add the month as another condition inside SUMIFS. A simple chart of spending by category makes patterns easy to see.
Step 6: Set budget targets
Next to each category in your summary, add a “Budget” column with the amount you plan to spend, and a “Difference” column that subtracts actual from budget. This turns your tracker into a budgeting tool. For how to set sensible targets, read our guide to building a budget.
Step 7: Make a habit of updating
Set a weekly 10-minute reminder to enter transactions from your bank app and receipts. Waiting months makes it slow and inaccurate. Many banks let you download a statement as a spreadsheet file that you can paste in and categorise.
Step 8: Protect and back up your data
- Use a strong password on the file or account that stores it. See our guide to protecting personal information.
- Do not include account numbers or full card details.
- Back the file up regularly.
If you run a side business
Add a column for “Business or personal,” keep receipts and note any tax-related items. Tax rules differ by country, so check official guidance and consider asking a qualified accountant. Our guide on starting a side hustle mentions record keeping as well.
Common mistakes
- Using too many categories so entry becomes a chore.
- Entering expenses as negative numbers in some rows and positive in others.
- Forgetting cash purchases.
- Not updating the sheet for months.
Frequently asked questions
Excel or Google Sheets?
Both work. Google Sheets is free and easy to share. Excel has more advanced features.
Can I use this for a small business?
Yes, for simple needs. As the business grows, consider accounting software and professional advice.
How do I handle recurring bills?
Enter them as regular transactions each month, or copy rows forward. You can also set reminders.
Final thoughts
A tracker does not need to be fancy. A clean transaction list, a few formulas and a weekly habit will give you a clear picture of your money. Start simple, then add charts and budgets as you gain confidence.
Next: visit our Start Here page and join our Telegram channel.




















































