Number format in charts automatically changed to custom (Bug?)

[Tobbe]

Hey,

I got the following problem in Excel 2010 (Win7): My data is formatted as Number with two decimal places. Now when I create a chart out of this data, a strange thing happens. The number format on the axis (and labels) is always automatically changed to Custom (0.0). So, for example, the value 25.8 in raw data becomes 2.6 in the chart (Screenshot: https://www.dropbox.com/s/8foxzl89fu5f93z/NumberFormatIssue.png)

Do you have any idea what is happening?

 

[Jon Peltier]

Is the default chart a custom template that you saved? Otherwise there's no reason for the format to be anything other General or whatever is in the data range.

 

[Tobbe]

I'd guess it is the standard chart, I have never saved any custom template. As I use a corporate computer I am not sure about globally customized settings, though. But it should be standard.

Using 2 or 0 decimal places in the data range works fine, everything else gets messed up. I even tested other formats, and the result is:

1 decimal (origininal problem): 25.8 --> 2.6
3 decimals: 25.800 --> 0.026
4 decimals: 25.8000 --> 0.0026
and so on...

I agree: I cannot imagine a reason for this weird behavior...

 

[Jon Peltier]

Using 2 or 0 decimal places in the data range works fine, everything else gets messed up. I even tested other formats, and the result is:
1 decimal (origininal problem): 25.8 --> 2.6
3 decimals: 25.800 --> 0.026
4 decimals: 25.8000 --> 0.0026
and so on...

Weird. In the first case, the decimal point is moved (like dividing by 10). In the second, it's like the number was divided by 1000. In the third, by 10,000. At first I thought it might have been a comma/period switch like you see in different localizations. But I don't know what could be causing this strange behavior.

 

[Tobbe]

Localization was a hint... I am using "." as decimal separator and " " as thousands (not system settings).
If I switch to system settings ("," as decimal separator), everything works fine...

But I still want to use ".", as I paste data ranges from other programs that use "." as separator, to get the right format when pasting...

 

[Jon Peltier]

The only ways to get the period as a decimal separator are through system settings or by overriding them. You can't get it from a number format if some other character is set as the decimal separator.