How to Calculate Percentages in Excel: The Exact Formulas You Need

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

If you regularly work with numbers, you probably also work with percentages. And when you do, you’ll find that Excel can handle them just as well as whole numbers or decimals.

Still, you need to know how to get the right numbers in the right places to get those percentages. Especially because Excel doesn’t have functions that do it for you.

So we’ll go over the basics here.

Converting decimals to percentages

We’ll start with a simple conversion: turning decimals into percentages in Excel.

You probably already know that multiplying a decimal by 100 turns it into a percentage.

But what if you want to display that number as a percentage in Excel?

It’s super easy. Just right-click on a cell that contains a decimal (or a row or column full of such cells), and select Format Cells.

format-cells

In the Number tab of the resulting menu, click Percentage.

format-percentage

You can also choose the number of decimal points you’d like in each percentage.

Keep in mind that those decimal points will show up even if you have a whole number.

So if you choose three decimal places, 75% would show up as 75.000%.

Kasper Langmann, Co-founder of Spreadsheeto

After that, hit OK, and your decimals will be converted to percentages.

Get your FREE exercise file

Want to practice some of the skills we go over in this article?

Grab the free exercise file and follow along!

cloud-download

BONUS: Download the Percent Exercise File (with 2 pre-loaded exercises) to go along with this post.

Calculating percentages

Let’s go over the most basic thing you can do with percentages: calculate them.

If you open the first sheet in the example workbook, you’ll see a list of student names next to their score on a test. The final column contains the maximum possible score on the test.

Why don’t we calculate each student’s percentage score on the test?

First, click into cell D2, so the percentage shows up in the Percentage column.

Then, type the following formula:

=(B2/C2)*100

percentage-score

Hit Enter to get the student’s score as a percentage of the total possible points.

You can then use the fill handle to drag the formula down to the rest of the scores.

Multiplying by 100 shows you the percentage—not the decimal. Keep this in mind if you want to change this column to percentage format.

If you do, you’ll want to divide all the cells by 100 before changing the format. Keeping track of how your number is displayed is crucial.

(In the next example, we won’t multiply by 100; keep an eye on how that changes the resulting numbers.)

Kasper Langmann, Co-founder of Spreadsheeto

You’ll see that Excel has given us six decimal places, which isn’t standard practice for grades. Let’s make that a little cleaner.

Highlight the column, right-click, and select Format Cells…

Select Number from the Category menu, and click the down arrow next to Decimal Places until it reads “0.”

percentage-formula

Click OK, and you’ll see that the percentage scores have been rounded to the nearest whole number.

(If you want to round with more options, check out Excel’s rounding functions.)

Calculating percentage change

One of the common things people ask about percentages in Excel is how to calculate the percentage change between two numbers.

To see how this calculation works, open the second sheet in the example workbook (it’s called “Revenue”).

You’ll see that we have companies’ Q1 and Q2 revenues, as well as a column for the percentage change.

Let’s find the percentage change from Q1 to Q2.

First, click into cell D2 and type the following formula:

=(C2/B2)-1

percentage-change

Don’t forget to subtract one! That’s crucial when calculating percentage change.

Kasper Langmann, Co-founder of Spreadsheeto

Now, as you can see, you have the percentage change in decimal format.

Use the steps detailed above to turn it into a percentage.

percentage-change-format

Note that some of the values in the spreadsheet are negative. That’s to be expected!

Percentage change works just as well with negative changes as it does with positive ones.

Finding percentiles

Another common task when working with percentages is calculating a number’s percentile rank.

The percentile rank of a number is where it falls in a range of numbers.

For example, if a number is in the 20th percentile, 20% of the numbers in the range are below that particular number.

Excel has two functions that let you calculate percentiles:

The syntax of Excel’s percentile functions

=PERCENTILE.EXC(array, value)
=PERCENTILE.INC(array value)

Both functions include the same arguments. The array argument is the range of numbers that you’re looking in for the percentile.

Value is the percentile rank you’d like to find.

Both of these will make more sense when we go through an example.

The difference between these two functions is that the first is exclusive, and the second is inclusive. In short, the percentile ranks available in the exclusive function don’t include 0 or 1, while both can be used in the inclusive function.

Let’s go back to the first sheet in the workbook and find a specific percentile for this class.

First, click into cell F4 and type “60th percentile:” so we don’t forget what we’re calculating. Then click over to H4 and type the following formula:

=PERCENTILE.INC(D2:D51, .6)

percentile-inc-formula

Remember to type the percentile as a decimal in the formula!

Kasper Langmann, Co-founder of Spreadsheeto

Hit Enter and you’ll see that the 60th percentile of this class’s scores is just above an 80% score on the test.

percentile-test

You can confirm this by running the same formula on column B, and dividing the result by 87, the maximum possible score.

Conclusion

Calculating percentages in Excel is a great skill to have—especially if you’re comfortable with percentage change. Fortunately, working with percentages is very easy in Excel!

If you can run a calculation with any formula, you can quickly turn the result into a percentage. Remember to format the values before you present or share your document, and you’re good to go.

2019-07-10T12:31:18+00:00