Calculate Percentage in Excel Formula

The percentage is calculated as the proportion per hundred. In other words, the numerator is divided by the denominator and the result is multiplied by 100. The percentage formula in Excel is = Numerator/Denominator (used without multiplication by 100). To convert the output to a percentage, either press "Ctrl+Shift+%" or click "%" on the Home tab's "number" group.

Let us consider a simple example.

On a 15-day vacation, Mr. A spent 10 days in his hometown and 5 days in the USA. What percentage of days did he spend in the USA?

The calculations are given as follows:

• Percentage formula=Portion days/Total days*100
• Percentage of days spent in the USA=5/15*100=33.33%
• Percentage of days spent in hometown=10/15*100=66.66%

Hence, Mr. A spent approximately 33% of his vacation period in the USA.

Let us go through the following examples to understand the concept of percentage in Excel.

Example #1

The following table shows the subject-wise marks of 6 students in a school. Since the results pertain to the annual examinations, the maximum marks of every subject are 100.

We have to calculate the percentage of marks for all students.

The steps to calculate percentages in Excel are listed as follows:

1. Calculate the total marks obtained by the students. For this, the marks of every subject are added.

   
   

2. Drag the formula of cell H2 to get the total marks obtained by all students. The output is shown in the following image.

   
   

3. Divide the total marks by 600. The values of the “total marks” column become the numerator. Since the number of subjects is 6, the maximum marks are 600 (100*6=600). This becomes the denominator.

   
   

4. Apply the following formula.

   
   Percentage=Marks scored/Total marks*100. 
   
   For percentage values (shown in the succeeding image), change the cell formatting. Select column I and press “Ctrl+Shift+%.” Alternatively, select “%” in the “number” group of the Home tab.
   
   

Example #2

The following table consists of students who undertake internship in an organization. Their task is to sell products based on pre-defined targets.

We want to determine the efficiency level (percentage) of every student.

The steps to calculate the efficiency level are listed as follows:

Step 1: Apply the formula “sales/target.”

Step 2: Format column D to obtain the efficiency levels in percentage. For the last two students (Gulp and Ganapathi), the formula returns “#DIV/0!” error.

Note: If the numerator is zero, the division of the numerator by the denominator returns “#DIV/0!” error.

Step 3: To eliminate the error, tweak the existing formula as “=IFERROR(C2/B2,0).” This implies that the IFERROR displays zero if the division of C2 by B2 returns an error.

In other words, the “#DIV/0!” error is replaced by zero with the help of the modified formula.

Note: The IFERROR function helps get rid of percentage errors.

Step 4: The output using the IFERROR function is shown in the following image. The efficiency levels of students Gulp and Ganapathi are zero. This is because of the blank (zero) in column B and/or column C.

Example #3

For 2018, an organization's sales for the months January and February are given in the following table.

We want to calculate the growth or the decline (percentage) in monthly sales.

The steps to calculate the increase (growth) or decrease (decline) percentage are listed as follows:

Step 1: Apply the formula “=(C2-B2)/B2.”

The sales in February (57500) are more than the sales in January (53250). The difference (growth) between the two sales is divided by the sales of the initial month (January).

Step 2: Format column D to obtain the growth percentage.

Hence, the February sales have increased by 7.98% over January sales.

Note: While calculating the growth or decline percentage, if the numerator or the denominator is less than zero, the output is a negative value.