How to Calculate Bonus Percentage in Excel: 4 Methods

Method 1 – Applying Direct Arithmetic Formula to Find Ratio

Apply an arithmetic formula to calculate the Bonus Percentage. Find out the Ratio in cell E5 like this.

D5 and C5 are Bonus and Salary of Jane.

By pressing ENTER, we find the ratio as 0.297297291.

Use Fill Handle to AutoFill cells from E6 to E15. We can do it by dragging down the cursor while holding the bottom right corner of the reference E5 cell.

Find the output of the Ratio from cell E6 to E15 like this.

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

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

Press ENTER. Then, using the 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.

Method 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 percentage figure instead of decimal values.

We need to follow the below steps for this calculation.

Select cells from E5 to E15, which are filled with ratios.

Press CTRL+SHIFT+% to convert this Ratio into a percentage.

Find Bonus Percentage from cells E5 to E15 like this.

See that all of the percentage values are in round figures.

Method 3 – Utilizing Number Format to Calculate Bonus Percentage in Excel

Select cells from E5 to E15.

Go to the General bar.

Select the Percentage button.

See that all the ratios are converted to percentages like this.

Method 4 – Applying IFERROR Function to Eliminate Error

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!. We changed our dataset like below, where some employees are not given the bonus, and their bonus cells are empty.

Put the Arithmetic Formula again in cell E5 to calculate the percentages like this.

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.

Write the IFERROR function in the E5 cell like this.

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

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.

Download Practice Workbook

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