What You Will Build Today
By the end of this workshop, you will have created a complete personal budget spreadsheet in Microsoft Excel with formulas, sorting, filtering, a chart, and your own real spending data.
- A budget spreadsheet with 8 expense categories formatted as currency
- Formulas that automatically calculate totals, averages, highest, and lowest
- Sorted and filtered data to find spending patterns
- A bar chart visualizing your budget at a glance
Goal: Set up a budget spreadsheet with 8 categories and format amounts as currency.
Build Your Budget Spreadsheet
Follow along with your instructor to create the foundation of your personal budget tracker.
-
Open Microsoft Excel and create a new blank workbook
Start menu → type "Excel" → click "Blank workbook"
-
In cell A1, type "Category" — in cell B1, type "Budgeted" — in cell C1, type "Actual"
Click cell A1, type "Category", press Tab to move to B1, type "Budgeted", Tab to C1, type "Actual"
-
Bold the header row: select A1:C1 → press Ctrl+B
Click A1, hold Shift, click C1, then press Ctrl+B to bold
-
Enter 8 expense categories in column A (rows 2–9)
Type each category in its own row: Rent/Mortgage, Groceries, Utilities, Transportation, Insurance, Phone, Medical/Copays, Entertainment
Sample budget data — enter these values to follow along:
| Category | Budgeted | Actual |
|---|
| Rent/Mortgage | $850 | $850 |
| Groceries | $300 | $335 |
| Utilities | $150 | $142 |
| Transportation | $120 | $98 |
| Insurance | $200 | $200 |
| Phone | $65 | $65 |
| Medical/Copays | $80 | $45 |
| Entertainment | $100 | $127 |
-
Select all dollar amounts (B2:C9) → right-click → Format Cells → Number tab → Currency → OK
This adds dollar signs and two decimal places automatically
-
Widen columns if needed: double-click the column border between A and B to auto-fit
Hover your mouse on the line between column letters A and B until you see a double arrow, then double-click
Tip: Formatting cells as Currency before entering data means every number you type will automatically show with a dollar sign. You only need to do this once!
Checkpoint — Raise your hand when done:
- ☐ Header row (A1:C1) is bold
- ☐ 8 expense categories listed in column A
- ☐ All amounts show as currency ($0.00 format)
Goal: Use SUM, AVERAGE, MAX, and MIN formulas to analyze your budget data.
Add Formulas to Analyze Your Budget
Formulas are what make Excel powerful. They automatically calculate results from your data.
-
In cell A10, type "TOTAL" and bold it
Click cell A10, type "TOTAL", then press Ctrl+B to bold
-
Click cell B10, type =SUM(B2:B9) and press Enter
This adds up all your budgeted amounts. You should see $1,865.00
-
Click cell C10, type =SUM(C2:C9) and press Enter
This adds up all your actual spending. You should see $1,862.00
-
In cell A12, type "AVERAGE" — in B12, type =AVERAGE(B2:B9) — in C12, type =AVERAGE(C2:C9)
AVERAGE divides the total by the number of items — your average budgeted expense
-
In cell A13, type "HIGHEST" — in B13, type =MAX(B2:B9) — in C13, type =MAX(C2:C9)
MAX finds the largest number — your biggest expense category
-
In cell A14, type "LOWEST" — in B14, type =MIN(B2:B9) — in C14, type =MIN(C2:C9)
MIN finds the smallest number — your least expensive category
-
Try the Fill Handle: click cell B10, notice the small square in the bottom-right corner, drag it right to C10
The fill handle copies formulas to adjacent cells, adjusting the column automatically!
-
Add a Difference column: In D1 type "Difference", bold it. In D2 type =C2-B2, press Enter. Use the fill handle to drag D2 down to D9.
Negative numbers mean you are under budget (good!). Positive numbers mean you went over.
Tip: The #1 mistake is forgetting to start formulas with the equals sign (=). If your formula shows as text instead of a number, check that it starts with =.
Common Error: If you see #VALUE! or #REF! errors, do not panic! Click the cell and check the formula bar — usually it is a typo in the cell range. Try retyping the formula.
Checkpoint — Raise your hand when done:
- ☐ SUM formulas calculate totals in row 10
- ☐ AVERAGE, MAX, and MIN formulas in rows 12–14
- ☐ Difference column (D) shows over/under for each category
Goal: Sort your data, apply filters, and create a bar chart.
Sort and Filter Your Data
Sorting and filtering help you find patterns in your spending without changing your data permanently.
-
Click anywhere in your data (A1:D9) → Data tab → Sort → Sort by "Actual" → Largest to Smallest → OK
Your expenses are now ordered from highest to lowest actual spending
-
To undo the sort and restore original order: press Ctrl+Z
Undo puts your rows back in the original order
-
With your data selected, go to Data tab → click Filter
Small dropdown arrows appear on each header cell
-
Click the dropdown arrow on column B ("Budgeted") → Number Filters → Greater Than → type 100 → OK
Only expenses over $100 are shown. The hidden rows are NOT deleted — just hidden temporarily.
-
Remove the filter: Data tab → click "Clear" (or click the dropdown → Clear Filter)
All your rows reappear — nothing was deleted
Create a Bar Chart
Charts turn numbers into visual pictures that make your budget easy to understand at a glance.
-
Select your data A1:B9 (Category and Budgeted columns) → Insert tab → Bar Chart → Clustered Bar
Excel creates a chart showing each category's budgeted amount as a horizontal bar
-
Click the chart title and rename it to "Monthly Budget Overview"
Click directly on the chart title text, select it, and type the new name
-
Resize the chart by dragging a corner handle — make it large enough to read easily
Click the chart border to select it, then drag one of the corner squares to resize
-
Try adding Actual data: right-click the chart → Select Data → Add → Series name: click C1, Series values: select C2:C9 → OK
Now your chart shows budgeted vs actual side-by-side for each category!
Tip: Keyboard shortcut: Select your data and press Alt+F1 to instantly create a chart on the same sheet!
Checkpoint — Raise your hand when done:
- ☐ You sorted data by Actual amount (then undid it)
- ☐ You filtered to show only expenses over $100 (then cleared it)
- ☐ A bar chart displays your budget data with a custom title
Goal: Replace sample data with your real spending, add categories, and apply conditional formatting.
Make It Your Own
Now it is time to turn this sample spreadsheet into your real personal budget.
-
Replace the sample amounts with your real monthly spending
Click each cell in the Budgeted and Actual columns and type your real numbers. The formulas update automatically!
-
Add more categories if needed: insert a row (right-click row number → Insert) and add your category
After adding rows, UPDATE your formulas! Change =SUM(B2:B9) to include the new rows (e.g., =SUM(B2:B11))
Important: When you add new rows, your formulas may not automatically include the new data. Always double-check that your SUM, AVERAGE, MAX, and MIN ranges cover all your expense rows.
Apply Conditional Formatting
Conditional formatting adds automatic color coding so you can see your budget status at a glance.
-
Select D2:D9 → Home tab → Conditional Formatting → Highlight Cells Rules → Less Than → type 0 → select "Green Fill with Dark Green Text" → OK
This highlights under-budget categories in green so you can see at a glance where you are saving money
-
Apply another rule: Conditional Formatting → Highlight Cells Rules → Greater Than → type 0 → select "Light Red Fill with Dark Red Text" → OK
Over-budget categories now show in red — a visual warning to watch your spending
Review and Save
-
Review your chart — it should have updated with your real data
Click on the chart to verify the bars reflect your actual spending amounts
-
Save your workbook: press Ctrl+S → name it "Budget_YourName.xlsx" → save to Desktop or Documents
Example: Budget_Smith.xlsx — use your own last name
Bonus Challenges (Done Early? Try These!)
- Add a "Savings Goal" row at the bottom: type your target monthly savings, then create a formula: =Income - Total Spending (this shows how much money is left over — positive means you have savings!)
- Create a Pie Chart of your actual spending to see budget breakdown percentages
- Add a second sheet tab (click "+" at the bottom) for next month's budget
Final Checkpoint — Review your work:
- ☐ Sample data replaced with your real spending amounts
- ☐ Conditional formatting shows green (under budget) and red (over budget)
- ☐ Formulas still calculate correctly with your real data
- ☐ Workbook saved as "Budget_YourName.xlsx"
Workshop Complete!
Outstanding work! You have built a complete personal budget tracker in Excel. Here is what you accomplished today:
- Created a budget spreadsheet with 8 categories and currency formatting
- Used SUM, AVERAGE, MAX, and MIN formulas to analyze spending
- Sorted and filtered data to find patterns in your expenses
- Built a bar chart comparing budgeted vs actual spending
- Applied conditional formatting for visual over/under-budget alerts
- Personalized the budget with your own real spending data
This Week's Challenge: Track your real spending every day this week. Update your budget spreadsheet daily. Bring your completed budget to Week 6 — we will save it to Google Drive so you can access it anywhere!