Make the Ultimate Personal Finance Tracker in Excel (+ Free Template), by Kenji Explains on YouTube. 18 min 18 s.
- Video length
- 18 min 18 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Open a blank workbook and add a sheet called Transactions. Set up columns for date, description, category, type (income or expense) and amount.
Enter each transaction as one row. Keep the type column consistent so income and expenses can be told apart later.
Add a second sheet for the tracker. List the 12 months down one side and your income and expense categories across the top.
Format the tracker cells as currency and widen the columns so the month and category labels are readable.
In the first month and category cell, write a SUMIFS formula that sums the amount column where the date falls in that month and the category matches.
Lock the ranges with absolute references using dollar signs, then copy the formula across all months and categories.
Add a savings row or column that subtracts total expenses from total income for each month.
Add a totals column and an annual average row so you can see yearly figures at a glance.
Apply conditional formatting to highlight negative savings or overspending in a category.
Build KPI cells for year-to-date savings and a chart of savings by month, then finish with a category breakdown chart.
Tips and common mistakes
Use absolute references in your SUMIFS ranges or the formulas will break when you copy them across the sheet.
Keep category names identical between the Transactions sheet and the tracker, or the sums will return zero.
Log transactions as they happen so the tracker stays current instead of needing a big catch-up.
Save a copy of the finished workbook as your template before you start entering real data.
Questions
Do I need to know pivot tables?
No. The tracker uses SUMIFS formulas, so you log transactions once and the totals update without any pivot tables.
Can I use this in Google Sheets?
SUMIFS and conditional formatting work in Google Sheets too, though some chart options and shortcuts differ from Excel.
What if my categories change later?
Add the new category to both the Transactions sheet and the tracker header, then copy the formula into the new column.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
