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
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
- 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.
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)
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.
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
- 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
- How to Calculate Percentage of Percentage in Excel (2 Examples)
- Calculate Annual Growth Rate in Excel (3 Methods)
- How to Calculate Percentage of Completion in Excel (3 Methods)
- Calculate Gross Profit Margin Percentage with Formula in Excel
- How to Calculate Absenteeism Percentage in Excel (2 Easy Ways)
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.
- 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.
- 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
- Calculate Percentage in Excel Based on Cell Color (4 Methods)
- How to Calculate Percentage of a Number in Excel (5 Easy Ways)
- Calculate Total Percentage in Excel (5 Ways)
- How to Calculate Average Percentage in Excel (3 Easy Methods)
- Percentage Difference Between Two Percentages in Excel
- How to Use Excel Formula to Calculate Percentage of Grand Total
- Calculate Contribution Percentage with Formula in Excel