Percent Change and Share of Total
Your task: In D2:D5, find each line's % change from January; in E2:E5, its share of February's total.
Full instructions, scenario and goal
Scenario
You are preparing the monthly report for a coffee distributor. The sheet shows each product line's sales in January and February, with February's total already worked out in C6. Management wants to know how every line changed and how big each one is within February's sales.
Your task
- In D2:D5, calculate the percentage change in sales from January to February for each product line.
- In E2:E5, calculate each line's share of February's total sales (the total is in C6).
- Write the formula for the first row, then copy it down. Check that the total reference stays fixed.
- Select Check Answer when both columns are filled.
You are done when
- Each change is measured against January's figure.
- Each share divides by the February total, which does not move when the formula is copied.
- The formulas keep working if the figures change.
What you will practise
Work out a percentage change against the starting value, and a share of a total by dividing by a fixed total cell.
The idea in 30 seconds
Percentage change compares a new value with an old one: (new - old) / old. The difference says how far the value moved, and dividing by the old value turns that into a share of where it started. A rise gives a positive result and a fall gives a negative one. Divide by the old value, not the new one, or the percentages will not match what your reader expects.
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
Percentage change compares a new value with an old one: (new - old) / old. The difference says how far the value moved, and dividing by the old value turns that into a share of where it started. A rise gives a positive result and a fall gives a negative one. Divide by the old value, not the new one, or the percentages will not match what your reader expects.
A share of a total divides one part by the whole: part / total. The total is the same cell for every row, so when you copy the formula down it must keep pointing at that cell. A dollar sign locks a reference: $C$6 never moves, while C6 would slide down to C7, C8 and so on. Press F4 while typing a reference to add the dollar signs. Format the results as percentages to read them naturally.
Skills you will practise
- Percentages
- Percent change
- Share of total
- Absolute references
Complete first: Sale Price After a Percentage Discount
Common mistakes to avoid
- Dividing by the new value
- (C2-B2)/C2 gives a smaller percentage than the real change, because it measures against where the line ended up.
- Forgetting to lock the total
- Without $ signs, C6 becomes C7 in the next row and the formula divides by an empty cell.