Percentage formulas in Excel and Google Sheets
Excel and Google Sheets store percentages as decimals: 20% is 0.2. To get a percentage, divide the part by the total with =A2/B2 and format the cell as Percentage. For a percentage change, use =(B2-A2)/A2 and format it the same way.
Format cells as percentages
In Excel, select the cells and choose Home › Number › % (or press Ctrl+Shift+%). In Google Sheets, choose Format › Number › Percent. Typing 20% straight into a cell stores 0.2, which is what the formulas below expect.
The formulas
| Task | Formula | Setup |
|---|---|---|
| Percent of total | =A2/B2 | Part in A2, total in B2 |
| Percent of a fixed total | =A2/$B$10 | Total in B10, copied down the column |
| Percentage change | =(B2-A2)/A2 | Old value in A2, new value in B2 |
| Percentage change with negative values | =(B2-A2)/ABS(A2) | Keeps the sign meaningful |
| Percentage of a number | =A2*B2 | Number in A2, percentage (like 20%) in B2 |
| Add a percentage | =A2*(1+B2) | Price in A2, increase in B2 |
| Subtract a percentage | =A2*(1-B2) | Price in A2, discount in B2 |
| Original value before an increase | =A2/(1+B2) | Final value in A2, percentage in B2 |
| Weighted average percentage | =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5) | Percentages in A, totals in B |
Check a result: compare your spreadsheet with the calculator.
% change from to ?
Common problems
- The result shows 2000% instead of 20%. You multiplied by 100 and also formatted the cell as %. Do one or the other.
- #DIV/0! The total or the old value is 0 or empty. Wrap the formula: =IFERROR(A2/B2, "").
- Formulas with semicolons. In languages that use a comma for decimals, functions separate arguments with semicolons: =SUMPRODUCT(A2:A5;B2:B5).
- Averaging a column of percentages. =AVERAGE is only right when every row has the same total; otherwise use the weighted formula above or the average percentage calculator.
How we calculate: exact decimal math (no rounding errors), you choose the decimal places, and your numbers never leave your browser. Methodology →
Updated October 2026