Excel Automatic Rounding

Question

Excel is automatically rounding numbers I type such as 12.5 and 13.5 up to the next whole number. I have tried changing the formatting to text, general etc. but it is still doing it and only in two columns in my spreadsheet. I also tried putting an apostrophe before the number but as I am using data validation on those cells it tells me that the value is not valid. How can I fix it?

Answer 1

The file was very helpful in defining the issue.

The problem is that the columns are too narrow to display the decimal number.  So Excel displays what it can, how it can.

The fix will be to widen the column or make the font smaller, so there is room for the entire value/string to display.

Answer 2

It is hard to know exactly what you are doing from what you write.  But if the value in the formula bar is 12.5, that is the value that excel is storing in that cell.

If you have actually changed the formatting to text, the string 12.5 should appear in the cell and be left-justified.  So either what you did didn't really change the formatting, or you have a macro running which is changing the formatting back to number with zero decimals.

Also, you write that you are using data validation, but you don't write what the data validation rules are.

If you can't figure out how to change the formatting and "make it stick", I would suggest uploading a copy of the misbehaving workbook (sanitized as necessary to remove sensitive information) to a sharing web site (e.g. Skydrive), and post the link here so we may examine the problem more thoroughly.

Answer 3

I have also just found that the rounding doesn't appear when I open the spread sheet in an older version of excel.....HELP!!

Answer 4

It does appear in the formula bar as 12.5 and 13.5, but when I tried to change the formatting to text, it kept rounding. Shouldn't that have been enough?

Answer 5

If it is only doing that in two columns, it is probably your format of those columns.  What is the value that appears in the formula bar?  If that value is 12.5 or 13.5, then you are seeing the results of having the cell formatted as number with zero decimal places.