Divide the part by the whole, =B2/C2, and click Percent Style: Excel shows 0.25 as 25%. Percentage change is the difference over the old figure, =(C2-B2)/B2, and a 20% increase is =B2*(1+20%).
D3 = -5% The difference, divided by where it started. North grew 20%, and South’s fall shows as a negative percentage.
Most percentage jobs at work come down to three formulas: a share of a total, the change from one figure to another, and a number made bigger or smaller by a percentage. Each one is a single line. What catches people out is that Excel stores a percentage as an ordinary number, 25% as 0.25, and only the cell’s format shows it with a % sign.
First, work out each region’s change since last year, and a target for next year, in the sales table below. Then each formula gets a small sheet of its own, where you can swap in the common mistakes and see what they do.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
A share is the part divided by the whole. =B2/B6 gives one product’s share of the total
in B6, as a fraction, and Percent Style shows it as a percentage. To fill the column with one formula,
lock the total with dollar signs, $B$6, so every copy divides by the same cell.
C5 = 16% The $ signs keep every copy on B6, so each product is divided by the same total, and the four shares add up to 100%.
Put the cursor on the reference and press F4 (Fn + F4 on most Mac keyboards) to add the dollar signs
instead of typing them. Excel formula practice shows what each
kind of locked reference does when it’s copied. Microsoft 365 also has a function for this, =PERCENTOF(B2, B2:B5), which adds up the whole for you. It shows #NAME? in
older versions, and Witsheet doesn’t have it yet.
The change from an old figure to a new one is the difference divided by the old figure: =(C2-B2)/B2. The brackets matter, because Excel divides before it subtracts. A rise comes
out positive and a fall negative, so one formula serves for an increase and a decrease.
D3 = -5% The brackets make Excel subtract before it divides. A fall comes out negative, so South reads -5%.
=C2/B2-1 is the same calculation written another way, and gives the same answer on every
row. Use whichever you find easier to read. Both say how much bigger the new figure is than the old,
which is why the old one always goes underneath.
“Of” means multiply. 30% of 450 is =450*30%, and with the price in A2, =A2*30%. Excel reads the % sign as “divide by 100”, so 30% and 0.3 are the same number to it.
C2 = 135 The % sign divides by 100, so this is 450 times 0.3: 30% of 450.
A rate that more than one formula uses belongs in a cell of its own. Change it there, and every formula
that reads it changes too. Copied down a column, the reference needs the dollar signs a total does: =A2*$B$2.
To add 10% to a price, multiply it by 1 plus 10%: =B2*(1+10%). To take 10% off, multiply by
1 minus it: =B2*(1-10%). Multiplying by the percentage alone gives only the increase, the
mistake to watch for.
C3 = 132 The brackets make 1+10% into 1.1 first, so every price grows by a tenth: the lamp goes from 120 to 132.
To raise a whole column of prices where they are, without a second column, type 1.1 in an empty cell and copy it. Select the prices, open Paste Special (Ctrl+Alt+V, or ⌘+Control+V on a Mac), pick Multiply and click OK. Every price grows by 10% and the 1.1 can go, though nothing on the sheet shows what was done.
Going back is a division. A price after 25% off is 75% of the full price, so the full price is =B2/(1-25%). Adding 25% back on gives the wrong answer, because the 25% you add is taken
from the smaller sale price.
C2 = 100 A sale price is 75% of the full price, so dividing by 0.75 undoes it: the jacket was 100.
It works the same for anything added as a percentage. A price that includes 8% sales tax was =B2/(1+8%) before the tax, and an amount after a 5% raise was =B2/(1+5%) before
it.
Both are the profit as a percentage, and they differ in what they divide by. Margin is the profit over the
price, =(C2-B2)/C2. Markup is the profit over the cost, =(C2-B2)/B2. Ask which
one a report wants before you write it, because the same mug has a 25% margin and a 33% markup.
D2 = 25% Margin: the 20 profit on a mug over its 80 price, so a quarter of every sale is profit.
To set a price that gives a target margin, divide the cost by what’s left: a 30% margin on a cost in B2
is =B2/(1-30%). A 30% markup is =B2*(1+30%), which is a lower price.
To find what share of a list meets a condition, count the rows that do and divide by the rows there
are. COUNTIF counts the matches and COUNTA counts every filled cell: =COUNTIF(B2:B9, "Yes")/COUNTA(B2:B9).
C2 = 75% Six orders of eight said Yes. COUNTA counts every filled cell, so a new order counts on both sides of the division.
Percent Style multiplies by 100 for display and adds the % sign. A formula that multiplies by 100 as well, the way you would on a calculator, gets multiplied twice.
D2 = 8% The formula gives 0.08 and Percent Style shows it as 8%. Leave the 100 to the format.
The same thing happens when you format numbers that are already written as percentages. A column of
20, 35 and 50, formatted as a percentage, shows 2000%, 3500% and 5000%, because 20 is twenty, not
twenty per cent. Divide them by 100 first, with =A2/100 or =A2% in a column
beside them, or in place with Paste Special and Divide, the way the prices were multiplied above.
Percent Style is on the Home tab, in the Number group, and Ctrl+Shift+% applies it in Excel for Windows. It shows whole percentages, so 0.375 reads 38%; Increase Decimal shows 37.5%, and the cell holds 0.375 throughout. You can also type a percentage straight into a cell: 20% goes in as 0.2, already formatted. The formulas on this page work unchanged in Google Sheets, where the % button is on the toolbar.
A change from zero has no percentage: no percentage of nothing makes 40. Excel shows #DIV/0!, and IFERROR can show something more useful in its place.
D3 = #DIV/0! Rugs sold nothing last year, and no percentage of zero makes 40, so Excel can’t divide. Mats, at zero both years, fail the same way.
From a negative figure, the formula’s sign comes out backwards: a loss of 50 shrinking to a loss of 20 is
an improvement, but =(C2-B2)/B2 calls it -60%. The usual fix divides by the size of the old
figure, whatever its sign: =(C2-B2)/ABS(B2) gives 60%. Witsheet doesn’t have ABS yet, so try that one in your own Excel. Whether a percentage means anything across zero
is a judgement call, and many reports show the change in amounts instead.
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches percentages: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →