Formula for Percentage in Excel (Table of Contents)
Excel Formula for Percentage Calculatiion
Percentage calculation is one of the basic mathematics we all have learned in the early stages of our life in mathematics class.
You take any profession to be it financier, accountant, admin, housekeeper, HR, any of the profession needs percentage calculation. Not only in profession common people day in day out work with percentages.
In excel it is no different to calculate percentages.
In this article, I will discuss the ways of calculating percentages in Excel.
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 numerator and another one is the denominator. We always dived the numerator by the denominator and multiply the result by 100 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 vacation 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%
Example #1 – Calculate the Formula for Percentage in Excel
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 – Calculate the Efficiency Percentage of Employees
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 – How to Find the Growth or Decline Percentage in Excel
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 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 about Excel Formula for Percentage
- 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 IFERROR function.
- We need to change the formatting of the cells to display percentage values.
You can download this Excel Formula for Percentage Template here – Formula for Percentage Excel Template
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 –
Leave a Reply