Different Types and Usages of Operators in Excel Formulas

Operators are special symbols that perform specific operations between operands, and then return a result. In our everyday life we basically use four mathematical operators, i.e. + (addition), - (subtraction), * (multiplication) and / (division). In Microsoft Excel we have many other operators to make our work easier.
Operators in Microsoft Excel are divided in four simple types or classes to make it easy to understand and remember. This classes are Mathematical or Arithmetic Operators, Logical or Comparison Operators, Reference Operators and Text Concatenation Operator. Below I have described the function and usages of this operators with examples. Say we have 10 in A1 field and 10 B1 field.

Mathematical or Arithmetic Operators

As the name suggest this operators are used to do mathematical calculation, whether it is addition or subtraction. In real life we have only 4 (better to say 2; + & -) mathematical operators, i.e. +, -, * and /. In Excel two more mathematical operators are created using this basic operators. Operators like % (Percent; is not a real operator though) and ^ (Exponentiation) are used in Excel. The table below will help you understand the usages of this operators.

Example and Usages of Mathematical Operators in MS Excel

SymbolOperatorUsagesUsagesResult
+Addition =A1+B1 10+1020
Subtraction =A1-B1 10-100
/Division =A1*B1 10*10100
*Multiplication =A1/B1 10/101
%Percent =(A1+B1)% (10+10)%0.2
^Exponentiation =A1^410^410000

Logical or Comparison Operators

This operators are used to do a logical test between tow or more numbers or strings. This operators returns a value of either True or False. Three symbols =, > and < combined with each other makes six logical operators. Basic use of logical test is like “If he is earns more than $10,000 then he has to pay government taxes”. Lets do it the Excel way; say we have 10 in A1 field and 5 B1 field.

Example and Usages of Logical Operators in MS Excel

SymbolOperatorUsagesUsagesResult
=Equal to =A1=B1 10=5FALSE
>Greater than =A1>B1 10>5TRUE
<Less than =A1 10<5FALSE
>=Greater than or equal to =A1>=B1 10>=5TRUE
<=Less than or equal to =A1<=B110<=5FALSE
<>Not equal to =A1<>B110<>5TRUE

Reference Operators

Excel supports another class of operators known as reference operators, it basically combine ranges of cells for calculations. Here we have three symbols that represents three operators. : (colon), , (comma), and (single blank space) are used for this.

Example and Usages of Reference Operators in MS Excel

SymbolOperatorUsagesResult
: (colon)Range operator, which produces one reference to all the cells between two references, including the two references.=A1:B4This will refer to all (8 in this case) cells from A1 to B4 range.
, (comma)Union operator, which combines multiple references into one reference=A1:B4,D1:E4This will refer to the combination of cells from A1 to B4 and D1 to E4
(single space)Intersection operator, which produces on reference to cells common to the two references (B7:D7 C6:C8)This will return the value of the cell C7 as this is the intersection of this two ranges.

Text Concatenation Operator

This operator is made for joining two more cells and return a single String in Excel and is represented by a Single Blank Space. Though concatenation is used with text, it can also be used to work with numbers. If the source and the results are numeric then the result can be used as a number, though it is technically a text string.
For example say we have 25 in cell A1 and 60 in cell A2 and Home in cell A3, then =A1&A2 will return 2560(remember the result is a string but excel can use it as a number too), and =A1&A2&A3 will return 2560Home

Add a Comment

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.