Software

Office challenge: Why is Excel creating two decimal places for input values?

In this week's Office challenge, help a frantic user discover why Excel insists on display two decimal places in all new input values.

Your first day back from holiday and a frantic user calls, describing a bizarre behavior in an Excel workbook. When she enters an integer, Excel enters two decimal places. For instance, if she enters 345, Excel turns it into 3.45.

You have her check the format and it's General. You have her change the format to Number with 0 decimal places, but Excel displays 3! You have her format a blank cell as Number with 0 decimal places. When she enters 345, Excel displays just 3.

Fortunately, within seconds, you have everything back to normal--what was wrong and how did you fix it?

Last week we asked…

How would you improve this userform? Everyone must have been on vacation last week! I'll leave this challenge open for another week so anyone who wants to participate has the opportunity!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

15 comments
crawbar
crawbar

I was going through a tutorial on Excel (I have Excel 2011) and they are entering whole numbers without mentioning the complexities discussed here. I broke my head before I ran into this blog. jprice thanks a lot for clearing out  the mistery! But after it is cleared out I find it not very convenient to uncheck the tic mark. I prefer to use a workaround: if you want enter an integer, use "general" and add 2 zeroes at the end of the number. If you want to enter decimal, use "number" and spefy as many decimal places as you need.

jprice71
jprice71

in Excel for Mac the tic mark can be found under Preferences/Edit.  Good grief, it is always a mystery with Microsoft stuff

steven.taub
steven.taub

the user may wish to check the style associated with the cells change the style back to "normal" to resolve

liljim
liljim

As an accountant, this is the normal operation of all of my Excel spreadsheets. Think about it for a second. I input thousands of data, almost everyone is a dollar and cents item. If the average value is an amount between 99.99 and 999.99 I enter on average 5 keystrokes of data, IF I DON'T NEED THE DECIMAL POINT ENTERED! I save 16% of my data entry keystrokes!!! So I go into tools/options, select the tab "Edit", and check the box for fixed decimal places and set the places to 2. Hit OK and I am set. Your frantic help call should just go to tools/options, select "Edit", and uncheck the Fixed Decimal box. Perhaps a better solution is to embrace teh new, more efficient data entry setting so dear to my profession.

stapleb
stapleb

Well, that one would have had me tearing out my hair!! I would have gone a search and eventually found the solution, but a big thank you to all who have answered the question. I have now tested it, and will be able to respond if ever I am asked. My question would be "what idiot did that to me?" Do it on your own system, but leave mine alone.

kschoeff
kschoeff

Per Micosoft: 1.Click the Microsoft Office Button , and then click Excel Options. 2.In the Advanced category, under Editing options, select the Automatically insert a decimal point check box. 3.In the Places box, enter a positive number for digits to the right of the decimal point or a negative number for digits to the left of the decimal point. For example, if you enter 3 in the Places box and then type 2834 in a cell, the value will be 2.834. If you enter -3 in the Places box and then type 283 in a cell, the value will be 283000. 4.Click OK. The Fixed decimal indicator appears in the status bar. 5.On the worksheet, click a cell, and then type the number that you want. Note The data that you typed before you selected the Fixed decimal check box is not affected.

philippe.henrion
philippe.henrion

Very easy: under Excel Options > Advanced > Editing options, the option "Automatically insert a decimal point" (Places: 2) is checked. Just unchek it and Excel will return to a more "normal" behaviour.

krama
krama

In Excel 2007 you click on the Office button Then Excel Options... Then advanced Then turn off the tick against "Automatically insert a decimal point", then click on OK If you leave the tick just type in your number with a decimal point followed by 2 zero's 345.00 displays as 345 not 3.45 3.00 = 3 not 0.03

jonathan.prater
jonathan.prater

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

BJWiltzius
BJWiltzius

Excel is creating two decimal places for input values because Fixed Decimal Places has been selected under the Tools Menu > Options > Edit tab. De-select this setting and input values will return to normal. BTW - This setting is useful if you have a lot of data entry to do and want to save key strokes.

stapleb
stapleb

Thanks liljim for explaining why you find it so useful. If I was in your position, then I would turn on the feature. As you say, for those who only want whole numbers it can be turned off. I'm guessing that it is "the best thing since sliced bread" for the accounting profession, but not necessarily the rest of the world.

Gordon Or-8
Gordon Or-8

It seems people did not read Kents post and carried on merrily telling people how to work around this in Excel 2007 and upwards. Some of us are in the less fortunate postion to have a "very long term" upgrade policy. (More fortunate of you are a techophobe that doesn't want the upgrade by choice I suppose..)

Kent Lion
Kent Lion

The answer previous to yours is correct for Excel 2003 and earlier, where there is no Advanced in Excel Options. What version are you using?

jbenton
jbenton

that would be more fortunate surely? the 2003 solution is also given above, it's just that's it's simpler and less convoluted, as with most Office 2003/2007+ scenarios

Editor's Picks