How to track expenses AUTOMATICALLY in Excel | BUDGET SPREADSHEET, by Money with Dan on YouTube. 23 min 20 s.
- Video length
- 23 min 20 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Log in to your bank and download each month's transactions as a CSV file. Save all the files in one folder you can find easily.
Open one CSV in Excel to see how the columns are laid out, then create a new workbook with a sheet named Data.
Copy the rows from the CSV and paste them into the Data sheet, keeping the header row at the top.
In a new column, use a LEFT formula to pull a short code from each description, for example the first few characters that identify the merchant.
Build a list of unique identifiers, one for each spending category, so every code from your bank maps to a single category.
Add a VLOOKUP formula beside the data that turns each identifier into its category name, then copy the formula down the whole column.
Select the data and insert a pivot table on a new sheet. Put category in Rows and the amount column in Values.
Format the pivot table: set the values to currency, rename the headers and sort the categories from largest to smallest.
Paste the next month's CSV rows under the first month on the Data sheet, then refresh the pivot table to include them.
Add MONTH and YEAR formulas to a date column, use them as a filter in the pivot table, then insert and format a pie chart.
Tips and common mistakes
Keep one transaction per row with no blank rows, or the pivot table will skip part of your data.
Leave the original description column untouched and put the identifier in a separate helper column.
Refresh the pivot table after every new month, otherwise the totals stay out of date.
Save a backup copy of the raw CSV files before you edit anything in the workbook.
Questions
Do I need any add-ins or paid tools for this?
No. Everything uses built-in Excel features: the LEFT and VLOOKUP formulas, a pivot table and a pie chart.
What if my bank puts dates and amounts in an unusual layout?
Adjust the formulas so they point at the right columns, or split the columns with Text to Columns first. Keep the same layout every month.
How do I add a new month later?
Paste the new CSV rows under the last month on the Data sheet, then right-click the pivot table and choose Refresh.
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.
