Why Are My Percentages Wrong in Excel? (4 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Trying to calculate percentages from some data but getting the wrong results? Thinking why my percentages are wrong in Excel? Actually, this may happen from different silly reasons. Burt doesn’t need to worry at all. This article will show you the reasons of why the percentages are wrong in Excel and hacks to unlock the solutions available for that problem.


4 Exclusive Solutions If There Are Wrong Percentages in Excel

Let’s say, we have a dataset of some employees of a company, the number of days they have worked, and the total working days that employees were supposed to work. From here, the percentages of their presence have to be calculated.

But. the percentage may be wrong due to some reasons and we want to fix that. This section will find 4 reasons why the percentages are wrong in Excel and relevant ways to solve this problem. I will discuss them one by one here with proper illustrations. Let’s check them now!


1. Remove Percentage Character

We have calculated the percentage of presence for the employees. The formula encountered here is:

=(C5/D5)*100 %

Here,

  • C5 = Days of Presence
  • D5 = Total Working Days

Percentages Are Wrong for the Character in Excel

Actually, in this formula, the ratio C5/D5 already denotes 0.9. Multiplying this by 100 results in 90. But inputting the % character in the formula again divides the value by 100. So, the result was found 0.9 again.

So, we want to fix the value so that the 100 multiplier can perform in the formula. To do so, proceed with the following steps.

✍️ Steps:

  • First of all, double-click on the cell, and the cell will show the formula. The Percentage character dismisses the action of the 100 multiplier. So, remove the %  character from the formula. So, the new formula would be:

=(C5/D5)*100

Edit Formula for fixing Why My percentages Are Wrong in Excel

  • Then, press ENTER so that the cell can get you the result.
  • Now, drag the Fill Handle tool to Autofill the formula right down every cell where you want to correct the formula and get the percentage value.

  • Hence, the new formula will be copied to every cell and you will now get the correct value of percentages.

Fixing Why My Percentages Are Wrong in Excel

Read More: Percentage Formula in Excel (6 Examples)


2. Format to Percent Style

For the same set of data, now your formula is mistaken for calculating the percentage value. Select a cell and check the formula in the input box. Here, the formula used is:

=(C5/D5)

Percent Style to Fix Why My Percentages are Wrong in Excel

So, it just gives a ration of the two values. If you have this type of formula, you don’t need to edit this to calculate percentages. Just follow the steps below.

✍️ Steps:

  • Here, select all the cells where the formula was encountered to calculate percentages.
  • Now, go to the Home tab> click Percent Style from the number group.

  • As soon as you click the icon, all the cells will be converted to the correct percentages format.

Percent Style for Correct My Percentages in Excel

So easy! Isn’t it?

Read More: Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)


3. Avoid Use of Percentage or 100 Multiplier Together

Sometimes, you may find that the formula seems to be correct but the percentage value is too high for the concerning case it shouldn’t be greater than 100%. Just select a cell of that type and check the formula. In this case, the formula is:

=(C5/D5)*100

Which seems to be correct. Actually, the problem is that both the Percentage Format and 100 multipliers have been used here together. There are two ways to avoid this unwanted problem.


3.1. Remove 100 Multiplier

In order to remove the 100 multipliers from the formula, just proceed with the steps below.

✍️ Steps:

  • First, double-click the cell and turn on the edit mode and delete the 100 multiplier, and press ENTER.
=C5/D5

Remove 100 Multiplier to solve why Are My Percentages Wrong in Excel

  • Now, your formula has changed. Drag the formula for the other cells to get the same results.

Read More: How to Use IF and Percentage Formula in Excel (2 Easy Ways)


Similar Readings


3.2. Change Cell Format to Number

For the same case, another solution is to change the cell format. Proceed as below.

✍️ Steps:

  • Here, just select all the concerning cells where you want to show percentages.
  • Now, go to the Home tab. You will see that in the Number Format box, Percentage is selected.

Change Format to Solve Why Are My Percentages Wrong in Excel

  • Just change the option to Number and the cell will convert the wrong percentages to the correct ones.

Read More: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)


4. Convert Number to Percentage

In some cases, the percentages values are right but the cell values lack of the % (Percentage) character you need to add the character just with values in the Excel cells.

So, proceed like the steps below to serve this purpose.

✍️ Steps:

  • Firstly, type 100 in any blank cell and press ENTER.
  • Then, copy the value just by pressing CTRL+C.

  • Now, right-click on the mouse and select Paste Special option.

Why Are My Percentages Wrong in Excel

  • After that, the Paste Special dialog box will appear on the Excel window. From the Operation group> select Divide and click OK.

  • So, the cell will show the result by dividing the formula by 100.

  • Then, drag the formula for other cells.

  • Now, from the Number group of the Home tab, select Percent Style and you will get the expected results.

Read More: Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)


Conclusion

In this article, I have tried to show some methods to solve the problem of why percentages are wrong in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafiul Hasan
Rafiul Hasan

Hey there! I am Md. Rafiul Hasan. Currently, I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our work time and made it easy for us to perform quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo