Fully Automated Income & Expense Tracker Dashboard in Excel, by Vedakarna on YouTube. 13 min 24 s.
- Video length
- 13 min 24 s
- Time to do it
- 1 hour
- Difficulty
- Medium
- Steps
- 8
What you need
Steps
Open a blank workbook and enter your transactions in columns: Date, Description, Category, Type (Income or Expense) and Amount. Keep one row per transaction and leave no blank rows.
Select the whole data range and press Ctrl+T to convert it to a table. Tick My table has headers and give the table a clear name in the Table Design tab.
Plan the dashboard on a separate sheet. Sketch where the KPI cards, income chart, expense chart and month filter will sit before adding anything.
Build KPI cards with formulas such as SUMIFS on the Type column to show total income, total expenses and net balance. Format them as currency.
Create a PivotTable from your table with Category in Rows and Amount in Values. Add a chart from the PivotTable and place it in the expense area of the dashboard.
Add a second PivotTable or chart for income so income and expenses sit side by side. Use conditional formatting to colour negative balances red.
Insert a slicer for the Date field and connect it to your PivotTables. Use it to filter the dashboard by month.
Add a profit and loss block that subtracts total expenses from total income. Check the figure against your raw data before you rely on it.
Tips and common mistakes
Keep all raw transactions on one sheet and never type figures directly into the dashboard, so refreshes stay accurate.
Press Refresh All after adding new transactions or the charts and KPI cards will show old numbers.
Use consistent category names, because a typo creates a separate category in the PivotTable.
Save a copy of the workbook before you start changing formulas.
Questions
Do I need PivotTables to build this dashboard?
You can use SUMIFS formulas and regular charts instead, but PivotTables make monthly filtering and category totals much faster.
Why do my charts not update when I add rows?
The new rows are probably outside the table. Add them inside the table range or refresh the PivotTable with Refresh All.
Can I use this in Google Sheets?
The layout works, but slicers and some PivotTable options differ. Excel gives the closest match to the steps here.
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.
