How to Use Operators in Excel? (All You Need to Know)

In this article, you’ll get to know about 4 commonly used operators in Excel. They are Arithmetic, Comparison/Logical, Reference, and Concatenation operators.

We’ll talk about the orders in which operators are used and how to change those orders.

Overall, you’ll get to know everything about ArithmeticComparison/LogicalReference, and Concatenation operators and their uses.

We have used Excel 365 while preparing the article.

The operators specify the type of calculation in a formula. For example, the arithmetic operators indicate addition, subtraction, multiplication, or division of formula elements.

The below overview image explains the uses of different operators in Excel. To show the uses, we used the IF function. to get a more logical output.

Excel Operators

Click the Image for a detailed view

Download Practice Workbook

What Are Operators in Excel?

Operators are characters or symbols used to perform mathematical calculations in MS Excel. We use Excel operators for calculations and various formulas. Operators help in processing data in Excel worksheets easily.

How Many Types of Operators Are There in Excel?

There are 4 types of operators in Microsoft Excel.

  • Arithmetic Operators (+,,*,/,%,^): Used for basic mathematical calculations.
  • Comparison Operators (=,<>,>,<,>=,<=): You can use the comparison operators in conditional formatting and in other complex formulas.
  • Reference Operators (“:”,“,”,“ ”): These operators refer to a specific range or cell link within formulas.
  • Concatenation Operator: The ampersand symbol (&) is the only concatenation operator that joins two or multiple strings together.

1. What Are Arithmetic Operators and How to Use Them in Excel?

There are 6 arithmetic operators. Plus (+) sign for addition, Minus (-) sign for subtraction, Asterisk (*) sign for Multiplication, Forward Slash (/) for Division, Percent (%) sign for percentage, and Caret (^) sign for Exponential operation.

All the arithmetic operators and their summary is given in the following table.

Operators Condition Name Formula Description
% Percent Sign =25%*B10 Converts a numeric value to a percentage.
^ Caret/Exponential =B10^C10 The value of the first cell is raised to the power of the value in the second cell.
* Asterisk =B10*C10 Returns the multiplied value of two cells.
/ Forward Slash =B10/C10 Divides the first cell value by the second one and gives the result.
+ Addition =B10+C10 Adds the numeric values and returns the result.
Subtraction =B10-C10 Subtracts the second cell from the first one and gives a numeric value.
Note: Before using the arithmetic operators you should know about their precedence. The Percent sign has the highest precedence. Then, Exponential or Caret symbol. After that, Multiplication and Division followed by Addition and Subtraction.
  • The following data table has 2 values in each row. We will see the result with each operator.

Dataset to Show Arithmetic Operators

  • Write the following formula in E5 and press ENTER. This will give you the added value.

Use of addition (+) operator

  • For the exponential operator, write the following formula in E9 and press ENTER.
  • You can see the exponentiated value 25 as a result.
  • Similarly, you can use the other arithmetic operators in Excel.

Use of Exponential operator

2. When to Use Comparison/Logical Operators in Excel?

You can use the comparison/logical operators to compare data between two cells. There are 6 comparison operators in Excel.

The following table explains these operators in an efficient way.

Operators Condition Name Formula Description
= Equal to =IF(C5=D5, “True”, “False”) Checks if two cell values are equal or not.
< Less than =IF(C5<D5, “True”, “False”) Checks if the first cell value is smaller than the second cell value.
> Greater than =IF(C5>D5, “True”, “False”) Checks if the first cell value is greater than the second cell value.
<> Not equal to =IF(C5<>D5, “True”, “False”) Checks if the two cells are equal or not.
<= Less than or equal to =IF(C5<=D5, “True”, “False”) Checks if the first cell value is smaller or equal to the second cell value.
>= Greater than or equal to =IF(C5>=D5, “True”, “False”) Checks if the first cell value is greater than equal to the second cell value.

We will use these logical operators in an IF formula. The IF function checks whether a condition is TRUE or FALSE. The syntax of the IF function is given as:

Syntax of IF function

Click the Image for a detailed view

  • Write the following formula in E5 and press ENTER.
  • This formula checks whether the logic is TRUE or FALSE.
  • As the C5 and D5 cells are not equal it returns FALSE.

Using of equal to (=) operator

  • Again, the C8 and D8 cells are not equal. So the Not equal to operator returns TRUE in E8.
  • Similarly, you can check for other operators.

Showing the not equal to operator

Read More: How to Use Logical Operators in Excel

3. What Are Reference Operators in Excel?

