Excel FormulasPercentage formulas in Excel
Percentages cause more confusion than any other everyday calculation in a spreadsheet. The arithmetic is simple. The confusion comes from the way Excel shows percentages, and from three questions that sound alike and are not. This page covers each kind with a formula you can copy.
How Excel stores a percentage
To Excel, a percentage is just a number: 18% is 0.18, and 100% is 1. The percent format multiplies the display by 100 and adds the sign. The value in the cell is still the decimal.
That gives two rules.
- Do not multiply by 100 in the formula. Write
=part/totaland format the cell as a percentage with the % button on the Home tab. If you multiply by 100 and also format, 25% shows as 2500%. - Typing 18% into a cell stores 0.18. A formula that uses the cell needs no dividing by 100.
The examples below use the TEXT function only to show the result in percent form on this page. In your own sheet, leave TEXT out and format the cell.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Salesperson | Region | Product | Units | Amount |
| 2 | 05-Jan-2026 | Asha | North | Notebook | 20 | 1200 |
| 3 | 12-Jan-2026 | Ravi | South | Pen | 50 | 500 |
| 4 | 20-Jan-2026 | Meena | North | Bag | 4 | 3000 |
| 5 | 02-Feb-2026 | Asha | East | Pen | 30 | 300 |
| 6 | 14-Feb-2026 | Kabir | South | Notebook | 15 | 900 |
| 7 | 28-Feb-2026 | Ravi | North | Bag | 2 | 1500 |
| 8 | 03-Mar-2026 | Meena | East | Notebook | 40 | 2400 |
| 9 | 15-Mar-2026 | Asha | North | Bag | 3 | 2250 |
| 10 | 22-Mar-2026 | Kabir | South | Pen | 60 | 600 |
| 11 | 01-Apr-2026 | Ravi | East | Notebook | 25 | 1500 |
| 12 | 18-Apr-2026 | Meena | North | Pen | 80 | 800 |
| 13 | 06-May-2026 | Asha | South | Bag | 5 | 3750 |
1. What percent of the total is this?
Divide the part by the whole.
=F2/SUM(F2:F13)Result: 0.064171123=TEXT(F2/SUM(F2:F13),"0.0%")Result: 6.4%=TEXT(SUMIF(C2:C13,"North",F2:F13)/SUM(F2:F13),"0.0%")Result: 46.8%
The first result is the raw decimal. The second shows it as a percentage. The third is the share of one region in total sales.
When this formula is copied down a column, the total must stay fixed: =F2/SUM($F$2:$F$13). See cell references.
2. Percentage change from old to new
Subtract the old value from the new and divide by the old.
=(new - old) / old
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr | May | Jun |
| 2 | Sales | 4700 | 2700 | 5250 | 2300 | 3750 | 4100 |
| 3 | Target | 4000 | 3000 | 5000 | 3000 | 3500 | 4000 |
=TEXT((C2-B2)/B2,"0.0%")Result: -42.6%=TEXT((D2-C2)/C2,"0.0%")Result: 94.4%=TEXT((D2-D3)/D3,"0.0%")Result: 5.0%
Sales fell from January to February, so the first result is negative. They rose from February to March. The third compares March sales with the March target.
Always divide by the starting value, the earlier period or the target. Dividing by the new value is the usual mistake. If the old value can be zero, guard against it: =IF(B2=0,"",(C2-B2)/B2).
3. Increase or decrease a number by a percentage
To add a percentage, multiply by one plus the rate. To take it off, multiply by one minus the rate.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Item | Category | Price | Stock |
| 2 | A101 | Basmati Rice 5 kg | Grocery | 650 | 40 |
| 3 | A102 | Toor Dal 1 kg | Grocery | 160 | 75 |
| 4 | B201 | Notebook | Stationery | 60 | 300 |
| 5 | B202 | Ball Pen | Stationery | 10 | 0 |
| 6 | C301 | LED Bulb | Electronics | 110 | 200 |
| 7 | C302 | Power Bank | Electronics | 1299 | 18 |
| 8 | D401 | Steel Bottle | Home | 450 | 55 |
=D2*(1+18%)Result: 767=D2*(1-10%)Result: 585=D2*18%Result: 117
The first is the price with 18 percent added, the second the price after a 10 percent discount, and the third the amount of an 18 percent tax alone.
With the rate in a cell, say H1, write =D2*(1+$H$1).
4. Finding the original before a percentage was added
A price includes 18 percent tax. What was it before tax? Do not subtract 18 percent of the final price. Divide by one plus the rate.
=ROUND(D2/(1+18%),2)Result: 550.85=ROUND(D2-D2/(1+18%),2)Result: 99.15=ROUND(D2*(1-18%),2)Result: 533
The first is the price before tax, and the second is the tax contained in it. The third, taking 18 percent off, gives a different and wrong answer, because 18 percent of the larger figure is more than 18 percent of the smaller one.
The same reasoning recovers the original price from a discounted one: divide by one minus the discount.
5. Marks as a percentage
Marks obtained divided by the maximum.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Class | Maths | Science | English |
| 2 | Asha | 8A | 78 | 85 | 91 |
| 3 | Ravi | 8B | 45 | 52 | 60 |
| 4 | Meena | 8A | 92 | 88 | 79 |
| 5 | Kabir | 8B | 33 | 41 | 38 |
| 6 | Tara | 8A | 67 | 72 | |
| 7 | Joy | 8B | 88 | 90 | 84 |
| 8 | Neha | 8A | 54 | 61 | 58 |
| 9 | Imran | 8B | 78 | 73 | 69 |
=TEXT(SUM(C2:E2)/300,"0.0%")Result: 84.7%=TEXT(AVERAGE(C2:E2)/100,"0.0%")Result: 84.7%=TEXT(COUNTIF(C2:C9,">=33")/COUNT(C2:C9),"0%")Result: 100%
The first two give Asha's overall percentage in two ways. The third is the share of students who passed Maths.
6. Points, not percent
When a rate moves from 10% to 12%, it has risen by 2 percentage points, and by 20 percent. The first is a subtraction, =new-old. The second is the percentage change formula. Say which you mean in any report, because the two figures tell very different stories.
7. A percentage of a percentage
Successive changes multiply, they do not add. A price raised by 10 percent and then cut by 10 percent does not return to where it started.
=D2*(1+10%)*(1-10%)Result: 643.5=D2Result: 650
The result is one percent below the original.
8. Growth over several periods
The average rate of growth per period, when a value has grown from a start to an end over n periods, is
=(end/start)^(1/n) - 1
From 2700 to 4100 over four months:
=TEXT((G2/C2)^(1/4)-1,"0.0%")Result: 11.0%
This is the idea behind a compound annual growth rate. Averaging the monthly percentage changes gives a different, less meaningful figure.
Formatting percentages
- The % button on the Home tab applies the format. The buttons beside it add or remove decimal places.
- Ctrl, Shift and % is the shortcut.
- Format the cell before typing, and typing 18 gives 18%. Format a cell that already holds 18, and it becomes 1800%, because the stored value is 18. Retype it as 0.18 or as 18%.
Common mistakes
- Multiplying by 100 and also using the percent format.
- Dividing a change by the new value.
- Taking a percentage off a tax-inclusive price to find the pre-tax price.
- Adding percentages that apply one after another.
- Averaging percentages of groups of different sizes. Go back to the totals: total passed divided by total students.
- Showing many decimal places. One is usually enough.
Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.