Percentage Formula in Excel (6 Examples)

In Microsoft Excel, we can use different methods to calculate the percentage of a numeric value with a formula. We can apply algebraic calculations or insert a function to meet our objectives. In this article, you’ll get to learn all suitable methods to determine the percentage of a numeric value by using a formula in Excel spreadsheets.


Download Practice Workbook

You can download the practice workbook that we have used here for demonstration. Click the following button and practice yourself.


Calculation with Percentage Formula in Excel: 6 Examples

The term Percentage means a fraction of 100 calculated by dividing the numerator by the denominator and then multiplying the fraction by 100. It’s a mathematical term that expresses the proportion of an amount per hundred.

For example, if a class has 100 students and 55 of them are male, we can say that the percentage of male students in the class is 55 percent or 55%.

The basic percentage formula is as follows:

(Value/Total Value) x 100

Now we will show how we can calculate the percentage using formulas in excel with daily life examples.


1. Basic Percentage Formula in Excel

There is no single formula for percentage which is applicable in every calculation. Although the basic principle is the same- to divide a partial value by the total value and multiply the result by 100.

The basic MS Excel formula for percentage is as follows:

Numerator/Denominator = Percentage

Unlike the conventional basic formula for percentage, Excel basic formula doesn’t contain the x100 part. Why is this so? We’ll discuss it at the end of the following example.

Example for Basic Percentage Formula in Excel:

Let’s assume that we have a simple data set. We have to calculate the percentage of mangoes in the proportion of total fruits.

Basic Percentage Formula in Excel

  • All we need to do is- just type the formula: =B5/C5 in cell C7 and press Enter.

You can also enter the formula in cell C7 this way:

  • Type “=” > Click once on cell B5 > Type “/” > Click once on cell C5.
  • Then press Enter.

What we see in Cell C7 is the result 0.10. We actually expected something like 10% or 10 percent.

What we could do is multiply the formula by 100. But Excel doesn’t need that. Excel has a Percentage Style button in the Number group at the Home tab.

  • Go to the Home tab > Number Group or use keyboard shortcut Ctrl+1 and go directly to Number group.
  • Then Go to Percentage > Select the Decimal places > Press OK.

Number Format Conversion to Percentage Style

You can also convert the number format into percentage style using a simple keyboard shortcut.

The Keyboard Shortcut for Percentage Style-Ctrl+Shift+%:

Select the cell(s) before or after your calculation and press Ctrl+Shift+%. The numerical result will be converted to percent style.

Applying percentage style on Cell C7, now we have our result in the desired look (10.0%).

Note:

Remember the shortcut Ctrl+Shift+%. We will use that all the way through this article.

As we have discussed earlier in this article, there is no fixed format of the formula for percentage. You have to arrange the formula according to the type of your calculation. See more examples in the following sections.


2. Formula for Percentage of Total

Let’s assume a case that we have a list of several mangoes and apples. We have to calculate the percentage of total mangoes and total apples in the proportion to the total number of fruits.

Formula for Percentage of Total in Excel

  1. Then, type the formula =B11/$B$14 in Cell C14 > press Enter.

The Absolute Cell Reference ($) sign in the formula indicates that Cell B14 will always be the denominator when you copy the formula in Cell C14 (or wherever).

We have copied the formula to Cell C14 too and applied it.

Now we have the percentages, but they are in fraction format.

  • Select the Cells B14 and C14 and press Ctrl+Shift+%.

We have the results in percentage format now.

Read More: How to Calculate Total Percentage in Excel (5 Ways)


3. Excel Formula for Percentage Difference between Rows and Columns

The excel formula for calculating the change between two values is:

(New Value - Old Value)/Old Value

Percentage Change between Rows:

Assuming that we have an attendance sheet of a student. We have to determine the percentage change of his attendance between two consecutive months.

