How to Use Percentages in Excel and Google Sheets
Spreadsheets are where percentage calculations get applied at scale — across hundreds of rows of financial data, survey results, or sales figures. Excel and Google Sheets handle percentage arithmetic reliably, but their behavior with percentage formatting can cause confusion: entering 20% into a cell formatted as a number stores it as 0.2, which changes how formulas work. Understanding how spreadsheets handle percentages internally, combined with the right formulas, lets you build reliable percentage calculations that work correctly across your entire dataset. This guide covers the essential patterns.
How Spreadsheets Store and Display Percentages
The most important thing to understand about percentages in Excel and Google Sheets: the cell format (how a number is displayed) is separate from the cell value (what is actually stored). When you format a cell as 'Percentage', the application displays the stored number multiplied by 100 with a % symbol. So a cell containing the value 0.25 formatted as Percentage displays as '25%'. A cell formatted as 'Percentage' that you type '25' into actually stores 25 — and will display as '2500%'. To enter 25%, type 0.25 or type 25% (with the percent symbol), which the application converts to 0.25. This matters for formulas. When you write =A1*20%, the spreadsheet interprets 20% as 0.20, so the formula correctly multiplies A1 by 0.20. When you reference a cell formatted as percentage, the underlying value (0.25, not 25) is used in calculations. These two conventions are consistent — both treat percentages as decimals internally. Common mistake: entering a percentage value as 25 (not 0.25 or 25%) in a cell used in a formula. If cell B1 contains 25 and you write =A1*B1/100 to calculate 25% of A1, the formula works. But if you write =A1*B1 expecting it to work like a percentage, it multiplies by 25, not 0.25. Decide on one convention (decimal or integer with /100) and stick to it throughout your spreadsheet. To apply percentage formatting: select cells → Format → Number → Percentage (Excel) or Format → Number → Percent (Google Sheets). Set decimal places to suit your data — 0 decimal places for whole-number percentages, 1 or 2 for precise values.
Essential Percentage Formulas in Excel and Google Sheets
These formulas cover the most common percentage calculations in spreadsheets. Percent of a number: =(percentage/100)*number or =percentage*number (if percentage is stored as a decimal) Example: to find 15% of the value in A2: =0.15*A2 or =15%*A2. If the percentage is in cell B2 formatted as percentage (decimal): =B2*A2. If B2 stores 15 (not 0.15): =B2/100*A2. What percent is part of whole: =part/whole or =part/whole*100 (if you want an integer result to display without the percentage format) Example: =B2/A2 (format the result cell as Percentage) or =B2/A2*100 (format as Number). Percent change between old and new values: =(new-old)/old or =(new-old)/ABS(old) (using ABS avoids sign issues when the old value is negative) Example: =(B2-A2)/A2 formatted as Percentage. To express as a signed whole number: =(B2-A2)/A2*100. Applying a percentage increase: =value*(1+percentage) where percentage is a decimal Example: to increase A2 by 8%: =A2*1.08 or =A2*(1+0.08). Applying a percentage decrease: =value*(1-percentage) Example: to decrease A2 by 15%: =A2*0.85 or =A2*(1-0.15). Reverse percentage (finding original from discounted): =final/(1-discount_rate) Example: if B2 is the sale price and the discount was 20%: =B2/0.80 or =B2/(1-0.20).
SUMIF, COUNTIF, and Percentage of Total in Spreadsheets
Calculating what percentage of a total any subset represents is a core analytics task in spreadsheets. Basic percentage of total (column sum): enter =SUM(B2:B100) in a total cell (say B101), then in C2 enter =B2/$B$101. The $ signs lock the reference to the total cell so it does not shift when you copy the formula down. Format column C as Percentage. Percentage contribution using SUMIF: if column A contains categories and column B contains values, to find what percentage of the total each category contributes: In cell D2: =SUMIF($A$2:$A$100, A2, $B$2:$B$100)/SUM($B$2:$B$100) Format D2 as Percentage. This sums all values where the category matches A2, then divides by the grand total. Counting items as a percentage: =COUNTIF(A2:A100, criteria)/COUNTA(A2:A100) Example: to find what percentage of responses in column A are 'Yes': =COUNTIF(A2:A100,'Yes')/COUNTA(A2:A100). Format as Percentage. Running percentage: to build a running total as a percentage of the grand total in column C: in C2 enter =SUM($B$2:B2)/SUM($B$2:$B$100). Copy down column C. The first $ anchors the start of the running range while the second reference shifts with each row, building a cumulative sum. Divide by the fixed total. PivotTable percentages: Excel and Google Sheets PivotTables have built-in '% of Column Total', '% of Row Total', and '% of Grand Total' value field settings. Right-click a value field → 'Show Values As' → select the percentage type. This avoids writing formulas manually for standard percentage breakdowns.
Common Spreadsheet Percentage Mistakes and Fixes
These are the errors that cause percentage formulas to produce wrong results in spreadsheets, along with how to diagnose and fix each one. Mixing decimal and integer percentage inputs: if some cells store 0.15 (decimal) and others store 15 (integer) as 'percentage', formulas applying these values will give inconsistent results. Audit column B with =MAX(B:B) — if the maximum is around 1 or less, values are decimal; if it is around 100, they are integers. Standardize to one format. Division by zero when old value is zero: percent change formulas fail with a #DIV/0! error when the denominator (old value) is zero. Wrap with IFERROR: =IFERROR((new-old)/old, 'N/A'). Or use IF: =IF(old=0, 'N/A', (new-old)/old*100). Hardcoded percentage not updating: if a formula uses =A2*0.20 and the tax rate changes, every formula needs updating. Instead, store the rate in a named cell (e.g., TaxRate) and reference it: =A2*TaxRate or =A2*$C$1. Changing $C$1 updates all formulas instantly. Rounding errors in percentage totals: percentage columns that should sum to 100% often produce 99.99% or 100.01% due to floating-point rounding. Use =ROUND(value/total*100, 2) to round each percentage to 2 decimal places. Alternatively, calculate all percentages and set the last one to =100-SUM(rest) to force the total to exactly 100. Percent change in percentage-formatted cells: if you calculate percent change between two cells already formatted as percentage, the formula returns a ratio of ratios. A cell showing 20% and one showing 25% will give a percent change of (0.25-0.20)/0.20 = 25% (relative change). If you want the percentage point change, subtract: =B2-A2 formatted as Percentage gives 5 pp.
Frequently Asked Questions
- Why does Excel show 2500% when I type 25 in a percentage-formatted cell?
- When a cell is formatted as Percentage, Excel multiplies the stored value by 100 for display. If you type 25, Excel stores 25 and displays 2500%. To enter 25%, type '25%' (with the percent sign) or type '0.25'. Excel will then store 0.25 and display it as 25% in a Percentage-formatted cell. This is a common source of confusion when copying numerical data into percentage-formatted columns.
- How do I calculate percent change between two cells in Excel?
- Use the formula =(new_value - old_value) / old_value and format the result cell as Percentage. Example: if A2 contains the old value and B2 the new value, enter =(B2-A2)/A2 in C2 and format C2 as Percentage. To express as a number with a % sign using multiplication: =(B2-A2)/A2*100 formatted as Number (not Percentage). Wrap in IFERROR to handle cases where the old value is zero: =IFERROR((B2-A2)/A2, 'N/A').
- How do I make percentage columns sum to exactly 100% in Excel?
- Rounding errors often cause percentage columns to sum to 99.99% or 100.01%. The simplest fix is to round each percentage: =ROUND(part/total*100, 2). For the last item in the column, set it to =100-SUM(all_other_percentages) to force the total to exactly 100. For PivotTable percentages, the rounding is handled automatically and always displays as 100%.