How to Calculate Percentage in Excel using Formula?
Percentages are always calculated on per hundred bases. That means per hundred what is the proportion. We need two kinds of numbers one is the numerator, and another one is the denominator. We always divided the numerator by the denominator and multiplied the result by 100 to get the percentage value.
For example: Assume you were on vacation for 15 days, and you have spent ten days in your hometown and the remaining five days in the USA. What is the percentage of days you have spent in the USA?
Here the total number of vacations is 15 days. It is split into two parts one is 10 days in hometown and 5 days in the USA.
- The excel formula for percentage is Portion Days / Total Days * 100.
- Percentage of Days spent in USA = 5 / 15 * 100 = 33.33%
- Percentage of Days spent in Home Town = 10 / 15 * 100 = 66.66%
Examples
Example #1
I have a given task to calculate the formula for percentage in excel of students based on the marks obtained in the year-end exams.
They had given me total marks in each subject. Each subject carries a maximum of 100 marks.
- Step 1: To get the percentage of each student, I need to calculate the total marks obtained by them among 6 subjects. Let me calculate total marks by adding all the 6 subjects together for each student.
Now I got a total of each student by adding all the 6 subjects together.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- Step 2: Now, we have the total marks column, i.e., numerator value. Maximum marks from 6 subjects is 600 i.e. 100 * 6 = 600 (denominator). Let me divide the marks obtained by each student by 600. i.e., Marks Scored / Total Marks *100
- Step 3: Now, we got the numbers. To arrive at the percentage values, we need to change the formatting of the cell. Select all the percentage cells and press Ctrl + Shift + %, or you can change the format under Home and Number then %
Example #2
All most all the corporate companies try to find out the efficiency percentage of their employees. Below is the individual sales employee sales report against their set targets. I need to calculate the efficiency level of each employee based on their target.
Now we all know how to calculate excel formula for a percentage. Here the formula is Sales / Target.
We go the efficiency levels, but in the end, we got two-division errors in excel, i.e., #DIV/0!.
We can eliminate this error by just tweaking our existing formula with the IFERROR formula in excel. IFERROR formula is crucial here. If the calculation of Sales / Target returns.
We get the result as zero using the IFERROR function.
Example #3
We can also find the growth or decline excel formula for a percentage. I have monthly sales for two months Jan and Feb 2018.
In Jan, sales were 53250, and in Feb, sales in 57500. It is clear that in Feb, sales are more. But the question is, what is the formula for the percentage of increase in excel in the sales when we compared to Jan.
Here we need to know what is the extra sale in Feb compares to Jan, i.e., Feb – Jan, then divide this variance by Jan sales.
Conclusion: In Feb sales, we use the formula for percentage increased in excel by 7.98% when we compared to Jan sales.
Things to Remember
- We will get the error as #DIV/0! If the numerator is zero.
- When we compare and find the growth or decline percentage, we get a negative percentage if the numerator or denominator value is less than zero.
- We can get rid of errors in the percentage calculation by using the IFERROR function.
- We need to change the formatting of the cells to display percentage values.
Recommended Articles
This has been a guide to Formula for Percentage in excel. Here we discuss how to calculate Percentage in excel using the IFERROR formula along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –
- Percentage Change Formula
- Percentage Profit Formula
- Calculate the Percentage Increase in Excel
- Percentage Difference in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion