Excel Percentage Formulas
Excel makes percentage calculations easy once you know the right formulas. This guide covers every common percentage calculation you'll need in a spreadsheet, with exact formulas you can copy and paste.
Key concept: Excel works with decimals internally. When you type a formula that gives 0.15, format the cell as "Percentage" to display it as 15%. You can also multiply by 100 yourself.
1. Calculate a Percentage of a Number
Question: What is 15% of the value in cell A1?
=A1*15%
Or equivalently: =A1*0.15
If the percentage is in another cell (say B1 contains 15%): =A1*B1
| A (Value) | B (Percent) | C (Result) | Formula in C | |
|---|---|---|---|---|
| 1 | 200 | 15% | 30 | =A1*B1 |
| 2 | 500 | 8% | 40 | =A2*B2 |
| 3 | 1250 | 20% | 250 | =A3*B3 |
2. Find What Percentage One Number Is of Another
Question: What percentage is 30 of 200?
=A1/B1
Then format the cell as Percentage (Ctrl+Shift+5). Result: 15%.
For percentage of total in a column: If column A has sales by region and you want each region's share of total:
=A1/SUM($A$1:$A$10)
The dollar signs ($) lock the total range so you can drag the formula down without it shifting.
3. Calculate Percentage Change
Question: What's the percentage change from old value (A1) to new value (B1)?
=(B1-A1)/ABS(A1)
Format as percentage. If A1=100 and B1=125, result is 25%.
| A (Old) | B (New) | C (% Change) | Formula | |
|---|---|---|---|---|
| 1 | 100 | 125 | 25.0% | =(B1-A1)/ABS(A1) |
| 2 | $50,000 | $62,000 | 24.0% | =(B2-A2)/ABS(A2) |
| 3 | 200 | 170 | -15.0% | =(B3-A3)/ABS(A3) |
4. Add or Subtract a Percentage
Add 20% to a value:
=A1*(1+20%)
Subtract 15% from a value (apply a discount):
=A1*(1-15%)
| Original (A1) | Add 20% | Subtract 15% |
|---|---|---|
| $100 | $120 | $85 |
| $250 | $300 | $212.50 |
| $1,000 | $1,200 | $850 |
5. Format Cells as Percentages
Three ways to format a cell as a percentage in Excel:
- Keyboard shortcut: Select the cell and press
Ctrl+Shift+5 - Home tab: Click the % button in the Number group on the Home ribbon
- Right-click: Format Cells → Number tab → Percentage → choose decimal places
Important: If you type "15%" into a cell, Excel stores it as 0.15. If you type "15" and then format as percentage, Excel shows "1500%". Always be aware of whether your data is already in decimal or whole-number form.
6. Useful Percentage Functions
| Task | Formula | Example |
|---|---|---|
| Average percentage | =AVERAGE(range) | Average of growth rates |
| Weighted average | =SUMPRODUCT(values,weights)/SUM(weights) | Weighted grade calculation |
| Count cells above X% | =COUNTIF(range,">0.5") | Count cells over 50% |
| Conditional percentage | =SUMIF(criteria_range,criteria,sum_range)/SUM(sum_range) | % of sales from one region |
| Running cumulative % | =SUM($A$1:A1)/SUM($A$1:$A$100) | Pareto analysis |
Frequently Asked Questions
Why does my percentage show as 0% in Excel?
Most likely the cell contains a very small decimal (like 0.0015) that rounds to 0% with zero decimal places. Increase decimal places in the percentage format, or check if your formula is dividing by the wrong number.
How do I calculate year-over-year percentage change for a whole column?
If months are in rows with values in column B: In C2, enter =(B2-B1)/ABS(B1), format as percentage, and drag down. Each cell will show the % change from the previous row.
How do I show a percentage bar in a cell?
Use Conditional Formatting → Data Bars. Select the range, go to Home → Conditional Formatting → Data Bars, and choose a style. Excel will draw a proportional bar inside each cell based on its value.