How to Calculate Percentages in Excel and Google Sheets
Spreadsheets do not have a "percentage function" — and they do not need one. Every percentage calculation in Excel or Google Sheets is ordinary arithmetic combined with one formatting rule that trips up almost everyone at least once. This guide covers the formulas that matter, in the same syntax for both applications, and explains the formatting behaviour that makes a correct formula display the wrong number.
To find a percentage of a value, use =A2*B2 where B2 holds the rate as a decimal or a percent-formatted cell. For percentage change use =(B2-A2)/A2 and format the cell as Percent. Never multiply by 100 and apply percent formatting — that gives 2500% instead of 25%.
The One Thing to Understand First: Percent Formatting
In every spreadsheet application, a percentage is stored as a decimal. The cell value 0.25 is 25%. Applying the Percent number format does not change the stored value — it only multiplies the display by 100 and appends a percent sign.
This means the classic mistake is doubling up: writing =(B2-A2)/A2*100 and then formatting the result as Percent. The stored value becomes 25, which the format displays as 2500%. Pick one approach:
- Recommended:
=(B2-A2)/A2with Percent formatting (Ctrl+Shift+5 in Excel, Ctrl+Shift+5 in Sheets). - Alternative:
=(B2-A2)/A2*100with plain number formatting, adding the "%" only as a label.
Percentage of a Number
To find 25% of 200 with the rate in A2 and the value in B2: =A2*B2 when A2 is percent-formatted, or =25%*200 typed literally — spreadsheets accept the % sign as an operator meaning "divide by 100". Both return 50. The same result comes out of the Percentage Calculator if you want to sanity-check a sheet.
Percentage of a Total
To show each row as a share of the column total, anchor the total with absolute references so the formula can be filled down:
=B2/$B$10 — formatted as Percent.
The dollar signs lock the reference to B10 while B2 advances to B3, B4 and so on. In Google Sheets you can press F4 to cycle through reference types, exactly as in Excel. Missing the absolute reference is the second most common spreadsheet percentage bug; it produces a column of results that shrink for no visible reason.
Percentage Change Between Two Columns
With last month in A2 and this month in B2:
=(B2-A2)/A2
A positive result is an increase, a negative one a decrease. To guard against a zero baseline, wrap it: =IF(A2=0,"n/a",(B2-A2)/A2). Percentage change from zero is mathematically undefined, and this stops #DIV/0! from spreading through a report. The underlying logic is the same as the Percentage Increase Calculator and the Percentage Decrease Calculator.
Increasing and Decreasing a Value
| Goal | Formula | Example |
|---|---|---|
| Increase by a rate | =A2*(1+B2) | 450 at 12% → 504 |
| Decrease by a rate | =A2*(1-B2) | 240 at 15% → 204 |
| Value before an increase | =A2/(1+B2) | $120 incl. 20% → $100 |
| Value before a discount | =A2/(1-B2) | $64 after 20% off → $80 |
The last two rows are reverse percentages — division, never subtraction. Verify them in the Reverse Percentage Calculator.
VAT and Sales Tax in a Sheet
With the net amount in A2 and the rate in B2: gross is =A2*(1+B2), the tax component alone is =A2*B2, and to work backwards from a gross figure the net is =A2/(1+B2) with tax =A2-A2/(1+B2). Keep the rate in its own cell rather than hard-coding 1.2 — when a rate changes, one edit updates the whole model. Cross-check totals with the VAT Calculator.
Weighted Averages and Blended Percentages
Averaging a column of percentages is almost always wrong, because each row may represent a different base. Use a weighted calculation instead:
=SUMPRODUCT(B2:B20,C2:C20)/SUM(C2:C20)
where B holds the rates and C the weights (units, revenue, headcount). A blended margin across products, for example, must come from total profit divided by total revenue — not from the average of the individual margins.
Conditional Percentages
To calculate a share of a filtered subset, combine SUMIF with the total: =SUMIF(A2:A50,"North",B2:B50)/SUM(B2:B50) returns the North region's share of the overall figure, formatted as Percent. In Google Sheets, =SUMIF() behaves identically; only the locale separator may differ, with some regions using semicolons instead of commas.
Excel and Google Sheets: Where They Differ
| Feature | Excel | Google Sheets |
|---|---|---|
| Percent format shortcut | Ctrl+Shift+5 | Ctrl+Shift+5 |
| Absolute reference toggle | F4 | F4 |
| Argument separator | Comma or semicolon by locale | Comma or semicolon by locale |
| Error handling | IFERROR | IFERROR |
| Array behaviour | Dynamic arrays (365) | ARRAYFORMULA |
For everyday percentage work the two are interchangeable; formulas copied between them almost always run unchanged.
Troubleshooting Checklist
- Result is 100× too big. You multiplied by 100 and applied Percent formatting. Remove one.
#DIV/0!— the baseline is zero or blank. Wrap the formula in IF or IFERROR.- Fill-down gives drifting results. The total reference is relative; anchor it with
$. - Percentages do not sum to 100%. Usually rounding in display only; widen the decimals to confirm before adjusting anything.
- Text stored as numbers. A cell imported as text silently breaks arithmetic; use VALUE() or re-import with the correct type.
Once your sheet is set up, use the calculators here as an independent check on individual cells — a second method catching a single wrong formula is worth far more than re-reading the same spreadsheet twice. Start with The Percentage Formula if you want the underlying maths behind each of these.
Sources and method: formula behaviour verified against current Excel and Google Sheets documentation for calculating percentages in Excel and the Google Sheets function list. Worked examples cross-checked with this site's calculators. Written and reviewed by the Snap Percent Calc editorial team.
Frequently Asked Questions
Found this useful?
Share it with someone who should start investing today.