Excel operators, wildcards and order of operations

Arithmetic operators: + - * / ^ %

These work like a calculator. The one that surprises people is %: it divides the number before it by 100, so 10% is 0.1 and =D2*(1-10%) takes 10% off a price.

The & operator: join text

& glues values together into one piece of text. Put spaces and punctuation in quotes. It does the same job as CONCAT, and is usually quicker to type for two or three pieces.

The catch: joining a number turns it into text and drops its format, so 1834.5 shown as $1,834.50 joins as 1834.5. Wrap the number in TEXT to keep the look.

Comparison operators: = <> > < >= <=

A comparison returns TRUE or FALSE. You will mostly use them inside IF, AND, OR, FILTER and conditional formatting rules. Three rules catch people out:

Text comparisons ignore case, so "north" equals "North"; use EXACT when case matters. A number and the same digits typed as text are not equal. And when Excel compares different types, text always counts as greater than any number, and TRUE and FALSE as greater than text.

Operators inside criteria: COUNTIF, SUMIF and friends

In COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF(S), MAXIFS and MINIFS the condition is a piece of text, so the operator goes inside quotes: ">=5", "<>North". To compare with a cell or a calculation, join the operator to it with &: ">"&F1. "<>" on its own means "not empty" and "=" on its own means "empty".

Wildcards: * ? and ~

A wildcard stands in for characters you do not know. * matches any number of characters, including none. ? matches exactly one. ~ in front of * or ? means the real character. Like everything in criteria, wildcards ignore case.

They work in COUNTIF(S), SUMIF(S), AVERAGEIF(S), MAXIFS, MINIFS, VLOOKUP and HLOOKUP with an exact match, MATCH with 0, SEARCH, and XLOOKUP and XMATCH when match_mode is 2. They do not work with the = operator, in FIND, or inside FILTER conditions: there, use ISNUMBER(SEARCH("lamp",A2:A6)) instead.

Reference operators: colon, comma and space

The colon makes a range: C2:C6 is every cell from C2 to C6, and A1:D6 a block of 24 cells.

The comma joins references. Inside SUM(C2:C6,D2:D6) it simply separates two arguments. Wrapped in its own brackets, =SUM((C2:C6,D2:D6)), it is the union operator that turns both ranges into one reference, which matters for the few functions that take a single reference argument.

A space is the intersection operator: =C2:C6 B4:D4 means only the cells the two ranges share, here C4. If the ranges do not overlap you get #NULL!, which is why a space typed by accident instead of a comma or colon causes that error.

The # and @ operators (Excel 365)

A # after a cell, such as F2#, means the whole spill range that starts there: every value a dynamic array formula in F2 returned, however many rows it grows to. =COUNTA(F2#) counts them and =SUM(F2#) adds them.

The @ is the implicit intersection operator. It asks for one value instead of a range. You rarely type it, but Excel 365 adds it to formulas written in older versions, such as =@A2:A10, so they keep returning one value as they did before spilling existed. Inside a Table, [@Units] means the Units value in the same row.

Order of operations: what Excel calculates first

Excel does not just work left to right. It follows this order, and brackets override it: whatever is in brackets is calculated first, innermost first. Operators on the same level, such as * and /, are worked left to right.

Two results look wrong until you know the order. Negation comes before ^, so =-2^2 squares -2 and gives 4, where most maths books give -4; write =-(2^2) for that. And ^ works left to right, so =2^3^2 is (2^3)^2, which is 64, not 2^9.