Comma or Semicolon in an Excel formula

Why does Excel expect commas or semicolons to separate the parameters in a formula? The answer can be found in this article.

Comma or semicolon ?

If you read this article, it's because one day you noticed that the separator between the parameters in any function is the comma sign or the semicolon.

Sometimes the separator is a comma

Comma as separator in formula
Comma separator

Sometimes the separator is a semicolon

Semicolon as separator
Semicolon separator

How and where to change this option?

Don't waste your time to find this option in Excel! This option is not in Excel but in the local settings of Windows 😮😮😮

Windows settings

To switch between comma or semicolon as separator, follow the next steps

1. Open your Windows settings

2. Select the Time & Language menu

Windows Setting

3. Then select Region & language > Additional date, time & regional settings

4. Click on Region > Change location

Shortcuts

You can open the same dialog box with the command line intl.cpl in the search bar of Windows 😉👍

Change your setting

When you select a country in the dropdown list, the default date and number settings are loaded.

US settings

For instance, if you select US, the date has the format mm/dd/yyyy and when you click on the button Additional settings, you can customize the decimal symbol (period or comma) but also the List separator.

The List separator is the option to write your formula with a comma separator or semi-colon separator.

And You don't have to Restart Excel to apply the settings. The change is immediate 😎😃👍

France, Spanish settings

If you don't mind, you can change the values of the country to load semi-colon as default list separator. It's the case for France or Spain. But in that case, your dates will be displayed in French or Spanish

Permanent link to this article: https://www.excel-exercise.com/comma-or-semicolon-in-excel-formula/

4 comments

Skip to comment form

    • Diogo on 24/01/2020 at 00:17
    • Reply

    Worked like a charm for me, thanks.

    • Paul on 12/01/2020 at 20:22
    • Reply

    In order for this to work, if you want to change from ";" to "," in addition to the steps detailed here, make sure that you are using "." as decimal symbol. If you are using "," as the decimal symbol it won't work.
    Thanks Frédéric for your website. It's really useful.

    • Ho on 22/10/2019 at 16:52
    • Reply

    it doesnt work

    1. What's wrong? There is no secret here. It should work

Leave a Reply

Your email address will not be published.