How to create an Excel Cashflow Template with Dashboard step by step, by Jopa Excel on YouTube. 29 min 19 s.
- Video length
- 29 min 19 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Open a new Excel workbook and add three sheets named List, Cashflow movements and Dashboard. Keep them in that order so the formulas are easy to follow.
On the List sheet, type your income categories in one column, such as salary and side hustle. Put expense categories in another column, such as rent, mortgage and electricity bill.
On the Cashflow movements sheet, build a table with these headers: Date, Month, Year, Category, Description, Amount, Type and Extra note.
In the Month column, enter a formula that pulls the month from the Date cell, for example =TEXT(A2,"mmmm"). In the Year column use =YEAR(A2). Fill both down the column.
Select the Category column and use Data, then Data Validation, then List. Point the source at your category range on the List sheet so each row gets a drop-down.
In the Type column, add a formula that returns Money In when the amount is positive and Money Out when it is negative, for example =IF(F2>0,"Money In","Money Out").
Log each transaction by entering the date, picking a category, adding a description and typing the amount. Use positive numbers for income and negative numbers for spending.
On the Dashboard sheet, add totals with SUMIFS: one for total Money In, one for total Money Out and one for the balance. Reference the Type and Category columns.
Add a small summary table by category using SUMIFS, then insert a chart from it. A bar or pie chart works well for showing where your money goes.
Apply conditional formatting to the Amount or Type column so Money In rows turn green and Money Out rows turn red. Use Home, then Conditional Formatting, then Highlight Cells Rules.
Tips and common mistakes
Keep the Date column in a real date format, otherwise the Month and Year formulas return errors.
Use negative amounts for expenses so the Type formula and dashboard totals stay correct.
Lock the List sheet cells or hide the sheet once your categories are set to avoid breaking the drop-down.
Save a blank copy as your template before you start entering real transactions.
Questions
Do I need to know Excel formulas to build this?
Only basic ones. The Month, Year and Type columns use short formulas like TEXT, YEAR and IF, and the dashboard totals use SUMIFS. You can copy them from the steps above.
Can I add more categories later?
Yes. Add them to the List sheet and the drop-down will include them. Just make sure the data validation range covers the new cells.
Why do my dashboard totals show zero?
Check that the Type column formula is filled down every row and that the SUMIFS ranges match the table size. A mismatch in ranges is the usual cause.
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.
