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.
Download Practice Workbook
4 Steps to Calculate Bonus Percentage in Excel
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.
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.
Here, D5 and C5 are Bonus and Salary of Jane respectively.
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.
As a result, we’ll find the output of the Ratio from cell E6 to E15 like this.
Now, to calculate Bonus Percentage in the F5 cell, we need to write the formula like this.
Here, E5 refers to the Ratio of Bonus and Salary of Jane.
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.
Note: Also, we can use the following formula directly in the F5 cell.
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.
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.
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.
Thirdly, select the Percentage button.
As a result, we’ll see that all the ratios are converted to percentages like this.
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.
Now, we have put the Arithmetic Formula again in cell E5 to calculate the percentages like this.
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.
To avoid this error, we can write the IFERROR function in the E5 cell like this.
Here, 0 refers to the output when there are errors in the Percentage Calculation.
As a result, all the cells with error output have now given output as Zero.
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.
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.