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 dived the numerator by the denominator and multiply the result by 100 to get the percentage value.
For example: Assume you were on vacation for 15 days and you have spent 10 days in your hometown and remaining 5 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
- Step 2: Now we have 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 at 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 IFERROR formula. IFERROR formula is crucial here. If the calculation of Sales / Target returns.
The result as error IFERROR function turn that results into zero.
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 is 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 using 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 IFERROR formula along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –
- Percentage Change Formula
- Profit Percentage Formula
- How to Calculate Percentage Increase in Excel?
- Percentage Difference in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
