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

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

In this article, we will discuss 4 commonly used types of operators in Excel: Arithmetic, Comparison/Logical, Reference, and Concatenation operators. We’ll discuss the orders in which operators are used and how to change those orders.

The below overview image explains the uses of different operators in Excel. We used the IF function to demonstrate their usage, 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 Excel. We use Excel operators for calculations and various formulas.

What Types of Operators Are There in Excel?

There are 4 types of operators in Excel:

• Arithmetic Operators (+,–,*,/,%,^): Used for basic mathematical calculations.
• Comparison Operators (=,<>,>,<,>=,<=): Used in conditional formatting and other complex formulas.
• Reference 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.

Section 1 – Arithmetic Operators and How to Use Them

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.

• Enter the following formula in E5 and press ENTER to get the added value:

• For the exponential operator, enter the following formula in E9 and press ENTER:

The exponentiated value 25 is the result.

• Similarly, use the other arithmetic operators to get the rest of the results.

Section 2 – When to Use Comparison/Logical Operators in Excel

Use the comparison/logical operators to compare data between two cells. There are 6 comparison operators in Excel:

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

Click the Image for a detailed view

• Enter the following formula in cell E5 and press ENTER:

This formula checks whether the logic is TRUE or FALSE. As cells C5 and D5 are not equal, it returns FALSE.

Again, cells C8 and D8 are not equal, so the Not equal to operator returns TRUE in cell E8.

Similarly, check the other operators.

Section 3 – Reference Operators

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

In the dataset below, we have different student names and their marks. We will find Total Marks using different reference operators.

Read More: How to Use Reference Operator

3.1  – Using Range Operators in Excel

Generally, we can perform addition with the SUM function in Excel, and 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:

• Hold and drag the Fill Handle from cell F5 downwards to find the Total Marks for all the students.

3.2 – Using Union Operators

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

• Enter the following formula in cell F5 and press ENTER:

• Copy the formula to the other cells to get the Total Marks for all the other students.

3.3  – Using Intersect Operators in Excel

To find a specific cell value in a data table, 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:

The formula returns a Chemistry mark of 75.

Section 4 – The Concatenation Operator and How to Use it

The ampersand (&) sign is known as the concatenation operator. We can use this concatenation operator (&) to join two or more strings.

For example, suppose 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, enter the following formula in cell D5 and press ENTER:

• Copy the formula to all the other cells, and all the 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?

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

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

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

Now add parenthesis and modify the formula as follows:

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

Read More: Order of Operations in Excel

Things To Remember

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

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, operators are used in different Excel functions or formulas so that they 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, use the comma (,) as a delimiter. To apply different logical conditions, use logical operators. Here is a complex formula =IF(AND(A1 > 60, B1 < 50), “Pass”, “Fail”) which returns Pass and Fail using a condition.

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

Answer: 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