- Work out a percentage of a total
- The three percentage formulas you'll use most
- The catch: don't multiply by 100 and then format as Percent
- TO_PERCENT and its relatives
- Working with percentages you've typed in
- Avoiding errors on empty rows
- Showing more or fewer decimal places
- My take
- Frequently asked questions
To calculate a percentage in Google Sheets, divide the part by the total, for example =A2/B2, then click Format, Number, Percent to show the result as a percentage. Sheets treats 1 as 100%, so you don't multiply by 100 yourself. For a change between two numbers, use =(B2-A2)/A2 and format it the same way.
Most percentage questions in a small business come down to the same three sums: what share of the total is this, how much did something go up or down, and what's this number as a percentage of a target. Google Sheets handles all three with one division and one formatting click. The trick is knowing that Sheets already thinks in percentages, so the common habit of multiplying by 100 gets you into trouble.
Work out a percentage of a total
- Put the part in one column (say completed tasks in A2) and the total in another (all tasks in B2).
- In an empty cell, type =A2/B2 and press Enter. You'll see a decimal.
- With that cell selected, click Format, then Number, then Percent.
- Drag the fill handle down to copy the formula to the rest of the rows.
Google's help page for TO_PERCENT spells out why step 3 works: Sheets uses the standard interpretation that 1 equals 100%. So a result of 1 shows as 100%, a half shows as half of that, and so on. The format does the multiplying for you.
The three percentage formulas you'll use most
| Question | Formula | Then |
|---|---|---|
| What share of the total is this? | =A2/B2 | Format as Percent |
| How much did it change? | =(B2-A2)/A2 | Format as Percent |
| How close am I to target? | =A2/C2 | Format as Percent |
| Convert a decimal with a formula | =TO_PERCENT(A2) | Already shows as a percentage |
For percentage change, put the old number in A2 and the new number in B2. A positive result is growth and a negative result is a drop. Add brackets exactly as shown, or Sheets divides before it subtracts and the answer comes out wrong.
The catch: don't multiply by 100 and then format as Percent
This is the mistake I see most. People write =A2/B2*100, get a whole number, then click Percent and suddenly the cell shows a result 100 times too big. Because Sheets already treats 1 as 100%, multiplying by 100 and then applying the Percent format counts the hundred twice.
Pick one approach. Either divide and use the Percent format (my preference, because the cell still holds the true value for other formulas), or multiply by 100 and leave it as a plain number with a label in the header saying it's a percentage. Never both.
TO_PERCENT and its relatives
- TO_PERCENT(value) converts a number to a percentage. Google says it's equivalent to clicking Format, Number, Percent. If the cell isn't a number, it returns the value unchanged.
- UNARY_PERCENT(value) reads a number as a percentage. Google's example is that UNARY_PERCENT(100) equals 1.
- TO_PURE_NUMBER(value) strips the formatting off, which is handy when you need the plain decimal for another calculation.
One odd detail from Google's help page: because dates and percentages are both numbers underneath, TO_PERCENT will happily turn a date into a percentage. It won't complain, but the result means nothing, so check you've pointed it at the right column.
Working with percentages you've typed in
If you type a value with a percent sign straight into a cell, Sheets stores it as a percentage, so typing a half as a percentage stores the same decimal a division would. That means typed percentages and calculated ones mix happily in the same formulas. Where it goes wrong is when someone types the plain number without the sign and expects Sheets to guess. If a column of rates looks a hundred times too big after formatting, that's usually why: retype them with the sign, or divide the column by 100 before applying the format.
Avoiding errors on empty rows
Percentage formulas are divisions, and Google's DIVIDE page notes that the divisor can't equal 0. If your total column has blanks, those rows show an error. Wrap the formula like this: =IFERROR(A2/B2, ""). The row stays blank until the total arrives, which looks far tidier on a shared sheet.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Showing more or fewer decimal places
Once a cell is formatted as Percent, use the increase and decrease decimal buttons in the toolbar to show more or less detail. For a report I'd usually show none or one decimal place: nobody needs to know a conversion moved by a hundredth of a percent. If you want exact control, Format, Number, Custom number format lets you build your own pattern, and Google lists the % character as the symbol that formats numbers as a percent.
My take
Divide the part by the total and use the Percent format. Don't multiply by 100 as well. For change over time, brackets first: (new minus old) divided by old. That covers most of what business owners need from percentages in Sheets. To highlight the rows that hit or miss a target, conditional formatting in Google Sheets is the next step, and adding up a column gives you the totals to divide by.
Frequently asked questions
What is the formula for percentage in Google Sheets?
Divide the part by the total, such as =A2/B2, then click Format, Number, Percent to display it as a percentage.
How do I calculate percentage change in Google Sheets?
Use =(B2-A2)/A2 with the old number in A2 and the new one in B2, then format the cell as Percent.
Why is my percentage 100 times too big?
You multiplied by 100 and then applied the Percent format. Sheets treats 1 as 100%, so use one or the other, not both.
What does TO_PERCENT do in Google Sheets?
It converts a number to a percentage, the same as clicking Format, Number, Percent. Non-numbers are returned unchanged.
How do I stop errors in percentage formulas?
Wrap the formula in IFERROR, for example =IFERROR(A2/B2, ""), so rows with a blank or zero total stay empty.
