Why ‘Your Version’ of Excel only Accept Semicolons in Formulas instead of Commas

Today, I learned how Region settings in Windows can truly botch up Excel.

A reader of one of my Pulitzer Prize worthy books sent me an email stating that the example formulas aren’t working.

After a bit of back and forth, he sent me this:

 

Huh? Since when does Excel use semicolons instead of commas as argument separators?

Well, in researching this anomaly, I was surprised to find

lots of examples where people state that “their version of Excel” uses semicolons instead of commas.

That makes no sense!

 

Well, it turns out that a Region setting causes Excel to error when entering commas in formulas.

If you’re one of those folks who have been happily entering formulas with semicolons, stop! You can fix this.

Here’s how:

 

1. In Windows, go to the Control Panel (Start->Control Panel).

2. Select Clock, Language and Region.

 

3. Select the option for number format.

 

4. In the Region and Language dialog box, the Additional Settings button.


 

5. Change the List Separator property to a comma, then click the Apply button,

 

Now you can use Excel like everyone else in the world.

7 thoughts on “Why ‘Your Version’ of Excel only Accept Semicolons in Formulas instead of Commas

  1. Ola

    No, no no. You, are totally wrong.
    Semicolon is way better then comma. Why? Well anyone that use a thousand separators.
    Comma is as ancient as Fahrenheit, Miles, and other Stone stuff. The date format is also messed up. It should be YYYY-MM-DD. There is no hierarchical logic in anything else.
    And we Never use conversion tables. Well with one exception – old history books.

    So welcome to the “2000-number” (not 21st century) 😉
    …or was it … on the other side of the wall 😀

    ….
    Just joking – I’ve enjoyed your blogg for many years.

  2. Ghazanfar Abidi

    Where this has annoyed me the most are CSV files. I used to work with someone based in Spain where they needed the CSVs separated by semi-colons instead. I ended up creating a .reg file to quickly switch between the regional settings.

  3. JimC

    Thanks for the heads up – good info when working in a Global environment.
    It does seem odd the definition of internal syntax could be affected by what is essentially a formatting option.

  4. Stuart McKim

    This is because some regions use a comma as the decimal symbol. If your region does, then the software needs to use something else (semi-colons) in formulas to distinguish it.

Leave a Reply

Your email address will not be published. Required fields are marked *