How to create Income and Expenses sheet in Microsoft Excel, by IMIND.Exceltraining on YouTube. 26 min 46 s.
- Video length
- 26 min 46 s
- Time to do it
- 25 minutes
- Difficulty
- Easy
- Steps
- 10
What you need
Steps
Open Excel and create a blank workbook. Rename Sheet1 to Transactions so the data sheet is easy to find later.
In row 1, type these headers across columns A to G: Date, Description, Category, Type, Income, Expense and Balance.
Select the headers and the rows below, then press Ctrl+T to make a table. Tick My table has headers and click OK.
Set up a category list on a second sheet with names like Salary, Rent, Food and Transport. Use it for the Category dropdown.
Select the Type column, open Data Validation, choose List and enter Income,Expense. Do the same for Category using the list range.
Type the opening balance in the first Balance cell. In the cell below, enter =previous balance + income - expense, for example =G2+F3-E3.
Copy the balance formula down the column. Each row now shows the running total after that income or expense is added.
Build a summary block with SUMIF formulas: one totals Income, one totals Expense, and one subtracts expenses from income for the net balance.
Add a totals row to the table or insert a PivotTable to group spending by category. Check the totals against your bank statement.
Save the file with a clear name and keep a backup copy. Update it regularly instead of building a new sheet each month.
Tips and common mistakes
Enter every expense as a positive number so the balance formula subtracts it correctly.
Use the same category names every time, otherwise your SUMIF totals will miss rows.
Freeze the header row with View > Freeze Panes so labels stay visible as the list grows.
Reconcile the sheet with your bank statement at the end of each month.
Questions
Do I need to use a table?
No, but a table expands formulas automatically and keeps filters and formatting consistent as you add rows.
How do I add more categories?
Add the new name to the category list sheet. The dropdown will pick it up if the validation range covers the new cell.
Why does my balance show the wrong number?
Check that income and expenses sit in the correct columns and that the first balance formula points to the opening balance cell.
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.
