How to prevent Excel rounding numbers or adding redundant 0's?
Matthew Martinez 
I have a column of numbers that appear like this:
but the actual value of the shown cell is 20130.153334
Other values have a different number of decimal places. I don't want to add redundant 0's, so I can't just specify a particular number of decimal places to display. I really just want to treat the values as text.
I have already changed the format of the cell to Text, as the description for Text is: "Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered.". However, it clearly isn't being displayed exactly as entered! Strangely, if I hit F2 on the cell to go into edit mode, then hit enter, it is then displayed correctly. I can't do this manually for 2000+ records though!
How can I prevent the numbers being rounded?
38 Answers
If you already have a number in the cell then you can't change the format to text (...or you can but it doesn't work!). You can verify that - if data is in A1 then change format to text and use this formula in B1
=ISTEXT(A1)
The answer will be FALSE
You need to format the cells as text before you input the data - then the data will be displayed as entered
To format the column as text "after the fact" try this:
Select column of data then use
Data > Text to Columns > Next > Next > under "column data format" select "Text" > Finish
That should format the whole column as text and show the number of digits for each entry as entered.
3crtl + ASelect all - right click - format cells - custom- ########.##################### will do what you want
It won't show leading or trailing zeros
Your problem is that you're formatting the numbers as text rather than number and you don't have enough decimal places showing so it has to round.
crtl + ASelect all - right click - format cells - number - change decimal places to however many you want to show and check use 1000 separator (,)
If you want 49.93 instead of 49.94, one way to guarantee it is with a formula like:
=INT((41200/825*100))/100What happens here is the decimal in your result is moved two places to the right, then INT strips off all decimals giving you 4,993, then it's divided by 100 to give you 49.93000, and you can format it to 2 places to get 49.93.
When doing this be careful with your parentheses.
1This site explains, how to set your precision from rounding.
short version:Ctrl-a to mark all cells, right click on a cell -> choose format cells -> select your precesion
As a person whom in a similar situation I've reached to following resolution. crtl + A Select all , crtl + C Copy all. Then in a new sheet Select all, Turn all cells into Text. You may use, "Quick Access Toolbar", "Home" Tab, "Number" Part . Switch to "Text" from "General". Then you may Paste all here first, to have the previous formatting , then again with Paste Values to have the values as they were. If this doesn't give satisfying results. Paste all into an empty Word document, (If you need any replacing do it here, for Word treats numbers as text) then again Select All and Copy All from that Word document. Before, pasting back turn all cells into text format. And finally, when pasting (into A1, with right click) use "Match Destination Formatting" option. I think this will do.
If 41200 ÷ 825 = 49.93939393, and you wish that the figure only 49.93 is not made round, then use =round(41200/825,4) and select format - cell - number decimal places select 2.
Your figure will be show 49.93 instead of 49.94 and be rounded off by the computer.
1I found this online and it worked for me very well - You can frequently prevent floating point rounding errors from affecting your work by setting the Precision as displayed option before you apply a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.
Note: Using the Precision as displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed precision will maintain the accuracy of your data.
1.Click File > Options.
In Excel 2007: Click the Microsoft Office Button Office button image , and then click Excel Options.
2.Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.
3.Click OK.
4.In the worksheet, select the cells that you want to format.
5.On the Home tab, click the Dialog Box Launcher Button image next to Number.
Excel Ribbon Image
6.In the Category box, click Number.
7.In the Decimal places box, enter the number of decimal places that you want to display.
Tip: To minimize any effects of floating point arithmetic storage inaccuracy, you can also use the ROUND function to round numbers to the number of decimal places that is required by your calculation.
