Excel - Operators
Operators are used to specify the type of operation you want to perform on elements in a formula - such as addition, subtraction, multiplication, or division. Operators in Excel can be categorized as follows:
- Arithmetic operators
- Assignment operators
- Text concatenation operator
- Reference operators
Excel Arithmetic operators
In Excel formula, arithmetic operators are used to perform standard set of math operators for performing addition, subtraction, multiplication, and division etc. The table below lists the arithmetic operators available in Excel:
| Operator | Name | Example |
|---|---|---|
| + | Addition | =10+2 |
| - | Subtraction / Negation | =16-4 |
| * | Multiplication | =12*12 |
| / | Division | =64/4 |
| ^ | Exponent / Power | =9^2 |
| % | Percent | =25% |
See the excel spreadsheet example below:
Excel Comparison operators
In Excel formula, comparison operators are used to perform comparisons, and to build formula criteria. Logical operators can be used in formulas on their own, or combined with each other and/or other functions. The table below lists the comparison operators available in Excel:
| Operator | Description | Example |
|---|---|---|
| = | Equal to | =A1=10 |
| <> | Not equal to | =A1<>10 |
| > | Greater than | =A1>30 |
| < | Less than | =A1<50 |
| >= | Greater than or equal to | =A1>=25 |
| <= | Less than or equal to | =A1<=70 |
See the excel spreadsheet example below:
Excel Text concatenation operator
In Excel formula, the ampersand (&) operator can be used to join, or concatenate, one or more text strings to produce a single piece of text.
| Operator | Description | Example |
|---|---|---|
| & | Ampersand | ="Good"&"Day" |
See the excel spreadsheet example below:
Excel Reference operator
In Excel formula, reference operators are used to combine ranges of cells for calculations. The table below lists the reference operators available in Excel:
| Operator | Description | Example |
|---|---|---|
| : (colon) | Range operator, which produces one reference to all the cells between two references, including the two references. | =SUM(B5:B15) |
| , (comma) | Union operator, which combines multiple references into one reference. | =SUM(B5:B15,D5:D15) |
| (space) | Intersection operator, which produces a reference to cells common to the two references. | =SUM(B7:D7 C6:C8) |
| @ (at) | Reference operator, which is used to indicate implicit intersection in a formula. | =@A1:A10 =SUM(Table1[@[January]:[December]]) |
Excel Operators Precedence
Operator precedence (order of operations) is a collection of rules that reflect conventions about which procedures to perform first in order to evaluate a given expression.
For example, multiplication has higher precedence than addition. Thus, the expression 1 + 2 × 3 is interpreted to have the value 1 + (2 × 3) = 7, and not (1 + 2) × 3 = 9. When exponent is used in the expression, it has precedence over both addition and multiplication. Thus 3 + 52 = 28 and 3 × 52 = 75.
The following table lists the precedence of Excel operators. Operators are listed top to bottom, in descending precedence. Operators with higher precedence are evaluated before operators with relatively lower precedence. Excel calculates the formula from left to right, according to a specific order for each operator in the formula.
| 9 | () | Parentheses |
| 8 | : | Less than, Less than or equal, Greater than, and Greater than or equal |
| (space) | Equal to | |
| , | Not equal to | |
| 7 | - | Negation |
| 6 | % | Percent |
| 5 | ^ | Exponentiation |
| 4 | * / | Multiplication, Division |
| 3 | + - | Addition, Subtraction |
| 2 | & | Connects two strings of text (concatenation) |
| 1 | < <= > >= | Less than, Less than or equal, Greater than, and Greater than or equal |
| = | Equal to | |
| <> | Not equal to |