Colon (:), Comma (,), and Space ” “ are the reference operators in Excel. They are also known as Range, Union, and Intersection operators. We use these operators to indicate any data range.

We have included these reference operators in the following table:

Operators Condition Name Formula Description
: Range =SUM(C5:E5) Indicates data range from the first cell to the second cell.
, Union =SUM(C5,D5,E5) Indicates separate cell values.
“ ” Intersection =C9:E9 D5:D12 Returns the intersection cell value.

In this dataset, we have different student names and their marks. We will find Total Marks with different reference operators.

Dataset to use Reference Operators

Read More: How to Use Reference Operator

3.1 Use of Range Operators in Excel

Generally, we can find summation with the SUM function in Excel. For that, we can reference data with the Range (:) operator.

The overview of the SUM function is shown in the following image:

Syntax of SUM Function

Click the Image for a detailed view

  • Use the following formula in F5 to find the Total marks:

Using SUM Function with Range operator

  • Now, hold and drag the F5 cell downward to find the Total Marks of all students.

Showing Total marks using Range operators

3.2 Use of Union Operators

We can also reference the data by the Union (,) operator in the SUM Function.

  • Write the following formula in F5 and press ENTER.

Using SUM Function with Union (,) operator

  • Copy the formula to other cells to get total marks for all other students.

Result after finding Total Marks with union (,) operator

3.3 Use of Intersect Operators in Excel

To find a specific cell value from a data table, you can use the Intersect (“ ”) operator. In the dataset below, we want to find the Chemistry marks of the student with ID number S005.

  • Use the following formula in D16 and press ENTER.
=C9:E9 D5:D12

Inserting Formula with Intersection operator

  • You can see that the formula returns the mark of Chemistry which is 75.

Finding Intersected cell

4. What Is Concatenation Operator and How to Use it in Excel?

The ampersand (&) sign is known as the concatenation operator. We can use this concatenation operator (&) to join two or more strings. Let’s see, how we can use this concatenation operator in Excel.

We want to concatenate the First Name and Last Name from the following data set.

Showing dataset to be used for Concatenate operator

  • As we have to include a space between the First Name and Last Name, write the following formula in D5 and press ENTER.
=B5&" "&C5

Formula and Result with Concatenate operator

  • Copy the formula to all other cells and all other names will also be concatenated as shown in the following image.

Showing Full Names after concatenation

What Is the Order of Excel Operators and How Can We Change it?

The Excel operators have predefined precedence. In an Excel formula, there could be many operators. The system will first work with the most precedent operator and it will move to the next most precedent operator. The order of operators is given in the following table:

Operators Description
Colon (,); Comma (,); Space ( ) Reference Operators
% Percentage
^ Exponential Operator
*, / Multiplication and Division Operators
+ , – Addition and Subtraction Operators
& Concatenating Operator
=, <>, <=, >=, <> Comparison Operators

You can change the operator order by adding parenthesis. For example, the following formula in Excel will return 13.


Since 20/4 will be calculated first and then added to 8 due to the higher precedence of the Division Operator (/).

But, if you add parenthesis and modify the formula as:


The formula will return 7. Since we are now telling Excel to calculate 8+20 first before dividing by 4.

This is how you can add parenthesis to change the operator’s order.

Read More: Order of Operations in Excel

Which Things Should You Remember?

  • Excel has precedence over arithmetic operators. Multiplications and Divisions are performed before Addition and Subtraction.
  • Make sure to use the proper data type. When you are concatenating, the cell value has to be in Text format. For arithmetic operators, you have to use the Number format.
  • You need to be careful while referencing especially with absolute and relative referencing.


Excel Operators are essential tools to carry out various calculations. You can use these operators for mathematical calculations and conditional formatting. We also use these operators for logical reasoning, referencing cells, and concatenation of two or more strings. We hope this article will help you master these operators and use them whenever you need them.

Frequently Asked Questions

1. What is the difference between Operators and Functions?

Answer: Excel Functions take values as input and return meaningful results. On the other hand, the operators are used in different Excel functions or formulas so that they can work properly.

2. How can I use operators to create complex formulas?

Answer: You can create complex formulas with Excel operators. To separate different criteria, we have to use comma (,) as a delimiter. Also, to apply different logical conditions, you can use logical operators. Here is a complex formula =IF(AND(A1 > 60, B1 < 50), “Pass”, “Fail”) which returns Pass and Fail using the condition.

3. Which operator is used to compare if two values are equal?

Answer: To find out if two values are equal, you need to use two equal operators (==). This operator can be used to compare between a pair of numeric, boolean, string, and object values.

Excel Operators: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!


Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF