2 Ways to Stop Excel from Rounding up Percentages
When working with data in Microsoft Excel, it’s essential to maintain accuracy in the calculations to make informed decisions. However, Excel has a tendency to round up percentages to whole numbers, which can sometimes lead to errors. In situations where precision is critical, it’s important to prevent Excel from rounding up percentages. This article will explore two practical methods that you can use to stop Excel from rounding up percentages, ensuring your data is accurate and reliable.
Introduction to the Dataset
In this dataset, you can see I have some company names and their yearly revenue for two years, 2021 and 2022. The annual growth is calculated in the column Growth in percentages.
As I applied the percentage format to the column, Excel automatically rounded up the numbers. It makes the actual growth rate unclear.
So I will show you how I stop Excel from rounding up the percentages automatically.
Easiest Way to Stop Excel from Rounding up Percentages
To prevent percentages from being rounded up, change the decimal place by using the Increase Decimal button. The Increase Decimal button will allow you to increase the number of decimal places after the decimal point in a selected cell or range of cells.
Usage Guide
Step_1: First, choose the cells that contain the percentages you want to modify.
In this case, I’ve selected the range D3:D17.
Step_2: Then go to the Home tab.
Step_3: Finally, click on the Increase Decimal button located in the Number group.
Final Result
In this case, I wanted three decimal places after the decimal point. That’s why I clicked on the Increase Decimal button three times.
Alternative Way: Convert into Text to Stop Excel from Rounding up Percentages
Another way to stop Excel from rounding up percentages is by using the Text to Columns feature to turn your values into text. By doing so, Excel will retain all the decimal places after the decimal point. Additionally, even though those numbers are now saved as text, you may still perform calculations on them just like you would on actual numbers.
Usage Guide
Step_1: At first, select the cell range (Here, cell D3 to D17).
Step_2: Then go to the Data tab.
Step_3: Now click on the Text to Columns command from the Data Tools group.
You will see a dialog box pop up on the Excel sheet.
Step_4: Here, select the Delimited option as the file type.
Step_5: Hit Next.
Step_6: Now, skip the Delimiters list.
Step_7: Just, click on the Next button.
Step_8: Now, select Text from the Column Data Format.
Step_9: Finally, click the Finish button.
Now you will see that Excel has restored all the decimal places after the decimal point.
If you are worried about the green little triangle signs, these are alert signs.
Green Error Signs
Green error signs appear to alert you to certain actions. But they don’t interfere with any functionality on the spreadsheet. When numbers are saved as text in a cell, Excel often detects this and displays an alert next to the cell.
When you see a green error sign in a cell, you can click on the cell to see a drop-down menu that provides information about the error and suggests potential solutions.
You can remove the error signs by following the below steps.
Step_10: Click on the yellow alert button located in the top-left corner of the top cell.
Step_11: Then select Ignore Error from the drop-down menu.
Final Result
You can see Excel has removed the Green Error sign, and you are good to go.
Note: If the cell or cell range containing percentages has applied formulas, this method will not be effective. To make it work, first, convert the formulas into values and then apply the technique.
Conclusion
I’ve shown 2 techniques for stopping Excel from rounding percentages. I hope you find this article helpful. Moreover, feel free to ask any questions you may have about this subject in the comment box below. For more articles like this one on Excel, please visit our Blog page. Thank you, and have a good day!
