Excel percentage formula generator.
Choose what you want to calculate and where your numbers are. You get the formula with the right dollar signs, how to fill it down, the cell format to use, and a preview of what it returns.
Most of Europe and Latin America use a semicolon between arguments; if Excel rejects a formula with commas, switch the separator.
—Cell format:
—The Excel percentage formula for a share of a total is =B2/SUM($B$2:$B$21), and for percent change it is =(C2-B2)/B2, with the cell formatted as a percentage. Pick a task above to get the formula for your own columns and rows, in Excel or Google Sheets.
See also: How to calculate percentage in Excel · How to calculate percentage in Google Sheets · Spreadsheet formula cheat sheet
How the generator builds the formula
Every percentage formula in a spreadsheet is a division: a part divided by a whole. What changes from task to task is which cells are the part, which are the whole, and which references must stay fixed when you fill the formula down. The generator writes that out for your columns and rows, then runs the formula on sample numbers so you can see what each row returns before you paste it.
Why some references have dollar signs
When you fill a formula down, Excel and Google Sheets shift every reference by one row: B2 becomes B3, then B4. That is right for the value in each row, but wrong for the total, which should stay put. A dollar sign locks the column or row that follows it, so $B$2:$B$21 never moves. Forget it and the second row divides by SUM(B3:B22), the third by SUM(B4:B23), and every share after the first comes out wrong.
The formulas, task by task
- Percent of total:
=B2/SUM($B$2:$B$21) - Percent change:
=(C2-B2)/B2, new minus old, divided by old - X% of a value:
=B2*C2when C holds 15%, or=B2*C2/100when it holds 15 - Increase by a percent:
=B2*(1+C2); a negative percent gives a decrease - Running percent of total:
=SUM($B$2:B2)/SUM($B$2:$B$21), where only the end of the first range moves - Weighted average:
=SUMPRODUCT(B2:B21,C2:C21)/SUM(C2:C21)
Formatting the result as a percentage
A formula like =B2/SUM($B$2:$B$21) returns a decimal such as 0.25. Format the cell as a percentage to show 25%; don’t multiply by 100 as well, or the cell will show 2,500%. In Excel use Home → Number → Percent Style (Ctrl+Shift+%). In Google Sheets use Format → Number → Percent.
Excel vs Google Sheets
The fill-down formulas are the same in both apps. The difference is the one-formula version for a whole column. Excel 365 and Excel 2021 spill a range formula such as =B2:B21/SUM(B2:B21) down the column on their own. Google Sheets needs it wrapped as =ARRAYFORMULA(B2:B21/SUM(B2:B21)). Older Excel versions don’t spill, so use the fill-down formula there.
Commas or semicolons?
The character between function arguments follows your system’s regional settings. Where the comma is the decimal mark, as in Germany, France, Spain or Brazil, Excel and Sheets use a semicolon instead: =SUMPRODUCT(B2:B21;C2:C21). If a pasted formula is rejected, switch the separator above.
Sources
About this tool.
A percentage is a part divided by a whole, so the formula is =part/whole, formatted as a percentage. For a share of a total it is =B2/SUM($B$2:$B$21); for percent change it is =(C2-B2)/B2.
The formula already multiplied by 100 and the cell is also formatted as a percentage, which multiplies by 100 again. Remove the *100 from the formula and keep the percentage format.
Make the total an absolute reference with dollar signs: =B2/SUM($B$2:$B$21). The $ before the column letter and the row number stops either from shifting.
The fill-down formula is the same. Only the one-formula-per-column version differs: Google Sheets needs ARRAYFORMULA, while Excel 365 spills the range formula on its own.
Your regional settings may use a semicolon between arguments instead of a comma. Switch the separator to semicolon and copy the formula again.