This article explains the function list separator used in Excel functions.
By default, Excel uses the list separator defined under regional settings in Control Panel. The US English version of Excel uses a comma (,) for list separator by default, while other international versions may use a semicolon (;).
This impacts how functions are entered in Excel. In the United States, and countries like Canada, Australia, United Kingdom, etc. functions are entered with arguments separated by commas. In other countries like Spain, France, Italy, Netherlands, Germany, functions are entered with semicolons. For example, the SUM would be entered like this in the US:
Note: Excel automatically translates the separator in many cases. If you open a worksheet created in the United States, Excel will automatically (and silently) change commas to semicolons as the file is opened. Likewise, if you change the list separator while a worksheet is open, Excel will change the separator used in formulas in that worksheet.
Formula error with wrong separator
If you try to enter a formula with the wrong list separator, you'll get an error that says "There's a problem with this formula" , and a dialog something like this:
Notice nothing in this dialog says anything about list separators :)
This is an especially common problem if you are in another region and copy and paste an example formula from a US website.
In general, the best solution is to just change the formula to use the list separator used in your region.
Regional settings for list separator
On Windows, you'll find this setting at:
Control Panel > Region & Language > Additional settings > Change date, time, or number formats > additional settings
Again, we recommend that you leave this setting alone and just use the system separator already defined to enter new formulas.
Excel has its own list settings at Options > Advanced > Use System separators
Here you can see Excel inherits the regional settings by default. If for some reason this box is unchecked, Excel won't inherit the regional list separator described above.
A function argument is a specific input to a function. For example, the VLOOKUP function takes four arguments as follows: = VLOOKUP ( value , table , col_index , [ range_lookup ]) Note most arguments are required, but some are optional. In Excel,...
An Excel function is a pre-built formula with a specific purpose. Excel provides hundreds of functions in various categories like dates and times, text, statistics, lookup, financial, engineering, etc. Most Excel functions require specific inputs,...