Excel decimal Rounding?

Question

How can I round a number in excel such as by formatting it so that the actual number changes and not just it's appearance in the cell.

For example, if I have 229.729599999998 in a cell and I format the cell to only show 4 decimal places it would now show 229.7296 in the cell but the value of the cell would still be 229.729599999998 so if I were to do any calculations excel would use the longer decimal number as oppose to the 4 decimal number.

Is there way I can make it so that not just the appearance of the number in the cell shows 4 decimal places but so that the actual value of the cell will reflect the 4 decimal number so that the cell and the value in the formula bar will show 229.7296 and not 229.729599999998.

I know I can probably use a function or formula in an adjacent cell to round the number but I just put together a huge spread sheet and it would take a really long time if I had to do the same thing for all the cells in my spread sheet. Any ideas?

Answer 1

Just copy the numbers into a sheet and save that as a new CSV. Then close the CSV and open it again and your numbers are rounded...

Answer 2

There is another option: uset the 'Precision as displayed' setting (found under calculation options).

From excel help

Permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed.