Excel Expression Definition

A couple of days ago I sent out a survey about the name of an argument in one of the new Excel 2016 functions.

The response was huge and I’m so grateful to the 1300+ members who managed to respond before I closed it. I apologise if you missed out. I had to close the survey so I could analyse the data.

Anyhow, I enjoyed reading every response, and thanks for the kind words some of you left in the additional comments 🙂

What I gathered from your responses is that many of you don’t know some basic terminology around Excel functions and formulas so let’s cover that quickly before we move on.

Anatomy of a Formula

A formula always starts with an equals sign and can be as simple as =2+2 or =C1+C2, or it can contain a function like VLOOKUP, or even nested functions.

Parts of a Formula

A formula can contain any or all of the following:

• Functions (IF, VLOOKUP, SUM etc.)
• References (cell references like A1, named ranges, structured references to Tables etc.)
• Math Operators (+ - / * > < = ^)
• Constants (text or numbers)

For example, the IF formula below contains the function name, cell references, operators and constants:

Parts of a Function

A function has a name and then arguments surrounded by parentheses.

Optional arguments are surrounded by square brackets e.g. [value_if_true] and [value_if_false] are optional arguments in the IF function.

Typically Excel argument names are somewhat descriptive of their purpose, although I’m sure most agree that they could be better.

What you can enter for a particular argument will depend on the function and purpose of that argument.

For example, the IF function’s logical_test argument definition is:

“Any value or expression that can be evaluated to TRUE or FALSE”

Which leads me on to the survey question.

Survey Question

Here is the survey question I asked:

If you saw an Excel function that had an argument called ‘expression’, e.g.:

=FUNCTION(expression, argument2, argument3…)

Would you know what that meant? Would you know what you could put in that argument? Or would you scratch your head and say “hmmm, I wonder what they mean by ‘expression’”?

I intentionally didn’t reveal the function name or other argument names because the term expression can mean many things and I didn’t want to limit the answers based on what this particular function might use.

I categorised each survey response into those who ‘do know’ what ‘expression’ means in the context of a function argument, and those who ‘don’t know’. And I was pretty lenient on awarding responses with ‘do know’. If there was one example of an expression, or even a vague description, then I awarded it ‘do know’.

However, the majority did not know, or more specifically, the term ‘expression’ wasn’t self-explanatory in the context of an Excel function.

Survey Answer – Excel Expression Definition

Drum roll….I know many of you are itching to know what the definition of ‘expression’ is in the context of Excel.

An expression is pretty much anything that evaluates to a single value, or in the case of array formulas, a range of values/cell references.

What the….that’s no clearer. Ok, let me give you some examples of expressions:

Note: This is not an exhaustive list and there may be more examples, but since there’s no documentation on this term for Excel (that I could find), I can’t be sure.

Survey Results

The 1344 responses are broken down as follows:

Use the double-headed arrow full screen icon in the bottom right of the image below to zoom in on the visualisations. Click on the chart columns to cross filter the other charts.

It is clear from the results that using the term ‘expression’ for a function argument name does not help the typical Excel user know what is required.

Comments like ‘it’s ambiguous’ and ‘it’s geek speak’ were scattered throughout the responses.

The term ‘expression’ is occasionally found in Excel documentation to describe what an argument can be, but has never been used as an argument name before.

Excel argument names typically describe what they do, or what they represent, although they could be better.

It’s my opinion that Microsoft should maintain this naming convention which is closer to layman’s terms, as opposed to shifting towards a technical language like we now find in the new DAX formula language used by Power Pivot.

There were only two respondents who said I shouldn’t encourage Microsoft to change the name of the argument, and that they couldn’t think of a better name. I might add that these people had programming experience and ‘expression’ is a common term used in programming.

Excel’s success has been its ease of use. People with no prior programming experience can build some pretty sophisticated models. It’s my fear that if Excel function language gets overly technical (some would say it’s already technical), that it will deter users from learning and discovering new functions.

Thanks

Thanks again to all those who took the time to complete the survey. It was enlightening and I enjoyed getting to know you a bit better in the process. Again, I apologise if you missed out.

Thanks to fellow MVP, Kevin Jones, who did extensive research on where the term ‘expression’ had previously been used in Excel terminology.

More on Formulas

If you’d like to go back to basics and understand the fundamental rules behind formulas check out this Overview of Excel Formulas.

Next week I’ll reveal two of the new formulas in Excel 2016. If you can’t wait, the function I based this survey on is SWITCH. Microsoft have recently updated the documentation on SWITCH (because I complained) to make it clearer, but I still disagree with the first argument being called ‘expression.

I’ll be forwarding the survey results to Microsoft soon. I’ll let you know if any changes arise as a result of the survey findings.