Learn Excel in 5 Minutes by Building One Real Spreadsheet, by yocandoanything on YouTube. 4 min 5 s.
- Video length
- 4 min 5 s
- Time to do it
- 20 minutes
- Difficulty
- Easy
- Steps
- 12
What you need
Steps
Open a blank workbook. The grid is made of columns lettered A, B, C and rows numbered 1, 2, 3. Each box is a cell, and its address is the column letter plus the row number, like B4.
In row 1, type your headings: Category in A1, Budget in B1, Actual in C1 and Difference in D1. Press Tab after each entry to move right.
Type your spending categories down column A, such as Rent, Food, Transport and Fun. Widen the column by double-clicking the line between the A and B column headers to auto-fit.
Fill in your budget amounts in column B and what you actually spent in column C. Type the first two numbers, select both cells and drag the small square at the bottom right corner to AutoFill a pattern.
Select the number columns and apply currency formatting from the Number group on the Home tab. Add fill colours to the header row so it stands out.
Click cell D2 and type =B2-C2, then press Enter. This subtracts actual spending from the budget so you see what is left.
Click D2 again and drag the fill handle down to copy the formula to the other rows. Excel adjusts the row numbers for you.
Click below your last budget figure and press Alt and the equals key to AutoSum the column. Repeat for the actual column.
To show percent used, type =C2/B2 in a new column. If a budget cell is empty you get #DIV/0!, so lock the total with $ using F4, for example =C2/$B$11.
Select the Difference column, open Conditional Formatting on the Home tab, choose Highlight Cell Rules and Less Than, then enter 0 and pick a red fill to flag overspending.
Select your category and budget columns, go to Insert, pick a clustered column chart and add the actual figures as a second series.
On the View tab click Freeze Panes and choose Freeze Top Row so your headings stay visible. Save with Ctrl + S.
Tips and common mistakes
Press F4 right after clicking a cell reference in a formula to add the $ signs that lock it.
If you see #DIV/0!, a formula is dividing by an empty or zero cell, so check the budget total is filled in.
Use Tab to move across a row and Enter to move down, which is much faster than clicking each cell.
Save the file as.xlsx so formulas and formatting are kept.
Questions
Do I need to know any Excel before starting?
No. The guide starts from a blank sheet and covers cells, typing data, formulas and charts from scratch.
Why does my formula show #DIV/0!?
It means you are dividing by a cell that is empty or zero. Fill in the budget total or wrap the formula in IFERROR to hide the error.
What is the difference between a relative and an absolute reference?
A relative reference like B2 shifts when you copy the formula down. An absolute reference like $B$11 stays fixed, which is what you want for a total.
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.
