Commission with One Rate Cell
Your task: In C4:C8, work out each salesperson's commission from the rate in B1, and the total in C10.
Full instructions, scenario and goal
Scenario
A regional sales manager pays every salesperson the same commission rate, which sits in one cell at the top of the sheet. Next quarter the rate may change, so the commissions must be calculated from that cell and not from a typed percentage.
Your task
- In C4, calculate the commission for the first salesperson: their sales multiplied by the rate in B1.
- Copy the formula down to C8. Make sure it still points at the rate cell in every row.
- In C10, calculate the total commission to be paid.
- Select Check Answer when you are done.
You are done when
- Every commission uses the rate cell, which does not move when the formula is copied.
- The total is a formula that follows the commissions.
- Changing the rate in B1 would update every result.
What you will practise
Lock a reference with dollar signs so that a formula can be copied down a column while still pointing at one fixed input cell.
The idea in 30 seconds
By default Excel references are relative: when you copy a formula down one row, every reference in it moves down one row too. That is exactly what you want for the sales figure in each row. It is exactly what you do not want for a single input such as a commission rate, because the reference would slide down onto the next sales figure.
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 C4. 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
By default Excel references are relative: when you copy a formula down one row, every reference in it moves down one row too. That is exactly what you want for the sales figure in each row. It is exactly what you do not want for a single input such as a commission rate, because the reference would slide down onto the next sales figure.
An absolute reference has dollar signs: $B$1 always means B1, wherever the formula is copied. You can type the dollar signs, or press F4 while the cursor is on the reference to add them. A mixed reference such as B$1 locks only the row. Keeping inputs like rates, tax percentages and targets in their own cells, and pointing at them with absolute references, lets you change one cell and update the whole sheet.
Skills you will practise
- Absolute references
- Relative references
- F4
Complete first: Invoice Total with Cell References
Common mistakes to avoid
- Leaving the rate reference relative
- Pointing at plain B1 works in the first row, but one row down the copy points at B2 and multiplies by a sales figure.
- Typing the percentage into the formula
- A typed 0.04 works today and silently breaks when the rate in B1 changes.