Percentages practice exercises
The percentage math that comes up in every office job and Excel test: change from last year, a markup, a discount, VAT added and taken out, and a profit margin.
How percentage formulas work in Excel
In Excel a percentage is just a decimal: 25% is 0.25. Format the cell as Percent to show it that way. Most percentage formulas are one of a few patterns. Change is (new - old) / old. A markup is cost * (1 + markup). A discount is price * (1 - discount). VAT is added by multiplying by 1 plus the rate.
=(C2-B2)/B2 with sales of 400 last year and 500 this year returns 0.25, which shows as 25% once the cell is formatted as Percent. =B2*(1-C2) takes a 15% discount off a price of 80 and returns 68.
The common mistake is taking VAT out the wrong way. A price with 25% VAT is the net price times 1.25, so you remove VAT by dividing: =B2/(1+$F$1) turns 125 into 100. Subtracting 25% from 125 gives 93.75, which is wrong. Also keep markup and margin apart: markup is profit divided by cost, margin is profit divided by price. The exercises below cover percent change, markup, discount, VAT added and removed, the VAT amount and profit margin.
- 1Percent change from last yearIn D2, work out the change in coffee sales from 2023 to 2024 as a decimal: the difference divided by the 2023 figure, (C2-B2)/B2.
- 2Add a markup to a cost pricePremiumIn D2, work out the selling price of the mug: the cost in B2 plus the markup in C2, which is a percentage of the cost. Use B2*(1+C2).
- 3Sale price after a discountPremiumIn D2, work out the sale price of the jacket: the price in B2 less the discount in C2, B2*(1-C2).
- 4Find the price before VATPremiumThe prices in column B include 25% VAT. In C2, work out the printer's price before VAT: divide by 1 plus the rate in F1, B2/(1+$F$1).
- 5Add VAT to a pricePremiumThe prices in column B are before VAT. In C2, work out the printer's price with 25% VAT: multiply by 1 plus the rate in F1, B2*(1+$F$1).
- 6Find the VAT in a price that includes itPremiumThe prices in column B include 25% VAT. In C2, work out how much of the printer's price is VAT: the price minus the price before VAT, B2-B2/(1+$F$1).
- 7Profit margin from price and costPremiumIn D2, work out the profit margin of the mug: the profit, B2-C2, as a share of the price in B2. Use (B2-C2)/B2.
Read the guide
Questions
How do I calculate a percentage of a number in Excel?
Multiply the number by the percentage: =B2*15% or =B2*0.15 gives 15% of the value in B2. If the percentage sits in its own cell, say C2, use =B2*C2. To get a percentage on top of the number, as with a price increase, multiply by one plus the percentage instead.
How do I calculate each row's percent of the total?
Divide each value by the total and lock the total with dollar signs: =B2/SUM($B$2:$B$10), then fill down and format the column as Percent. The locked range stays put while B2 moves. If you don't lock it, the range slips down and the percentages turn wrong.
Why does Excel show 0.25 instead of 25%?
The cell is formatted as a number, not as a percentage. Select it and click the % button on the Home tab, or press Ctrl+Shift+%. The value stays 0.25, and only the display changes. Typing 25 into a cell that is already formatted as Percent gives you 25%.