Excel Operators

There are four different types of Excel Operators. These are:

Arithmetic Operators
Text Operator
Comparison Operators
Reference Operators

Each of these Operator types is discussed below.


Arithmetic Operators

The Excel Arithmetic Operators and the order in which they are evaluated are shown in the table below:

OperatorDescriptionPrecedence
(1=top; 3=bottom)
%The percent operator1
^The exponentiation operator1
*The multiplication operator2
/The division operator2
+The addition operator3
-The subtraction operator3


Precedence of Arithmetic Operators

The table above shows that the percent and exponentiation operators have the greatest precedence, followed by the multiplication and division operators, and then the addition and subtraction operators. Therefore, when evaluating Excel Formulas that contain more than one arithmetic operator, the percent and exponentiation operators are evaluated first, followed by multiplication and division operators. Finally, the addition and subtraction operators are evaluated.

The order in which the arithmetic operators are evaluated makes a big difference to the result of an Excel formula. However, brackets can be used to force parts of a formula to be evaluated first. If a part of a formula is encased in brackets, the bracketed part of the formula takes precedence over all of the above listed operators. This is illustrated in the following examples:

Arithmetic Operator Examples

 Formulas:
 A
1=1+2*3+4
2=(1+2)*(3+4)
3=3^2*2
4=3^(2*2)
5=20%*500
 Results:
 A
111
221
318
481
5100

Excel Text Operator

The Excel Concatenation Operator (denoted by the & symbol) joins together text strings, to make a further, single text string.

Concatenation Operator Example

The following formula uses the concatenation operator to combine the text strings "SMITH", ", " and "John"

 Formulas:
 A
1="SMITH" & ", " & "John"
 Results:
 A
1SMITH, John

Excel Comparison Operators

The Excel Comparison Operators are used when defining conditions (for example when using the Excel If Function). These operators are listed in the table below:

OperatorDescription
=Equal to
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
<>Not equal to


Comparison Operator Examples

The spreadsheets below show examples of the comparison operators used with the Excel If Function.

 Formulas:
 ABC
112=IF( A1=B1, "equal", "not equal" )
2  =IF( A1>B1, "A", "B" )
3  =IF( A1<B1, "A", "B" )
4  =IF( A1>=B1, "A", "B" )
5  =IF( A1<=B1, "A", "B" )
6  =IF( A1<>B1, "not equal", "equal" )
 Results:
 ABC
112not equal
2  B
3  A
4  B
5  A
6  not equal

Excel Reference Operators

The Excel Reference Operators are used when referring to ranges within a spreadsheet. The reference operators are:

OperatorDescription
:Range operator (defines a reference to a range of cells)
,Union operator (combines two references into a single reference)
  (space)Intersection operator (returns a reference to the intersection of two ranges)


Reference Operator Examples

Example 1 - Excel Range Operator

Cell C1 of the following spreadsheet shows the range operator, used to define the range A1-B3. The range is then provided to the Excel Sum Function, which adds together the values in cells A1-B3 and returns the value 21.

 ABC
112=SUM(A1:B3)
234 
356 


Example 2 - Excel Union Operator

Cell C1 of the following spreadsheet shows the union operator, used to define a range made up of cells in the two ranges A1-A3 and A1-B1. The resulting range is then provided to the Excel Sum Function, which adds together the values in the combined range and returns the value 12.

(Note that the Excel Union Operator does not return a true mathematical union, as cell A1, which is included in both ranges A1-A3 and A1-B1 is counted twice in the sum calculation).

Example of Excel Union Operator


Example 3 - Excel Intersection Operator

Cell C1 of the following spreadsheet shows the intersection operator, used to define a range made up of cells in the intersection of ranges A1-A3 and A1-B2. The resulting range (range A1-A2) is then provided to the Excel Sum Function, which adds together the values in the intersecting range and returns the value 4.

Example of Excel Intersection Operator


Further information on Excel Operators is provided on the Microsoft Office website.