How to Add a Transaction Tracker to Your Budget in Google Sheets | Step-by-Step Beginner Tutorial, by My Productive Life Co on YouTube. 19 min 44 s.
- Video length
- 19 min 44 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Open your budget sheet and add a Difference column next to each Actual column. Use a formula like =Budgeted-Actual so you can see how far off you are.
Add a Total Outflow cell that sums all your expense Actual amounts, so you can compare total spending against total income.
Select the Difference column and open Format, then Conditional formatting. Set a rule that turns cells red when the value is below zero, meaning you went over budget.
Create a new sheet tab called Transactions. Add headers for Date, Description, Category and Amount, then select the header row and choose Data, then Create a filter.
In the Actual column of your budget, enter a SUMIF formula that adds transaction amounts where the category matches. Point it at the Category and Amount columns on the Transactions sheet.
Build a category reference list on a helper area using =FLATTEN(FILTER(...)) to pull every category name from your budget tables into one column.
Select the Category cells in your tracker and choose Data, then Data validation. Set the criteria to Dropdown and point the range at your category list.
Add a conditional formatting rule on the tracker that highlights any category not found in your budget list, so typos and missing categories stand out.
Paste transactions from your bank statement or CSV into the tracker. Paste as values only so your dropdowns and formatting stay intact.
Tips and common mistakes
Paste bank data with Ctrl+Shift+V or Edit, Paste special, Values only to keep your dropdown menus working.
Keep category names identical between the tracker and the budget, or the SUMIF formulas will miss those amounts.
Test the tracker with one or two sample transactions before pasting a full month of data.
Check the Difference column after each update so you catch overspending early.
Questions
Do I need to type every transaction by hand?
No. You can copy rows from your online bank statement or a downloaded CSV file and paste them into the tracker, then fix the category for each row using the dropdown.
Why do my Actual amounts stay at zero?
The SUMIF formula is probably pointing at the wrong range or the category names do not match exactly. Check for extra spaces and confirm the formula references the Category and Amount columns.
Can I use this with Excel instead?
The SUMIF and conditional formatting steps work in Excel, but FLATTEN is a Google Sheets function. In Excel you would build the category list another way.
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.
