Why this matters
Tom (made up) owns a small bakery. Each month his bookkeeper asks, "Where did we go over budget?" Tom answers by adding up receipts on a calculator, writing the totals on paper and typing them into an email, and he usually gets one sum wrong.
In this module you build his budget in Excel for the web instead: a table that totals itself, a Difference column for every line, overspending that turns red on its own, and one link the bookkeeper can open.
What you’ll build
A workbook called "Bakery budget, October": five budget lines formatted as an Excel table, a Difference column (=C2-B2) that fills itself down, a Total Row, a red highlight on every line that went over budget and Currency formatting.
With the sample numbers, Budget totals $16,450 and Actual $16,602: $152 over. Ingredients (+$360) and Utilities (+$62) turn red.
What you need
- Nothing to start the practiceThe practice is built from real Excel for the web screens and does not touch any account.
- A free Microsoft account, to do it for realSign in at microsoft365.com and open Excel. Excel for the web saves as you work.
Concepts
3 ideas you’ll use in the practice.
Format as Table does the bookkeeping for you
Home › Format As Table turns a plain range into an Excel table with filter arrows on each header. Add a column next to it and the table grows to include it.
Type a formula in the first row of a table column and Excel fills it down the whole column for you. That is a calculated column.
A Total Row instead of a calculator
With a cell in the table selected, the Table Design tab appears. Tick Total Row, then use the drop-down in each total cell to choose Sum.
Next month Tom only types the new Actual numbers. Every total and difference updates by itself.
Conditional formatting flags the problem
Home › Conditional Formatting › Highlight Cell Rules › Greater Than opens a panel on the right in Excel for the web. Type 0, keep the light red style and click Done.
Any line where Actual is higher than Budget now turns red without anyone checking it by hand.
Practice
Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.
Do it for real
Now repeat it in your own account. Tick each step as you go.
Quick check
3 questions to check you’ve got it.
1. You type =C2-B2 in the first row of a table column. What happens?
- Only D2 gets the formula
- Excel fills it down the whole column
- Excel shows an error
Show the answer
Excel fills it down the whole column. Inside an Excel table, a formula typed in a column becomes a calculated column and fills down by itself.
2. With the sample numbers, how much is the bakery over budget in total?
- $152
- $360
- $422
Show the answer
$152. Actual $16,602 minus Budget $16,450 is $152.
3. Which feature turns overspent lines red without you checking them?
- AutoSum
- Conditional formatting
- Total Row
Show the answer
Conditional formatting. A conditional formatting rule (Difference greater than 0) colours the cell whenever its value meets the rule.
Excel for the Web: Find Your Top Customers in a Sales Export
Next, the same Excel for the web answers a sales question: your top customers from a raw export, with a PivotTable.
Start module 2 →