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
Sometimes the separator is a semicolon
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
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
As you can see in the picture, the list separator is a comma. Also the date format is M/d/yyyy (month, day, year)
Now, when you write a formula, the separator between each arguments is a comma. And for the date as well, automatically your dates will be written with the US date format.
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
Kay
Batman
Nilesh
mukesh dudi
Me
samrrr
Johnny Santos
Michael Jonker
Diogo
Paul
Katja
Ho
Frédéric LE GUEN
06/01/2023 @ 01:16
Thank you, very useful tip
06/07/2022 @ 18:26
Thanks man!! very clear explanation.
02/03/2022 @ 11:09
Thanks.
22/11/2021 @ 17:36
very ususe full
29/08/2022 @ 13:14
You're a lifesaver. I have no idea it is using comma in English language.
04/07/2021 @ 18:27
usefull
30/04/2021 @ 00:40
You've saved my time! thanks a lot
15/10/2020 @ 14:57
Works great, thanks!
I initially searched all over excel. Still a pity this option cannot be easily set in excel in the context of csv files only. Sometime I need semicolons, sometimes I need commas, and it is often not up to me to decide...
In most cases it should be rather trivial to determine if a csv is comma based or semicolon based.
24/01/2020 @ 00:17
Worked like a charm for me, thanks.
12/01/2020 @ 20:22
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.
16/06/2021 @ 14:19
Thank you Paul , that is very good advice that helped me
22/10/2019 @ 16:52
it doesnt work
23/10/2019 @ 17:13
What's wrong? There is no secret here. It should work