ULTIMATE Personal Budget & Interactive Dashboard in Excel (FREE Template), by MyOnlineTrainingHub on YouTube. 33 min 20 s.
- Video length
- 33 min 20 s
- Time to do it
- 60 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Create two Excel Tables on a sheet: one listing your income categories and one listing your expense categories. Format each range as a table with a header row so Power Query can read it.
Build a budget table with a row for each category and a column for each month. Enter your planned amount for every category and month so you have a target to compare against.
Create a transactions table with columns for date, description, amount and category. Paste your bank statement rows into it and tag each row with a category using a data validation drop-down.
Clean the pasted bank data: remove blank rows, fix dates that imported as text, and make sure amounts are numbers. Consistent column headers matter here.
Open Data, then Get Data, then From Table/Range for each of the three tables. In Power Query, use Append or Merge Queries to combine the budget, transactions and category tables into one dataset.
Load the merged query to a new worksheet. Insert a PivotTable from it with categories on rows and months on columns, then add calculated fields for budget and actual amounts.
Add PivotCharts for expenses against budget by category and for income and spending by month. Place them on a dashboard sheet and arrange them in a grid.
Insert a timeline slicer for the date field and connect it to the PivotTables so one control filters the whole report to any date range.
Add a profit and loss section with conditional formatting icons: a green tick when a category is within budget and a red cross when it is over. Use GETPIVOTDATA to pull the headline figures.
Each month, paste new bank transactions into the transactions table, tag each row with a category, then click Data, then Refresh All to update the dashboard.
Tips and common mistakes
Keep the three source tables on separate sheets and never type inside the Power Query output, or your next refresh will overwrite it.
Name your tables clearly in Table Design so you can find them quickly when editing the query.
If dates import as text, change the column type to Date in Power Query rather than fixing them by hand.
The Insert Icons feature needs Microsoft 365, but any small image works as a dashboard icon in older versions.
Questions
Do I need Power Query experience?
No. The video walks through merging the tables step by step, and once set up the query refreshes with one click each month.
Will this work in Excel 2016?
Yes, Power Query and PivotTables are built in. Only the Insert Icons feature needs Microsoft 365, and you can swap in any image.
How long does the monthly update take?
A couple of minutes: paste the statement, tag each row with a category, and click Refresh All.
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.
