How to calculate percentages in Excel: percent of a number, change, discount and VAT

A percentage in Excel is an ordinary number. 25% is 0.25, 100% is 1, and the percent sign is only a format that multiplies the number by 100 on screen. Once you see it that way, every percentage question turns into multiplying or dividing by that number, and each kind of question has one short formula.

This guide has them all: a percent of a number, adding and removing a percentage, the VAT or sales tax inside a price, discounts, what percent one number is of another, percent change, margin and markup, and percentage points. Each comes with an example you can read cell by cell, and an exercise where you write it yourself.

Every formula on one page

In each formula, B2 is the number you start from and C2 holds the percentage or the second number. Format the result as a percentage when it's a share or a change, and as a number when it's an amount.

How Excel stores a percentage

Type 25% into a cell, and Excel stores 0.25 and shows it with the Percent format. Type 0.25 and press Ctrl+Shift+5, and you get the same thing. Both cells hold the same number, and formulas see 0.25 either way.

That's why a formula never needs to divide by 100. =B2*25% is the same as =B2*0.25, and if C2 holds 25%, =B2*C2 already multiplies by 0.25.

The trap is the other way around. Type 25 into a cell, then format it as a percentage, and Excel shows 2500%, because 25 is 25 whole units. If a column of rates came in as whole numbers, divide them by 100 once, or type them again with the % sign.

The Percent format also rounds what you see. 0.125 shows as 13% with no decimals, while the cell still holds 0.125. Click Increase Decimal on the Home tab to show 12.5%. Formulas always use the full number, whatever the cell shows.

One more habit pays off in every section below: put the rate in its own cell. A VAT rate, a discount or a target typed into a formula is hidden, and it has to be changed in every formula when it changes. A rate in a labeled cell is visible, and one change updates everything.

A percent of a number

To find a percent of a number, multiply. With the price in B2 and the VAT rate in E1, the VAT on the printer is:

The $ signs lock E1, so the formula still points at the rate when you fill it down to the monitor and the rest. Without them, the copy in C3 would point at E2, which is empty, and give 0. Press F4 while the cursor is in E1, and Excel adds the $ signs for you.

The same formula answers any question of the form "what is 15% of this?": a tip, a commission, a deposit, a bonus. Multiply the amount by the rate.

Add a percentage

To add a percentage, such as VAT, sales tax or a markup, you could work out the amount and add it: =B2+B2*$E$1. It's shorter to multiply by 1 plus the rate:

The 1 keeps the price and the 0.25 adds the VAT, so 800 × 1.25 = 1,000. The parentheses matter. Without them, Excel multiplies first and then adds 0.25 to the result.

VAT and sales tax work the same way. The examples here use 25%, the VAT rate in Denmark, and a sales tax of 8% is just 0.08 in the rate cell. Practice it in add VAT to a price, and the same formula with a markup on a cost price in add a markup to a cost price.

Remove a percentage that was added

Going back is where most people go wrong. A price of 1,000 includes 25% VAT, and you want the price before VAT. Taking 25% off gives 750, which is wrong, because the VAT was 25% of the price before VAT, not of the total. Divide by 1 plus the rate instead:

Check it: 800 plus 25% of 800 is 1,000. The rule works for anything that was added as a percentage. A price that includes a 10% service charge is divided by 1.1, and a salary after a 4% raise is divided by 1.04 to get last year's salary. Practice it in find the price before VAT.

The VAT inside a total

Sometimes you need the tax itself, for example to fill in a VAT return from a list of receipts. It's the total minus the price before VAT:

Notice that the VAT is 200 of 1,000, which is 20% of the total. With 25% VAT, the tax is always a fifth of a price that includes it, so =B2*0.2 gives the same result. It breaks the day the rate changes, though, so the version with E1 is safer. At 20% VAT, the tax is a sixth of the total, and at 8% sales tax it's 0.08 / 1.08 of it. The exercise find the VAT in a price that includes it drills the full formula.

