Many of the simpler formulas that you build require the sole use of Excel’s operators, which are the symbols that indicate the type of computation that is to take place between the cells and/or constants interspersed between them. Excel uses four different types of operators: arithmetic, comparison, text, and reference.
| Type | Character | Operation | Example |
|---|---|---|---|
| Arithmetic | + (plus sign) | Addition | =A2+B3 |
| – (minus sign) | Subtraction or negation | =A3–A2 or –C4 | |
| * (asterisk) | Multiplication | =A2*B3 | |
| / | Division | =B3/A2 | |
| % | Percent (dividing by 100) | =B3% | |
| ^ | Exponentiation | =A2^3 | |
| Comparison | = | Equal to | =A2=B3 |
| > | Greater than | =B3>A2 | |
| Less than | =A2 | ||
| >= | Greater than or equal to | =B3>=A2 | |
| Less than or equal to | =A2 | ||
| Not equal to | =A2B3 | ||
| Text | & | Concatenates (connects) entries to produce one continuous entry | =A2&” “&B3t |
| Reference | : (colon) | Range operator that includes | =SUM(C4:D17) |
| , (comma) | Union operator that combines multiple references into one reference | =SUM(A2,C4:D17,B3) | |
| (space) | Intersection operator that produces one reference to cells in common with two references | =SUM(C3:C6 C3:E6) |
Most of the time, you’ll rely on the arithmetic operators when building formulas in your spreadsheets that don’t require functions because these operators actually perform computations between the numbers in the various cell references and produce new mathematical results.
The comparison operators, on the other hand, produce only the logical value TRUE or the logical value FALSE, depending on whether the comparison is accurate.
The single text operator (the so-called ampersand) is used in formulas to join together two or more text entries (an operation with the highfalutin’ name concatenation).
You most often use the comparison operators with the IF function when building more complex formulas that perform one type of operation when the IF condition is TRUE and another when it is FALSE. You use the concatenating operator (&) when you need to join text entries that come to you entered in separate cells but that need to be entered in single cells (like the first and last names in separate columns).
When you build a formula that combines different operators, Excel follows the set order of operator precedence. When you use operators that share the same level of precedence, Excel evaluates each element in the equation by using a strictly left-to-right order.
| Precedence | Operator | Type/Function |
|---|---|---|
| 1 | – | Negation |
| 2 | % | Percent |
| 3 | ^ | Exponentiation |
| 4 | * and / | Multiplication and Division |
| 5 | + and – | Addition and Subtraction |
| 6 | & | Concatenation |
| 7 | =, , =, | All Comparison Operators |
