A personal budget workbook
The problem
Build a monthly budget in your own Excel that records income and spending, shows the balance by category and month, and warns when a category is over its limit.
Requirements
- A Transactions Table with date, description, category, type and amount, with a validated category list
- A summary with SUMIFS totals per category and month, and the remaining budget per category
- Conditional formatting that marks categories over budget
- A chart of spending by category and a PivotTable by month
Technologies
- Excel Tables
- SUMIFS
- Data validation
- Conditional formatting
- PivotTables
- Charts
Step by step
- Create the Transactions Table and a Settings sheet with the category list and limits.
- Add data validation for categories and types.
- Build the summary with SUMIFS and absolute references to the settings.
- Add conditional formatting, a chart and a PivotTable, then protect the formulas.
Expected result
A reusable budget workbook that stays correct as new transactions are added.
How it is evaluated
- Totals match the transactions exactly
- Only valid categories can be typed
- Over-budget categories stand out
- Formulas are protected and inputs are not
Ways to improve it
- Add a yearly view with dynamic arrays
- Import bank statements with Power Query
- Add a savings goal with Goal Seek