How to Create an Expense Tracker in Excel | Simple & Automated Budget Sheet, by Jopa Excel on YouTube. 17 min 45 s.
- Video length
- 17 min 45 s
- Time to do it
- 25 minutes
- Difficulty
- Easy
- Steps
- 9
What you need
Steps
Open a blank workbook and type column headers in row 1: Date, Description, Category, Amount and Month. Leave a few rows free above for a title if you like.
In the Month column, enter a formula such as =TEXT(A2,"mmmm") next to the first date so each expense shows its month name automatically.
Select the Category column cells and open Data, then Data Validation. Choose List and type your categories separated by commas, or point to a list on another sheet.
Select the Amount column, right-click and choose Format Cells, then Currency. Pick your currency symbol and two decimal places.
Select any cell in your data and press Ctrl+T to turn the range into an Excel table. Tick My table has headers so new rows inherit formulas and formatting.
Enter a few test expenses with dates, descriptions, categories and amounts to check the drop-down and month formula work.
Click inside the table, go to Insert and choose PivotTable. Place it on a new worksheet so it stays separate from your data.
Drag Month into Rows, Category into Columns and Amount into Values. Set the value field to Sum so you see total spending per category each month.
Add a new expense to the table, then right-click the PivotTable and choose Refresh to confirm the summary updates with your new row.
Tips and common mistakes
Keep one row per expense and never leave blank rows inside the table, or the Pivot Table will miss data.
Use Data Validation for the Category column so typos do not split one category into two.
Refresh the Pivot Table after adding expenses, since it does not always update on its own.
Save the file as.xlsx so tables, formulas and Pivot Tables keep working.
Questions
Do I need to know formulas to build this tracker?
Only one simple formula is needed for the month column. Everything else uses Excel menus like Data Validation, Format Cells and Insert PivotTable.
Can I add my own expense categories?
Yes. Edit the list in the Data Validation settings or the source list on your second sheet, and the drop-down updates.
Why does my Pivot Table not show new expenses?
Pivot Tables need a refresh. Right-click the table and choose Refresh, or press Alt+F5, after adding rows.
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.
