Percentages: discounts, shares and change
9 minutes · 5 steps · free, no sign-up
Percentages come up in almost every real sheet: a discount, a tax rate, a share of the budget, how much a price has gone up. They are just ordinary numbers with a special display. You will price a sale, work out what share of the total each item is, and calculate a percentage change.
- Apply a discount with a percentage in a cell
- Work out a share of a total
- Calculate a percentage change
- Format results as percentages
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceThree patterns to remember
=B2*(1-$F$1) price after a discount
=B2/$B$6 share of a total
=(new-old)/old percentage changeThe key ideasSummary: Percentages: discounts, shares and change
A percentage is a number between 0 and 1 shown as a share of 100, so 20% is 0.2. Keep the rate in one cell, lock it with dollar signs, and every formula that needs it points at that cell. Change the rate and the whole sheet updates.
Three patterns cover most needs. Discount: multiply by one minus the rate. Share of a total: divide the part by the total. Change: take the new value minus the old value, then divide by the old one. Format the answer as a percentage afterwards.
Watch out forCommon mistakes
- Dividing by the new value for change. Percentage change is always measured against the old value:
=(B9-B8)/B8. - Not locking the total. If you write
=B2/B6and fill it down, the total reference slides and you get nonsense. Use=B2/$B$6. - Forgetting to format.
0.5is correct but unfriendly. Click the % button to show it as 50%. - Adding a percentage as a plain number. To add 20% tax multiply by
1.2, or by(1+rate), rather than adding 20.
Next stepTry it in the real Google Sheets
In Google Sheets you can type a percentage straight in: typing 20% stores 0.2 and formats the cell as a percentage for you. To work out a percentage of a number, multiply: =B2*20% works exactly as you would hope.