Operators

What operators are

An operator is a symbol in a formula that tells Excel what to do with the values on either side of it. For example:

• the + in =B2+C2 adds
• the = in =B2=C2 compares
• the & in =B2&C2 joins text
• the colon in B2:C9 turns two cell addresses into a range

Besides numbers, texts, references, names and function names, a formula consists mostly of operators and brackets.

Excel has four families, each with its own kind of result:

• arithmetic operators: a number
• comparison operators: TRUE or FALSE
• one text operator: text
• the reference operators: a reference

When several operators stand in one formula, Excel does not read from left to right but in a fixed order, which is the part that causes most wrong results.

Arithmetic operators

Arithmetic operators work on numbers and give a number. The percent sign deserves attention: 21% is not a format, it is the number 0.21, so =B2*21% and =B2*0.21 are the same formula.

Text that looks like a number, such as "100", is converted in arithmetic, so ="100"+1 gives 101. Text that cannot be converted gives #VALUE!.

Comparison operators

A comparison gives TRUE or FALSE, or passes on an error value if one of the values is an error. Text is compared without regard to upper and lower case, so ="vrk"="VRK" is TRUE.

Between types the order is fixed: every number is smaller than every text, and every text is smaller than TRUE. That is why ="a">100 is TRUE.

A comparison does not convert text to a number, which is the opposite of arithmetic. ="100"=100 is FALSE because one side is text and the other a number, while ="100"+0=100 is TRUE.

The same sign has two jobs in a formula: the first = only announces that a formula follows, any further = compares. In =A2=B2 Excel compares A2 with B2 and shows TRUE or FALSE.

=C2>=E1

=C2>=E1

=COUNTIF(C2:C100, ">" & E1)

=COUNTIF(C2:C100; ">" & E1)

Joining text with &

The ampersand joins two values into one text: =A2&"|"&B2 puts the contents of A2, a bar and the contents of B2 behind each other. It is the usual way to build a key from two columns or a search pattern with an asterisk.

Numbers and dates are turned into text on the way, so the result is always text, even if it looks like a number.

The conversion uses the regional setting of the computer that calculates. A number such as 1089.5 becomes "1089.5" under English settings and "1089,5" under Dutch settings, so a key built on one machine can differ from the same key built on another.

A date joins as its serial number (31 Dec 2025 becomes "46022"); use the TEXT function when you need a readable date inside the text.

=A2 & "|" & B2

=A2 & "|" & B2

=TEXT(A2, "dd-mm-yyyy") & " " & B2

=TEXT(A2; "dd-mm-yyyy") & " " & B2

Reference operators

Reference operators combine cell addresses into a larger reference instead of calculating with values:

• the colon makes a range
• the list separator makes a union: one reference built from separate pieces
• a single space makes an intersection: only the cells the two references share

The union operator is the list separator of your regional settings: a comma under English settings, a semicolon under Dutch settings. It is the same character that separates function arguments, so a union inside a function must sit between its own pair of brackets, as in =SUM((A2:A9,C2:C9)).

Without the extra brackets Excel reads two arguments, which is also valid but a different construction.

The # and @ signs are newer. A reference followed by # points to the complete spill range of the formula in that cell, so it grows and shrinks with the result.

A @ in front of a reference asks for one value only (implicit intersection): Excel picks the cell in the same row or column as the formula. Excel adds @ itself when it opens an older workbook where a formula used to return one value.

=SUM((A2:A9, C2:C9))

=SUM((A2:A9; C2:C9))

=SUM(B2:B9 A5:D5)

=SUM(B2:B9 A5:D5)

=SUM(F2#)

=SUM(F2#)

The order Excel applies them

Excel works through the operators in the order of the table, from step 1 to step 8. Operators in the same step are applied from left to right, so =10-4-3 is 3, not 9.

Parentheses override the order: Excel always evaluates what is between brackets first, innermost pair first. That is the cure for most precedence mistakes. Write the brackets even when the order would be right without them; a reviewer does not have to remember the table.

A comparison comes last, so in =A2+B2>C2 the sum is calculated before it is compared with C2.

=B2*(1+21%)

=B2*(1+21%)

=B2*1+21%

=B2*1+21%

TRUE and FALSE in calculations

In arithmetic TRUE counts as 1 and FALSE as 0: =TRUE+1 is 2 and =(A2>100)*B2 is B2 when A2 is above 100 and 0 otherwise. This is how conditions are multiplied (AND) or added (OR) inside one formula.

It does not work everywhere: SUM and the other aggregate functions ignore TRUE and FALSE that they find in a range, and SUMPRODUCT treats an array of TRUE and FALSE that stands on its own as zeros.

The double unary, two minus signs in a row, forces the conversion. The first - turns TRUE into -1, the second turns it back into 1, so --(A2:A9>100) is an array of ones and zeros that SUMPRODUCT can add. Multiplying by 1 or adding 0 has the same effect.

When the conditions are joined with *, as in (A2:A9>100)*(B2:B9="MEM"), the multiplication already does the conversion and no -- is needed.

=SUMPRODUCT(--(C2:C100>E1))

=SUMPRODUCT(--(C2:C100>E1))

=SUMPRODUCT((B2:B100="MEM")*(C2:C100>E1)*C2:C100)

=SUMPRODUCT((B2:B100="MEM")*(C2:C100>E1)*C2:C100)

Related topics

• CriteriaA criterion is the condition a cell must meet to be counted, added up or averaged: how to write exact, comparison, date and blank criteria, and how to combine several.
• WildcardsWildcards are characters that stand in for other characters, so a criterion or lookup value can match part of a text instead of the whole text.
• Cell references: relative, absolute and mixedA cell reference is the address a formula reads. Dollar signs decide whether it moves when you copy the formula, and getting that right is what lets one formula fill a whole column or table.
• Spill and dynamic arraysA spill is what happens when one formula returns several values: they flow into the cells below and beside it. See which Excel does this, how to refer to the result with #, and what #SPILL! means.
• Error valuesAn error value is a result that starts with # and names what went wrong, such as #N/A or #DIV/0!. It spreads through formulas, and hiding it can hide a gap in your audit evidence.