How to Create a Finance Expenses Dashboard in Excel for Beginners (Step-by-Step Tutorial), by Vedakarna on YouTube. 28 min 50 s.
- Video length
- 28 min 50 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Create a table with the columns Date, Description, Category, Subcategory, Type and Amount. Type Income or Expense in the Type column, enter one row per transaction, and leave no blank rows or merged cells.
Click any cell in the data and press Ctrl+T to convert it into an Excel Table. Give the table a clear name in the Table Design tab so it grows when you add rows.
Add a Month column with the formula =TEXT([@Date],"mmm") so each row shows a short month name. This lets you group spending month by month in the pivot tables.
Go to Insert, then PivotTable, and place it on a new sheet. Drag Month into Rows, Type into Columns, and Amount into Values so it sums. You now see income and expenses side by side.
Build a second PivotTable with Category in Rows and Amount in Values, then filter Type to Expense. Sort it largest to smallest so your biggest spending areas appear at the top.
Select each pivot table and insert charts: a doughnut chart for spending by category, a bar chart for income versus expenses by month, and a waterfall chart for net income.
Click inside a pivot table, choose Insert Slicer, and tick Month and Category. Use Report Connections to link the slicers to your other pivot tables so one click filters everything.
Add a new sheet for the dashboard. Paste the charts, then add cells for total income, total expenses and net income using SUMIFS on the Type and Amount columns.
Tidy the layout: turn off gridlines under View, add a bold title, keep one colour scheme, and line the charts up in a grid so the numbers read top to bottom.
Tips and common mistakes
Use the same category spelling every time, or your totals will split across near-identical labels.
Create a small list of categories on a side sheet and use Data Validation so you pick from a drop-down instead of typing.
After adding new transactions, click Data then Refresh All, or the pivot tables and charts will show old numbers.
Keep raw data on its own sheet and never delete rows from the middle of the table.
Questions
Can I add new expenses after the dashboard is built?
Yes. Type them into the bottom of the Excel Table, then click Data and Refresh All. The pivot tables, charts and slicers update to include the new rows.
Do I need macros or Power Pivot for this?
No. Standard PivotTables, PivotCharts and slicers are enough, and they work in Excel 2016 and later.
How do I get monthly totals?
Add a Month column with =TEXT([@Date],"mmm") and put Month in the Rows area of your pivot table. Each month then gets its own row of income and expense totals.
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.
