| 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<=B3 | |
| <> | Not equal to | =A2<>B3 | |
| 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) |
Excel 2019: “Smooth operator”
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. For example, say that you enter the following formula in cell A10:
=B10<>C10If B10 contains the number 15 and C10 contains the number 20, the formula in A10 returns the logical value TRUE. If, however, both cell B10 and C10 contain the value 12, the formula returns the logical value FALSE.
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). For example, suppose that you enter the following formula in cell C2:
=A2&B2If cell A2 contains
John and cell B2 contains Smith, the formula returns the new (squashed together) text entry, JohnSmith. To have the formula insert a space between the first and last names, you have to include the space as part of the concatenation as follows:
=A2&" "&B2
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).
Order of operator precedence in Excel 2019
When you build a formula that combines different computational 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 |
=B4+C4/D4Because division (like multiplication) has a higher level of precedence than addition (4 versus 5), Excel evaluates the division between cells C4 and D4 and then adds that result to the value in cell B4. If, for example, cell B4 contains 2, C4 contains 9, and D4 contains 3, Excel would essentially be evaluating this equation in cell A4:
=2+9/3In this example, the calculated result displayed in cell A4 is 5 because the program first performs the division (9/3) that returns the result 3 and then adds it to the 2 to get the final result of 5.
If you had wanted Excel to evaluate this formula in a strictly left-to-right manner, you could get it to do so by enclosing the leftmost operation (the addition between B4 and C4) in a closed pair of parentheses. Parentheses alter the natural order of precedence so that any operation enclosed within a pair is performed before the other operations in the formula, regardless of level in the order. (After that, the natural order is once again used.)
To have Excel perform the addition between the first two terms (B4 and C4) and then divide the result by the third term (cell D4), you modify the original formula by enclosing the addition operation in parentheses as follows:
=(B4+C4)/D4Assuming that cells B4, C4, and D4 still contain the same numbers (2, 9, and 3, respectively), the formula now calculates the result as 3.666667 and returns it to cell A4 (2+9=11 and 11/3=3.66667).
If necessary, you can nest parentheses in your formulas by putting one set of parentheses within another (within another, within another, and so on). When you nest parentheses, Excel performs the calculation in the innermost pair of parentheses first before anything else and then starts performing the operations in the outer parentheses.
Consider the following sample formula:
=B5+(C5–D5)/E5In this formula, the parentheses around the subtraction (C5–D5) ensure that it is the first operation performed. After that, however, the natural order of precedence takes over. So the result of the subtraction is then divided by the value in E5, and that result is then added to the value in B5. If you want the addition to be performed before the division, you need to nest the first set of parentheses within another set as follows:
=(B5+(C5–D5))/E5In this revised formula, Excel performs the subtraction between the values in C5 and D5, adds the result to the value in cell B5, and then divides that result by the value in cell E5.
Of course, the biggest problem with parentheses is that you have to remember to enter them in pairs. If you forget to balance each set of nested parentheses by having a right parenthesis for every left parenthesis, Excel displays an Alert dialog box, informing you that it has located an error in the formula. It will also suggest a correction that would balance the parentheses used in the Excel formula. Although the suggested correction corrects the imbalance in the formula, it unfortunately doesn’t give you the calculation order that you wanted — and if accepted, the suggested correction would give you what you consider an incorrect result. For this reason, be very careful before you click the Yes button in this kind of Alert dialog box. Do so only when you’re certain that the corrected parentheses give you the calculation order that you want. Otherwise, click No and balance the parentheses in the formula by adding the missing parenthesis or parentheses yourself.