How to Calculate Bonus Percentage in Excel (Step-by-Step Guide)

Get FREE Advanced Excel Exercises with Solutions!

Every office deals with bonuses on salary for various festivals and occasions. This bonus varies from employee to employee. It depends on employee activities and outputs sometimes. Every company calculates the percentage of bonuses for its statistical analysis. In Microsoft Excel, we can easily calculate bonus percentages even if the number of employees is huge. We can do this by studying how to calculate bonus percentages in Excel. In this article, we’ll discuss the step-by-step guide on how to calculate bonus percentage in Excel.


How to Calculate Bonus Percentage in Excel: 4 Easy Steps

Let’s say, we have a dataset based on the Salary and Bonus of some employees of a company in California to calculate the Bonus Percentage. The dataset is like this.

how to calculate bonus percentage in Excel

We’ll now try to calculate the Bonus Percentage step-by-step.


Step 1: Applying Direct Arithmetic Formula to Find Ratio

Simply, we can apply an arithmetic formula to calculate the Bonus Percentage. We can first find out the Ratio in cell E5 like this.

=(D5/C5)

Here, D5 and C5 are Bonus and Salary of Jane respectively.

how to calculate bonus percentage in Excel using Arithmetic Formula

Secondly, by pressing ENTER, we find the ratio as 0.297297291.

After that, we should use Fill Handle to AutoFill cells from E6 to E15. We can do it by dragging down the cursor while holding the right bottom corner of the reference E5 cell.

how to calculate bonus percentage in Excel using Arithmetic Formula

As a result, we’ll find the output of the Ratio from cell E6 to E15 like this.

how to calculate bonus percentage in Excel using Arithmetic Formula

Now, to calculate Bonus Percentage in the F5 cell, we need to write the formula like this.

=E5*100

Here, E5 refers to the Ratio of Bonus and Salary of Jane.

how to calculate bonus percentage in Excel using Arithmetic Formula

Finally, we need to press ENTER. After that by using Fill Handle, we find the output of Bonus Percentage from cell F5 to F15 like this.

how to calculate bonus percentage in Excel using Arithmetic Formula

Note: Also, we can use the following formula directly in the F5 cell.

=(D5/C5)*100

Step 2: Using Keyboard Shortcut to Calculate Bonus Percentage in Excel

After finding the Ratio of Bonus and Salary, we can use a keyboard shortcut to find the Bonus Percentage. This will also give a round figure of the percentage instead of decimal values.

We need to follow the below steps for this calculation.

Firstly, we need to select cells from E5 to E15 which are filled with ratios.

how to calculate bonus percentage in Excel using Keyboard Shortcut

Secondly, we need to press CTRL+SHIFT+% to convert this Ratio into the percentage.

Therefore, we’ll find Bonus Percentage from cells E5 to E15 like this.

 using Keyboard Shortcut

Eventually, we can see that all of the percentage values are in round figures.


Step 3: Utilizing Number Format to Calculate Bonus Percentage in Excel

Alternatively, we can use Number Format instead of using Keyboard Shortcut or multiplying the ratio by 100 to get the final percentage. For doing this we need to follow the below steps.

Firstly, select cells from E5 to E15.

Secondly, go to the General bar.

 using Number Format

Thirdly, select the Percentage button.

how to calculate bonus percentage in Excel using Number Format

As a result, we’ll see that all the ratios are converted to percentages like this.

how to calculate bonus percentage in Excel using Number Format


Step 4: Applying IFERROR Function to Eliminate Error

Lastly, we can apply the IFERROR function when the Numerator or Denominator in the formula has no value and Excel gives the output as #Value! Or #Div/0!. To solve this problem we have changed our dataset like below where some of the employees are not given the bonus and their bonus cells are empty.

how to calculate bonus percentage in Excel using IFERROR Function

Now, we have put the Arithmetic Formula again in cell E5 to calculate the percentages like this.

=(D5/C5)*100

 using IFERROR Function

After pressing ENTER, we can see that the output of the cells E6, E14, and E14 are #Value!. This is because the Bonus cells of these cells are empty and the above formula hasn’t found any numerator to calculate.

 using IFERROR Function

To avoid this error, we can write the IFERROR function in the E5 cell like this.

=IFERROR(D5*100/C5,0)

Here, 0 refers to the output when there are errors in the Percentage Calculation.

 using IFERROR Function

As a result, all the cells with error output have now given output as Zero.

 using IFERROR Function


Things to Remember

  • We can use the Arithmetic Formula directly or step by step by calculating the ratio first. Then we can multiply it by 100 to get the percentage.
  • If we want to get the percentage in exact decimal form, we should not use Keyboard Shortcut. Because it will give a round figure as the output.
  • If all the cells which we need to calculate the percentage are filled with some values, we need not use the IFERROR function.

Download Practice Workbook


Conclusion

By studying this article we’ll definitely figure out any percentage we need to calculate in our daily activities. We can calculate percentage either salary based or some other topics based.


<< Go Back to Commission Bonus | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo