Excel for the Web Tutorial: Your First Budget Spreadsheet
Updated · By Robert Breen
The problem
Tom 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 totals on paper and typing them into an email, and he usually gets one sum wrong.
What you’ll be able to do
In about 15 minutes you will have a budget-vs-actual sheet that totals itself, shows the difference for every line, turns overspending red automatically and can be shared as one link, in the free Excel for the web.
Before you start
You need a free Microsoft account and a browser. Go to microsoft365.com, sign in and pick Excel, or create the file from OneDrive. Excel for the web saves as you work, so there is no Save button to forget. Microsoft’s Basic tasks in Excel for the web page has the official version of the first steps.
The data
| Category | Budget | Actual |
|---|---|---|
| Rent | 2,400 | 2,400 |
| Ingredients | 3,100 | 3,460 |
| Wages | 9,800 | 9,650 |
| Utilities | 650 | 712 |
| Marketing | 500 | 380 |
Steps
- Create a Blank workbook. Click the file name at the top and rename it "Bakery budget, October".
- In A1 to C1 type the headers Category, Budget and Actual, then type the five rows above underneath. Type numbers without the comma or a currency sign; you will format them in a moment.
- Click any cell in the data and choose Home > Format as Table. Pick a style, make sure My table has headers is ticked and click OK. Your range is now an Excel table with filter arrows on each header.
- In D1 type Difference. The table grows to include it. In D2 type =C2-B2 and press Enter. Excel fills the formula down the whole column for you (a calculated column).
- With a cell in the table selected, open the Table Design tab (it appears only while you are in the table) and tick Total Row. A total appears under the last column; use the drop-down in each total cell to choose Sum for Budget and Actual.
- Select the Difference cells (D2 to D6). Choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, type 0 and pick a red fill. Any line that went over budget turns red.
- Select B2 to D7 and apply the Currency or Number format from the Home tab.
- Click Share at the top right, add the bookkeeper’s email and choose whether they can edit or only view.
Check your work
Budget should total $16,450 and Actual $16,602, a difference of $152 over. Ingredients (+$360) and Utilities (+$62) should be red; Wages (−$150) and Marketing (−$120) should not. If the Difference column shows the formula as text, the cell was formatted as text before you typed; change it to General and type the formula again.
No table? You can still total a column the quick way: click the empty cell under the numbers and choose Home > AutoSum. AutoSum guesses the range and writes the SUM formula for you.
Why this beats the calculator
Next month Tom only types new Actual numbers. Every formula, total and red flag updates by itself, and the bookkeeper opens the same link instead of waiting for an email. When he wants to pull prices from a supplier list, XLOOKUP is the next function to learn.
Check your understanding
You type =C2-B2 in the first row of a table column. What happens?
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 automatically.
With the sample data, what is the total over budget?
Show the answer
$152. Actual $16,602 minus budget $16,450 is $152. Two lines went over ($360 + $62) and two came in under ($150 + $120).
Which feature turns overspent lines red without you checking them?
Show the answer
Conditional formatting. A conditional formatting rule (Difference greater than 0) colors the cell whenever its value meets the rule.
Sources
Written from these official help pages. Menus change, so if a button has moved, the help page has the current wording.
- Microsoft Support: Basic tasks in Excel for the web
- Microsoft Support: Format an Excel table
- Microsoft Support: Use calculated columns in an Excel table
- Microsoft Support: Ways to add values in a spreadsheet (AutoSum)
- Microsoft Support: Use conditional formatting to highlight information in Excel
Terms in this lesson
Related in the AI glossary
Do this with AI
Have a bigger spreadsheet and want a first read on it? The free Stepthrough lesson on analyzing a spreadsheet with ChatGPT shows how to ask AI to summarize it, and how to check what it says.
Open the free AI lesson