Rounding to the cent

Percentages rarely come out even. 25% VAT on 79.99 is 19.9975, and a price with VAT of 99.9875 shows as 99.99 but still holds every decimal. Add up a column of those, and the total can be a cent off from the receipts.

Round where the money is rounded, usually per line: =ROUND(B2*(1+$E$1),2). ROUND with 2 rounds to two decimals, and the cell then holds exactly 99.99. The guide to Excel for finance explains when to round per line and when to round the total, and round a price to two decimals is the exercise.

Take a percentage off

A discount takes a percentage off the price. Multiply by 1 minus the discount:

25% off means paying 75%, so 600 × 0.75 = 450. The long version, =B2-B2*C2, gives the same answer. Two discounts in a row multiply: 20% off and then another 10% off is =B2*(1-20%)*(1-10%), which is 28% off in total, not 30%. Try it in sale price after a discount.

The reverse works like VAT. If a jacket costs 450 after 25% off, the original price is =B2/(1-C2), which is 450 / 0.75 = 600. Adding 25% back on would give 562.50, because the 25% was taken off the original price, not the sale price.

Change a whole column by a percentage

To raise every price in a column by 10% without a helper column, let Paste Special do the multiplying. Type 1.1 in an empty cell and copy it. Select the prices, press Ctrl+Alt+V, choose Multiply and click OK. Every selected cell is now 10% higher, and you can delete the 1.1.

The change overwrites the old values, so it suits a one-off update of a price list better than a model you'll change again. For anything you might want to adjust, keep the old prices, put the increase in its own cell and use =B2*(1+$E$1) in a new column. Changing E1 then reprices everything, and the old prices are still there to compare with.

What percent one number is of another

To find what percent one number is of another, divide the part by the whole:

The part goes on top. 45 of 60 is 45/60, and 60/45 would say 133%, which answers a different question. Format the result as a percentage, and the 0.75 shows as 75%.

When each row is a share of the same total, lock the total: =B2/$B$10 with the total in B10, or =B2/SUM($B$2:$B$9) without a total row. That's the formula behind every "% of total" column and every pie chart. It's drilled in each region's share of the total, and the guide to pivot tables shows the same calculation as % of Grand Total.

The same division measures progress against a target. Sales of 56,000 against a target of 50,000 is =B2/C2, or 112%, so the target was beaten by 12%. What's left to go is 1 minus the share: with 45 of 60 invoices done, =1-B2/C2 gives 25% still open. A share above 100% is fine here. It only means the part is bigger than the whole you compare it with, which is exactly what beating a target means.

Percent change

Percent change compares a new value with an old one. Subtract the old from the new, and divide by the old:

Divide by the old value, the one you're comparing against. 300/1,500 would say 20%, which is the drop you'd need to go back from 1,500 to 1,200, not the increase. Tea went from 800 to 720, and the same formula gives -10%, a decrease. Fill it down, and positive numbers are growth and negative numbers are decline.

=C2/B2-1 is the same calculation in fewer characters, and you'll see both in real workbooks. If the old value can be 0, the formula gives #DIV/0!, since nothing can grow by a percentage from zero. Show a blank instead with =IF(B2=0,"",(C2-B2)/B2). The guide to IF has more ways to handle cases like that. Practice the basic version in percent change from last year.

Growth over several years

Percent change over several years can't be divided evenly between them. Sales that grew from 1,000 to 1,500 over five years grew 50%, but not 10% a year, since each year's growth builds on the year before. The average yearly growth is =(C2/B2)^(1/5)-1, which is about 8.4%. Check it the other way: 1,000 × 1.084 five times is close to 1,500. Finance calls this the compound annual growth rate, CAGR.

To project the other way, grow a value by the same percentage for several years with =B2*(1+C2)^5, where C2 holds the yearly growth and 5 is the number of years.

Show a plus sign on increases

A column of changes is easier to read when increases have a plus sign. Select the cells, press Ctrl+1, choose Custom and type the format +0%;-0%;0%. The three parts are for positive numbers, negative numbers and zero, so 0.25 shows as +25% and -0.1 as -10%. For a color on top, add Green and Red in front of the first two parts, or use conditional formatting.

Margin and markup

Margin and markup both start from the same profit, the price minus the cost, but they divide it by different numbers. Margin divides by the price:

Markup divides by the cost instead: =(B2-C2)/C2, which gives 54% for the same lamp. A 35% margin and a 54% markup describe the same sale, so always say which one you mean. Retail and finance usually talk about margin, and pricing from a cost price usually uses markup.

To set a price that gives a target margin, divide the cost by 1 minus the margin: =C2/(1-D2) with the target margin in D2. A cost of 130 and a 35% margin give a price of 200. Practice it in profit margin from price and cost.

Percentage points

When the numbers you compare are already percentages, there are two kinds of change. Subtracting them gives the difference in percentage points:

A conversion rate that goes from 50% to 75% has risen by 25 percentage points. As a percent change, with =(C2-B2)/B2, it has risen by 50%. Both are correct, and they answer different questions, so reports should say which one they use. "Up 25 points" and "up 50%" mean the same thing here, and mixing them up makes a change look twice or half as big.

Averages of percentages

Averaging a column of percentages gives the wrong answer when the rows have different sizes. If one team finished 45 of 60 invoices, 75%, and another finished 12 of 40, 30%, the average of the two percentages is 52.5%. But together they finished 57 of 100, which is 57%.

For an overall percentage, add up the parts and the wholes first and then divide: =SUM(B2:B5)/SUM(C2:C5). Use the plain average of percentages only when each row should count the same, whatever its size.

Common mistakes

• Typing 25 instead of 25%. The cell holds 25, so a formula multiplies by 25, not by a quarter. Type the % sign, or 0.25.
• Taking a percentage off to remove VAT. 1,000 × 0.75 = 750 takes off too much. Divide by 1.25 to get 800.
• Forgetting the parentheses. =B2*1+C2 multiplies by 1 and then adds the rate. Write =B2*(1+C2).
• Dividing by the wrong number. Percent change divides by the old value, and a share divides by the whole.
• A rate that moves when you fill down. Lock the rate cell with $, or every row after the first points at an empty cell and gives 0.
• Adding up percentages. 10% of one thing plus 10% of another isn't 20% of anything. Add up the amounts, then divide.
• A number formatted as percent that looks rounded. The cell shows 13%, but formulas use 0.125. Add decimals with Increase Decimal, or round the number with ROUND if the rounded value is what you want.

Percentages in Google Sheets

Every formula in this guide works unchanged in Google Sheets. Format a cell as a percentage with Format > Number > Percent or the % button on the toolbar. Sheets also has a TO_PERCENT function, but the plain formulas above are easier to read and work in both programs. See Google Sheets or Excel for the other differences.

Percentages in Excel tests

Percentages turn up in almost every employer Excel test, usually as one row of a larger task: the change from last year, the share of the total, a price with VAT or after a discount. The math is quick, so the points are lost on details. Lock the rate cell before you fill down, divide by the old value for a change, divide by 1 plus the rate to remove VAT, and format the answer as a percentage when the task asks for one. Check the first row by hand before you fill the formula down.

In short

Excel stores 25% as 0.25, so percentages are just multiplication and division. Multiply by the rate for a percent of a number, by 1 plus the rate to add it and by 1 minus the rate to take it off. Divide by 1 plus the rate to remove VAT or anything else that was added. Divide the part by the whole for a share, and the change by the old value for a percent change. Keep the rate in its own cell, lock it with $, and format the answer as a percentage when it's a share.

The exercises below drill each formula on a small sheet, from the change since last year to the VAT in a price and a profit margin. Try the first one for free, and write the formula yourself.