Operators in Excel helps to perform verity of operations in Excel Calculations. Excel Operators are very useful in creating Formulas and Expressions. We can divide the Excel Operators into 4 major categories. Such as,1. Reference Operators, 2. Arithmetic Operators, 3. Concatenation Operator and 4. Comparison Operators.

In this topic:

  1. Reference Operators
  2. Arithmetic Operators
  3. Concatenation Operator
  4. Comparison Operators

Reference Operators

Excel Reference Operators are used to refer the Excel Ranges and Cells in the Formula. Colon, Space and Comma are the Reference Operators in Excel. Excel will evaluate these Operations first when you use in Excel.

Operator Sign Evaluation Order Operator Name Operator Meaning Description
: 1 Colon Range Operator

Reference All the Cells of Two Ranges

We can use the Range Operator  (:)  Colon to refer all the Cells Between Two Ranges. For Example, A1 to E5 can be referred as A1:E5 in Excel Formula.

Examples:

  1. Sum all the cells of the Range D1 to D25, =SUM(D1:D25)
  2. Count the cells with data of a Column B, =COUNT(B:B)

 
  1 Space Intersection Operator

Reference the Common Range of Cells in Two Ranges

We can use the Intersection Operator ( )  SPACE to Intersection Cells of Two Ranges. For Example, Intersecting Cells of Range A1:B5 and B1:D5 can be refereed as A1:B5 B1:D5 in Excel.

Examples:

  1. Find the Sum of the Intersection Cells of Two Ranges A1:B5 and B1:D5, =SUM(A1:B5 B1:D5)
  2. Find Count of the Intersection Cells of Two Ranges B1:B10 and B5:B15, =COUNT(B1:B10 B5:B15)
, 1 Comma Union Operator

Combines Multiple References to One Reference

We can use the Union Operator (,)  Comma to  Combine Two Ranges in to One. For Example, Combine Cells of Range A5:B10 and C1:D5 can be refereed as A5:B10,C1:D5 in Excel.

Examples:

  1. Find the Maximum of the All the Cells of Two Ranges A1:B5 and B1:D5, =MAX(A1:B5,B1:D5)
  2. Find the Sum of the Two Ranges B1:B10 and B5:B15, =SUM(B1:B10,B5:B15)

Arithmetic Operators

Excel Arithmetic Operators are used to perform the mathematical calculations in Excel. +,-,*,/,^,-,% are the Arithmetic Operators in Excel. Excel will evaluate these Operations after the reference operators when you use in Excel Formula.

Operator Sign Evaluation Order Operator Name Operator Meaning Description
– 2 Minus Negation

Negative Number

Negation Operator is useful to find the Negation value of number. For example, 25 can be converted to its Negation value, -25
% 3 Percent Percent

Percentage Number

We can use the Percentage (%) Operator to find the Percentage Number. For example, 10%*150

Examples:

  1. Find the 25% of 1000, =25%*1000
  2. Sum the 10% of Range A1 and 20% of Range B1, =10%*A1+20%*B1
^ 4 Caret Exponentiation

Number 2 (Right) Exponential of the Number 1 (Left)

We can use Caret/ Exponentiation  Operator to find the Exponential value of number. For example, 10^2 returns the square value of 10=100.

Examples:

  1. Square value of 25 can be found using exponentiation operator, =25^2
  2. Cube value of 100 can be found using exponentiation operator, =100^3

 
/ 5 Forward slash Division

Divide the Number 1 (Left) with Number 2 (Right)

Forward slash is the division of Operator in Excel to divide any number with another number. For example, 10/2, 10 is dived by 2.

Examples:

  1. Divide Range B1 by A1, =B1/A1
  2. Find the Half of a number in Range A1,=A1/2
* 5 Asterisk Multiplication

Multiply the Number 1 (Left) with Number 2 (Right)

We can multiply using */ Asterisk Operator in Excel. For example, A1*25

Examples:

  1. Multiply Two Ranges, =A1*B1
  2. Sum the values in Column A and Multiply with Range B1, =SUM(A:A)*B1

 
+ 6 Plus Addition

Add the Number 1 (Left) with Number 2 (Right)

We can use + (Plus) operator to add in the Excel. For example, =25+75

Examples:

  1. Add Range A1 and B1, =A1+B1
  2. Add 30 days to Today’s Date, =TODAY()+30
– 6 Minus Subtraction

Subtract the Number 2 (Right) from Number 1 (Left)

We can use – (Minus) Operator to Subtract the numbers in Excel. For example, =50-10

Examples:

  1. Subtract 100 from range B5, =B5-100
  2. Sub tract Range A1 from A2,=A2-A1

Concatenation Operator

Ampersand (&) is the Concatenate Operator in Excel. & uses to connect the results of two expressions or Strings. We can concatenate two strings, two values or both, the result will be a string.

Operator Sign Evaluation Order Operator Name Operator Meaning Description
& 7 Ampersand Ampersand

Concatenates two strings and returns String 1 (Left) String 2 (Right)

We can use &(Ampersand) Operator to concatenate two strings. For Example, =”Hello!”&” How are You?”

Examples:

  1. We often concatenate Two Cells to form a combined string, = A1&B1
  2. We can Add Prefix or suffix to a sting. Let’s add question mark to Range A1, =A1&”?”

Comparison Operators

Comparison Operators in Excel uses to compare two expressions in Excel Formula. =,>,<,>=,<=,<> are the comparison operators in Excel.

Operator Sign Evaluation Order Operator Name Operator Meaning Description
= 8 Equal to Equal to Comparison

Checks if two expressions are equal

Example:

=IF(A1=A2,”Equal”,”Not Equal”)
> 8 Greater than Greater than Comparison

Checks if Expression 1 (Left) is Greater than Expression 2 (Right)

Example:

=IF(A1>A2,”Bigger”,”Not Bigger”)
< 8 Less than Less than Comparison

Checks if Expression 1 (Left) is Less than Expression 2 (Right)

Example:

=IF(A1<A2,”Smaller”,”Not Smaller”)
>= 8 Greater than or equal to Greater than or equal to Comparison

Checks if Expression 1 (Left) is Greater than or Equal to Expression 2 (Right)

Example:

=IF(A1>=A2,”Greater than or equal”,”Smaller”)
<= 8 Less than or equal to Less than or equal to Comparison

Checks if Expression 1 (Left) is Less than or Equal to Expression 2 (Right)

Example:

=IF(A1<=A2,”Less than or equal”,”Greater”)
<> 8 Not equal to Not equal to Comparison

Checks if two expressions are Not equal

Example:

=IF(A1<>A2,”Not equal”,”Equal”)

Share This Story, Choose Your Platform!

Leave A Comment