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 Arithmetic, Comparison/Logical, Reference, 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.

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.

• The following data table has 2 values in each row. We will see the result with each operator.

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

• 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.

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.

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:

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.

• 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.

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:

In this dataset, we have different student names and their marks. We will find Total Marks with different 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:

Click the Image for a detailed view

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

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

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.

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

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.

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

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.

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

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

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:

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.

Conclusion

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