Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
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 '12 at 16:43
    
This may be relevant. –  pnuts Dec 11 '12 at 18:19
add comment

4 Answers 4

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

share|improve this answer
    
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 '12 at 17:52
    
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 '12 at 18:26
add comment

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

share|improve this answer
    
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 '12 at 17:37
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.