This post originated from an RSS feed registered with .NET Buzz by Anand M. Original Post: Excel and Number Format Feed Title: .NET From India Feed URL: http://www.dotnetindia.com/index.rss Feed Description: Your daily dose of .NET From India Latest .NET Buzz Posts Latest .NET Buzz Posts by Anand M Latest Posts From .NET From India

This post was actually triggered by a mail from a co-MVP, Saurav Basu, on some peculiar behavior he was facing with the way Excel formats numbers. His problems was

According to MS KB articles, One may change the Office Excel 2003 thousand separator by changing the Window's regional settings,

Now here is my config, Windows XP SP2 (Installed from Integrated WinXP SP2 Disk), and Office 2003 SP (Installed from Integrated Office2k3 SP2 Disk)..

The problem is, I have changed the Regional Settings in Windows XP to make sure number system separators works like this, 12,34,56,789.00 (India), but I don't see the reflection and Office 2003, Excel. !! Excel 2003 is still placing the numbers, the format, 123,456,789.00.

Since I was running only Excel 2007 (on Vista), I decided to check this out with that.

Surprise, surprise, I change the number format in Advanced Customization and nothing happens in excel (value formatted as Number, with the show thousand separator option chosen). Just out of curiosity I went ahead and changed the settings for the currency format in Vista and Excel changed the number format at once (no close/reopen, looks like they were listening for system setting change events, which is a good thing).

So I guess Excel's number format is based on Vista's Currency format and not its actual Number Format. But do note, this is only for the number grouping style. For all other stuff like the decimal separator, it seems to be taking the settings in the actual Number Format section.

My guess, some programmer just read the wrong registry entry or parameter or there is some documentation in an obscure corner of the microsoft site on why it works this way, but I have been unable to find it:)