Invoice Total with Cell References
Your task: In D2:D4, D5, D7 and D8, work out the line totals, subtotal, tax and total due.
Full instructions, scenario and goal
Scenario
You run a small stationery shop and are preparing an invoice for an office customer. The quantities, unit prices and tax rate are already in the sheet. Your job is to let Excel do the arithmetic, so the invoice stays right if the customer changes the order.
Your task
- In D2:D4, work out each line total: the quantity multiplied by the unit price.
- In D5, add the three line totals to get the subtotal.
- In D7, work out the tax: the subtotal multiplied by the tax rate in D6.
- In D8, work out the total due: the subtotal plus the tax.
- Use cell references in every formula, then select Check Answer.
You are done when
- Every answer cell contains a formula that points at other cells, not typed amounts.
- The totals are right now and stay right if a quantity, price or the tax rate changes.
What you will practise
Build a formula that starts with =, uses cell references and the + and * operators, and recalculates when the numbers it points to change.
The idea in 30 seconds
Every Excel formula starts with an equals sign. After it you can use numbers, operators (+ for add, - for subtract, * for multiply, / for divide) and cell references such as B2, which means "whatever is in column B, row 2". A formula that uses references is alive: change the number in B2 and every formula that points at B2 updates by itself. A formula with typed numbers is just a calculator printout.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell D2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Every Excel formula starts with an equals sign. After it you can use numbers, operators (+ for add, - for subtract, * for multiply, / for divide) and cell references such as B2, which means "whatever is in column B, row 2". A formula that uses references is alive: change the number in B2 and every formula that points at B2 updates by itself. A formula with typed numbers is just a calculator printout.
Excel follows the usual order of operations: multiplication and division before addition and subtraction, and anything in brackets first. So B2+C2*2 multiplies C2 by 2 first. Formulas can also point at other formulas: a total can be built from line totals, and when a line changes the total changes too. This chain of small, readable steps is how real invoices and budgets are built.
Skills you will practise
- Cell references
- Arithmetic
- Operators
Common mistakes to avoid
- Typing the numbers into the formula
- =12*3.5 gives the right answer today and the wrong one when the quantity changes. Point at B2 and C2 instead.
- Adding the tax rate instead of the tax
- D6 is a rate (20%), not an amount. The tax amount is the subtotal multiplied by that rate.