Why does Excel 2010 automatically reformat numbers?

Excel 2010 is automatically reformatting numbers/cells where a specific number format has already been applied. Whenever I access or modify the cell contents, Excel reformats by changing the number of decimal places to 2. I've already gone into file \ options \ advanced \ and made sure that the option to automatically add decimal places is unselected, but Excel still automatically makes the change. Very time consuming to continue reselecting number formats each time the cell contents are changed/accessed.

Any ideas?

Thx!

1

7 Answers

You don't have to change the cells' formatting one at a time. You can apply the same formats to whole columns, ranges or even sheets. If you know that you'll have number in all the cells, make sure you set them to one of the number formats. The General formatting option is treated by Excel as a sort of "no formatting" option, so it will often try to guess what specific format you want it in.

Alternatively, you may want to look at this answer and see if it applies to your situation:

Excel assumes you have not applied a format if you use general, so it tries to find the best format for the data you entered. To disable this, go to File => Options => Advanced and uncheck the option for "Extend data range formats and formulas."

2

I had a similar issue.

I had numbers formatted as percentages with one or two decimal points. Whenever I changed the cell value by adding/subtracting a % (e.g. the formula is =A2-.2%), it reformatted the cell with 15 significant digits. Ugh!

I tried the tips above (e.g. deselecting Extend data range formats and formulas) and they didn't help. I tried re-formatting the cells as General, then applying the % format, and then updating the formula. Same problem. I went into custom formats and deleted the % format with 15 decimal points, but as soon as I re-edited the cell's formula, Excel magically re-created the 15 digit custom format and applied it.

I finally found a solution:

When adding/subtracting, don't use the % in the formula. Instead of =A2-.2%, use =A2-.002.

In Excel Options, go to Advanced and uncheck "Automatically insert a decimal point"

For versions Excel 2003 or earlier, under Tools menu > Options > Deselect "Fixed Decimal Places".

1

I have same problem and fixed with this blog.

Office Excel 2007/2010/2013

Step 1.

Excel 2010/2013 - Go to File -> Options
Excel 2007 - Go to Office Button -> Excel Options

Step 2.

Select Advanced in the left hand column.

Step 3.

Uncheck Automatically insert a decimal point.

Step 4.

Click OK

Office X/2001/XP/2003

Open Excel.

This step is dependent on the version of Excel you are using.

Excel 2003 - Go to Tools -> Options
Excel XP - Go to Tools -> Options
Excel 2001 - Go to Edit -> Preferences
Excel X - Go to Excel -> Preferences
Select the Edit tab.
Uncheck Fixed Decimal Places:.
Click OK.

Thanks may it's help other

1

Got it! right click on whole column OR just the cell you're working on..."format cells" then "number" then make "decimal places" to 0, (mine read 2), and then "ok."

1

Try right-clicking on the offending cell, clicking Format Cells... and selecting the General format.

1

I had the auto format problem described here; solved by unchecking the options for "Extend data range formats and formulas" and “Use system separators” for decimals and thousands.