What are function arguments in Excel? Definition and syntax rules

Microsoft's definition of a function carries the point: they "perform calculations by using specific values, called arguments, in a particular order, or structure." The function is fixed; the arguments are the part you supply, and almost every formula that returns a plausible but wrong answer got one of them wrong. What a function is in the first place belongs to Spreadsheet functions.

Required, optional, and the square brackets

Exceljet's definition is the compact one — "a function argument is a specific input to a function" — and its VLOOKUP illustration carries the convention every piece of spreadsheet documentation uses:

=VLOOKUP (value, table, col_index, [range_lookup])

"Note most arguments are required, but some are optional. In Excel, optional arguments are denoted with square brackets." The brackets are notation, not something you type. A trailing ellipsis means the pattern repeats: =COUNTIFS(range1,criteria1,[range2,criteria2],...) says "you can optionally add additional arguments in pairs: range3/criteria3, range4/criteria4, etc."

Microsoft's AND is a good specimen of both conventions and of where the limit sits. Its syntax is AND (logical1, [logical2], ...), where logical1 is required and logical2 is "Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions."

You do not have to remember any of this. "A tooltip with the syntax and arguments appears as you type the function. For example, type =ROUND( and the tooltip appears" — with the caveat that "tooltips appear only for built-in functions", so a custom function you or a colleague wrote gets none. The Insert Function dialog goes further, showing "the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula."

Position is the meaning

Arguments in built-in spreadsheet functions are positional. Nothing in =VLOOKUP(G5,B5:D16,3,0) labels the 3 as a column index — it is the column index because it is third. Swap two arguments of compatible types and you get a number rather than an error, which is the failure mode worth fearing: a col_index of 3 where 4 was meant returns the wrong column silently. This is a large part of why INDEX MATCH is preferred to VLOOKUP on wide tables, since the position it depends on is a range you can see rather than a count you have to keep.

Named arguments exist, but only for functions you write yourself. Ben Collins describes them for Google Sheets named functions: "one big difference between a named function and a regular function is that we put placeholders into the formula as inputs, instead of references to cells or ranges", and each placeholder gets a name, a description and an example value that show up when someone uses the function. Built-in functions in either application have no such thing.

What an argument is allowed to be

Broader than most people assume. Microsoft: "Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references … Arguments can also be constants, formulas, or other functions." The binding rule is the next sentence: "The argument you designate must produce a valid value for that argument."

Argument type	Looks like
Constant	0.2, "EMEA", TRUE
Cell reference	B5, $B$5, Orders[Amount]
Range or array	B5:D16, {1,2,3}
Another formula	B5*C5
Another function	SUM(B5:B16)

That last row is where types start to matter. "When a nested function is used as an argument, the nested function must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, the nested function must return a TRUE or FALSE value. If the function doesn't, Excel displays a #VALUE! error value." Excel also caps the depth: "a formula can contain up to seven levels of nested functions."

Some functions quietly forgive the wrong type instead of complaining, which is worse. AND's documentation is explicit: "if an array or reference argument contains text or empty cells, those values are ignored." A range of conditions half-filled with text still returns TRUE or FALSE, from a smaller test than you intended.

The comma is not universal

The character between arguments is a regional setting, not part of the function. Exceljet: "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 (;)." So the same sum is =SUM(A1,C1,E1) in the United States and =SUM(A1;C1;E1) in Italy.

Files travel without trouble, because "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." Pasted text does not. Typing a formula with the wrong separator produces "an error that says 'There's a problem with this formula'", and, as Exceljet notes, "nothing in this dialog says anything about list separators" — which is why this is "an especially common problem if you are in another region and copy and paste an example formula from a US website." Excel's own switch for it is Options → Advanced → Use System separators.

How Formula Foundry handles this

Arguments are where Formula Foundry's visual builder does its clearest work. It renders a formula's call tree as a form: every argument labelled with its own name, nested calls shown as nested groups, fields validated as you fill them, and a live preview of what you are assembling. For an inherited formula, seeing col_index on its own labelled row is a faster way to notice it says 3 than reading it out of a comma-separated line.

The editor handles the separator problem rather than tripping over it. Parsing is locale-aware: a semicolon list separator in a European locale is understood as one, and when it is the wrong separator for the context the parser says so instead of returning a single generic complaint. Errors from one pass are collected together with targeted messages — a missing operator, a malformed name/value pair — with best-effort recovery so one mistake near the start does not hide four more after it. Auto-suggest covers function names and range names as you type.

The honest limit is the one that matters most here. Formula Foundry parses, formats, explains and translates; it does not evaluate your workbook. It can tell you a call is malformed, that a bracket closes in the wrong place, or that a name is not one it can see — it cannot tell you that a perfectly well-formed third argument points at the wrong column. That gap is what the AI assistant's plain-English explanation of a formula is for. The features overview shows the builder, the editor and the assistant in Google Sheets and Excel.

FAQ