Formula for Percentage Change between Rows

  • Type the formula =(C6-C5)/C5 in Cell D6 > Press Enter.
  • Copy and paste the formula to the next cells > Press Enter.

Look at Cell D8. The formula is copied and changed accordingly to return the percentage change between March and April.

We have calculated the percentage change of the student’s presence between two consecutive months, but again in fraction format.

We can convert the fraction format to percentage style using keyboard shortcut Ctrl+Shift+%. You can convert the fraction to percentage using the lengthy process described at the starting of this article, but we won’t recommend that for Excel spreadsheets.

Percentage Change between Rows

Percentage Change between Columns:

Let’s assume that we have the mark sheet of a student. We have to determine the percentage change of his marks in different subjects between half-yearly in June and final exam in December.

Percentage Change between Columns

  • Type the formula =(D5-C5)/C5 in cell E5 > Press Enter.
  • Copy and paste the formula to the next cells > Press Enter.

Percentage Change between Columns

Look at Cell E7. The formula is copied and changed duly to give the percentage change.

Again, we have calculated the percentage change between two consecutive columns in fraction format.

Convert the fraction to percentage using a comfortable way described previously in this article

Percentage Change between Columns

Read More: Percentage Difference between Two Percentages Excel (2 Easy Ways)


Similar Readings


4. Formula to Calculate an Amount or Total by Percentage

In a super shop, 20% of the fruits are mangoes. You can calculate the number of mangoes or the number of total fruits in the following way.

Formula to Calculate an Amount or Total by Percentage in Excel

Calculate an Amount by Percentage:

  • Just enter the formula =B5*C5 in cell D5. Press Enter.

Calculate an Amount by Percentage

So the number of mangoes in the shop is 30 as we see in the following screenshot.

Calculate an Amount by Percentage

Calculate the Total by Percentage:

  • Just enter the formula =B8*C8 in cell D5. Press Enter.

Calculate the Total by Percentage

So the number of the total fruits in the shop is 150 as we see in the following screenshot.

Calculate the Total by Percentage

We can also customize the number format. Click here to know more. I have used the 0 “Nos” customized format here.

Amount or Total by Percentage in Excel

Read More: Excel formula to calculate percentage of grand total (4 Easy Ways)


5. Excel Formula to Increase/Decrease an Amount by Percentage

Assume that we have certain input numbers and have to apply the positive or negative change on them by percentage.

Formula to Change an Amount by Percentage in Excel

The formula is simple:

New Value = Old Value + Old Value x Percentage Change
  • So, enter the formula =B5+B5*C5 in Cell D5 > Press Enter.
  • Copy and paste the formula in the range of cells D6:D10 > Press Enter.

Look at the following screenshot. The formula is copied and changed properly to return the output numbers.

Read More: How do you Calculate Percentage Increase or Decrease in Excel


6. Use of Excel IFERROR Function in Percentage Formula

Your data set may contain text strings. As a result, the percentage formula entered in the cell will give erroneous values like #DIV/0! or #VALUE! etc. In these cases, you can use the IFERROR function to make your data set look better.

Use of Excel IFERROR Function in Percentage Formula

  • Type the following formula in Cell E5:
=(D5-C5)/C5

We will add the IFERROR function with that.

  • Type the following formula in Cell E5.
=IFERROR((D5-C5)/C5,"-")
  • Copy and paste the formula in the range of cells E6:E9.

Use of Excel IFERROR Function in Percentage Formula

As a result, when an error occurs in the result, the formula will return the output as in double quotations.

Use of Excel IFERROR Function in Percentage Formula

Related Content: How to Apply Percentage Formula in Excel for Marksheet (7 Applications)


Conclusion

Concluding, we have described 6 basic usages of the percentage formula in Excel. Besides we have also given a short intro of the percentage concept for the newcomers. Hope you will find all these methods instrumental. The workbook is there for you to download and practice yourself. If you have any questions, comments, or any kind of feedback, please let me know in the comment box.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo