How to Calculate Percentage in Excel: Share, Change and Totals

Excel doesn't have a single PERCENT button that does the maths for you. Instead, a percentage is two steps: a simple division, then a format that shows the answer with a percent sign. Once you see it that way, every percentage question, from "what share is this?" to "how much did it grow?", follows the same pattern.

I'll take you through the four calculations people need most, using Microsoft's own worked examples, plus the formatting quirk that turns 10 into 1000.00 percent and makes people think Excel is broken.

The basic percentage formula in Excel

Microsoft's help puts it as amount divided by total equals percentage. Excel works out the decimal, and the Percent Style format shows it as a percentage.

1. Put the part in one cell, say B2, and the total in another, say C2.
2. In D2, type =B2/C2 and press Enter. You'll see a decimal, such as 0.84.
3. With D2 selected, select Percent Style in the Number group on the Home tab, or press Ctrl + Shift + %.

Microsoft's example is 42 correct answers out of 50: =42/50 gives 0.84, and Percent Style shows 84 percent. You never multiply by 100 yourself. The format does that.

Why Excel shows 1000.00 percent instead of 10

This is the most common percentage mix-up, and Microsoft explains it clearly. If a cell already holds the number 10 and you apply the Percentage format, Excel multiplies it by 100 and shows 1000.00 percent.

• Already typed whole numbers? Divide by 100 first, or retype them as decimals, then format.
• Formatting empty cells first? Microsoft says Excel then treats typing 10 or 0.1 the same way: both show as 10.00 percent.
• Too many decimals? Open Format Cells, choose Percentage, and set Decimal places to 0 to see 10 percent rather than 10.00 percent.

Calculating percentage change in Excel

Percentage change compares a new number with an old one: new minus old, divided by old. Microsoft's example uses 2,342 as the starting value and 2,500 as the new value.

1. Put the old value in B2 and the new value in C2.
2. In D2, type =(C2-B2)/B2 and press Enter.
3. Apply Percent Style.

Microsoft's numbers give 0.06746, which Percent Style rounds to 7 percent. Going the other way, from 2,500 down to 2,425, gives -0.03, a fall of 3 percent. The brackets matter: without them, Excel divides before it subtracts and the answer is wrong.

This is the formula I'd use for month-on-month followers, email sign-ups or website visits.

Increasing or decreasing a number by a percentage

To add a percentage on top of a number, multiply by 1 plus the rate. To take a percentage off, multiply by 1 minus the rate. Microsoft explains that the 1 stands for 100 percent.

Goal	Formula (number in B2, rate in C2)
Increase by the rate	=B2*(1+C2)
Decrease by the rate	=B2*(1-C2)
Find the percentage of a number	=B2*C2

Keep the rate in its own cell formatted as a percentage. Then you can change it once and every formula that uses it updates.

Working backwards to the original number

Sometimes you know the result and the percentage, but not the starting number. Microsoft's example: 15 is what's left after 25 percent has been taken off, so 15 is 75 percent of the original. Divide 15 by 0.75 and you get 20.

In formula form, if the reduced number is in B2 and the percentage removed is in C2, use =B2/(1-C2).

Percentage of a total in Excel

To see what share each row makes of the whole, divide each row by the grand total, and lock the total cell with F4 before you copy the formula down so it doesn't shift.

1. Work out the total at the bottom of the column, using the SUM function.
2. In the next column, divide the first row by the total, for example =B2/B10, pressing F4 on B10 to lock it.
3. Copy the formula down and apply Percent Style.

The shares should add up to 100 percent. If they don't, the total cell probably moved when you copied. Shares of a whole are also exactly what a pie chart shows, and my guide on making a pie chart in Excel turns this column into a picture.

The PERCENTOF function

Microsoft lists a newer function in Excel for Microsoft 365: =PERCENTOF(data_subset,data_all). It adds up a subset and divides it by the total of all the values, the same as dividing one SUM by another. If your version doesn't recognise it, the division method above does the same job.

Showing a percentage as a whole number

Percent Style rounds to whole percentages by default, which hides small differences. If two results both show as 7 percent, select the cells and use Increase Decimal on the Home tab to see more detail. Remember that this only changes how the number looks. Excel still calculates with the full value behind the scenes, so totals and charts stay accurate.

Making negative percentages stand out

When you track change over time, falls are worth spotting quickly. Microsoft suggests a custom number format, 0.00%;[Red]-0.00%, which shows positive percentages normally and negative ones in red. Find it under Format Cells, Number, Custom. Conditional formatting can do the same job if you prefer menus.

If your Excel percentage looks wrong

• Huge results like 1000.00 percent: you formatted a whole number. Divide by 100 or use a decimal.
• #DIV/0!: the total cell is empty or zero.
• Odd shares or errors after copying down: the total wasn't locked with F4.
• A decimal instead of a percent: the format is still General. Apply Percent Style.

Frequently asked questions

What is the formula for percentage in Excel?

Divide the part by the total, such as =B2/C2, then apply Percent Style. Microsoft describes it as amount divided by total equals percentage.

How do I calculate percentage change in Excel?

Use =(new-old)/old, for example =(C2-B2)/B2, then apply Percent Style. Microsoft's example of 2,342 rising to 2,500 shows as 7 percent.

Why does Excel turn 10 into 1000.00 percent?

Microsoft says applying Percentage format to an existing number multiplies it by 100. Enter 0.1, or divide by 100 first, to see 10 percent.

How do I add a percentage to a number in Excel?

Multiply by 1 plus the rate, such as =B2*(1+C2). To take a percentage off, use =B2*(1-C2).

What's the shortcut for percent format in Excel?

Microsoft lists Ctrl + Shift + % to apply percentage formatting, or select Percent Style in the Number group on the Home tab.