PDA

View Full Version : Excel number format



FoxyMX
03-02-2010, 04:45 PM
Excel has recently started mucking around with the number formats in my files. When I type in 468 it will display 4.68 until I change the format to Text. Nothing else will make it display 468 but it will display 468.00 if entered like this. It happens in new spreadsheets and old ones.

I haven't changed any settings and can't see how to make it behave like it used to. Any ideas? :confused:

Sweep
03-02-2010, 05:17 PM
Select the column you want and change it to whatever you want to appear. If you want 468 to appear then one would use text.

Which version of Excel are you using?

FoxyMX
03-02-2010, 05:54 PM
Select the column you want and change it to whatever you want to appear. If you want 468 to appear then one would use text.

I don't want it to be text, though, because I need to add the numbers up.

Version is Excel XP/2002.

Sweep
03-02-2010, 06:07 PM
OK. Then you want number format rather than text. There are several number formats to choose from.

Is this currency? If so you would use two decimal places and entering 468 should give 4.68.

You are correct in that you can't add text.

Paul.Cov
03-02-2010, 06:21 PM
Just a hunch... I'm not sufficiently familiar with Excel to stick my neck out.

If it is affecting a multitude of pre-existing files, then it is probably a 'universal' setting within the Options section.

Try opening Excel without actually opening any spreadsheets, then look for options or preference settings that may relate to number format or decimal places.

Sweep
03-02-2010, 06:37 PM
Just a hunch... I'm not sufficiently familiar with Excel to stick my neck out.

If it is affecting a multitude of pre-existing files, then it is probably a 'universal' setting within the Options section.

Try opening Excel without actually opening any spreadsheets, then look for options or preference settings that may relate to number format or decimal places.

The pre-existing files worried me too but there is no global setting for all cells in a spreadsheet other than regional and this should not affect all cells other than dates.

coldot
03-02-2010, 08:15 PM
Maybe this relates:
http://excel.tips.net/Pages/T002755_Fixing_the_Decimal_Point.html
There is an option in Excel 2003 and 2007 to have automatic decimal point inserted. Maybe someone turned it on by accident?
Not sure why it would affect old spreadsheets though.
When you say it happens in old ones - do you mean that numbers previously entered now show with wrong decimal place? Or only if you try adding data to the old spreadsheet?

FoxyMX
03-02-2010, 09:14 PM
That looks promising, coldot. Someone else is using that PC at the moment but I'll check it out at the first opportunity.

Numbers previously entered have not changed, only new numbers entered, even in cells where the number was previously correct and changed.

FoxyMX
04-02-2010, 12:24 PM
That worked. :cool: :cool:

Thanks so much for that coldot. I'm not sure whether I changed that setting a while ago without realising what it did and forgot about it or what, but now my hair can start growing back again!! :p

Sweep
04-02-2010, 12:49 PM
Thanks for that. I learned another new thing today.

coldot
04-02-2010, 07:37 PM
You're welcome.


That worked. :cool: :cool:
.... but now my hair can start growing back again!! :p

Wish I could say the same for mine!