7

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!

Improve this question
1
  • 1
    Did you try highlighting the cells/row/column you want in a specific format and manually set it in the Right Click > 'Format Cells' > Number (tab)? I have not had it revert that formatting unless I copy/paste in other pre-formatted data.
    – nerdwaller
    Dec 11, 2012 at 16:43

7 Answers 7

Reset to default
5

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."

Improve this answer
2
  • I agree. I've set specific number formats to ranges of cells as appropriate. Very few, if any, cells have a General format. The only reason I'm changing formats to one cell at a time is to correct the Excel auto-change that occurs each time I modify a cell's contents to correct a formula or change a hard coded number (in each of these actions, Excel messes up the selected format).
    – user179555
    Dec 11, 2012 at 17:52
  • 1
    After testing different ideas, I think this glitch is only present with specific "Custom" number formats (see the custom format list - primarily those without decimal places). When I use some of the Accounting, or other formats, the auto-reformat doesn't occur. I'm going to focus on trying to solve it by creating a new Custom format and see if I can eliminate the problem. Thanks for everyone's help. Let me know if anyone replicates the problem with any of the MS provided custom formats in your version of Excel - I'm wondering if my application load caused this since it seems isolated).
    – user179555
    Dec 11, 2012 at 18:26
3

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.

Improve this answer
2

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".

Improve this answer
1
  • The OP specifically said that he did this and it didn't help
    – Dmitry Grigoryev
    May 26, 2015 at 20:36
1

I have same problem and fixed with this blog.

https://kb.wisc.edu/helpdesk/page.php?id=1567

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

Improve this answer
1
  • 2
    Please include the relevant details from the blog; links go dead. Thanks.
    – Arjan
    Oct 12, 2015 at 9:58
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."

Improve this answer
1
  • Just never ever ever change that cell again.
    – Hippyjim
    Jan 23, 2018 at 14:49
0

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

Improve this answer
1
  • Tried that and it changed to general format with 2 decimal places. The Excel auto-format action adds the 2 decimal places, but keeps all other aspects of my specific number format. I also tried to select the column and set the format using Right Click > Format Cells....and Excel still changes the format when I access or alter the cell contents. I'm definitely puzzled by this one.
    – user179555
    Dec 11, 2012 at 17:37
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .