It is highly unlikely that you’d be an Excel user without having heard of reference operators. We use these essential operators frequently. And this is the part of Excel which we have never been instructed. But there are certain uses of the operators. In this article, we’ll focus on some special applications of the reference operator in Excel along with the basics including real-life examples. Moreover, we’ll explore the use of arithmetic and logical operators in Excel.

## Download Practice Workbook

## What Is an Operator in Excel?

Operational signs (operator signs) are symbols that represent a type of computation between cells and/or integers in Excel. Excel provides a wide range of operators for performing calculative operations on a given collection of data.

## Types of Operators in Excel

Typically, operators are classified into four types.

### 1. Arithmetic Operator

As the title suggests, this operator is utilized for arithmetic operations for example to *add* (**+**), *subtract* (**–**), *multiply* (*****), *divide* (**/**) cells or cell range, etc. Such as if you want to add two cells i.e. **A1** and **B1**, the formula will be:

`=A1+B1`

### 2. Comparison Operator

Comparison or logical operator compares values meaningfully. They compare values (by cell or separately) and return a logical result such as **TRUE** or **FALSE**. If the **A1** cell is greater than or equal to **B1 **cell, the relationship can be expressed as:

`A1>=B1`

**Read More:** **How to Use Greater Than or Equal to Operator in Excel Formula**

### 3. Text Concatenation Operator

The *ampersand* symbol (**&**) is the only operator to join or concatenate. You can use it to add two or more strings of text together in a single string. For example, if you want to join the **A1** and **B1** cells, the formula will be:

`= "A1"&"B1"`

### 4. Reference Operator

Reference operator relates to the range of Excel worksheets in a formula. Referencing might be created using a *colon* (**:**), *comma* (**,**), and *space* ( ). And these are called range operator, union operator, and intersection operator, respectively. All right, the details of these operators will be discussed here.

## 5 Uses of Reference Operator with Their Types in Excel

Researchers say example-based learning fosters the reasoning ability of the learners. So, let’s learn the reference operator using the following dataset. Consider the **Exam Scores** of four subjects are given with respect to **Students’ Name**.

Right now, we’ll see how we can use the different types of reference operators in Excel. So, let’s begin.

### 1. Range Operator (:)

The range operator enables you to create a single reference for numerous cells that are positioned between two cells that you define. It is expressed by the **colon (:)** sign. Here, we’ll use the **SUM function** to add the scores in the *Science* and *Math columns *in the **D5:E13** cells. So, let’s start.

📌 **Steps:**

- First of all, go to the
**C15**cell >> enter the formula given below.

`=SUM(D5:E13)`

Here, the **D5:E13** cells indicate the scores in *Math* and *Science*.

Finally, the result looks like the image shown below.

#### 1.1. Contiguous Range (Symmetrical Range)

A range is made up of all cells that are close to one another in the same row or column. When such a range is selected in a spreadsheet, it is typically represented by a square or rectangle. For example, we’ll use the **SUM** function to obtain the sum of the contiguous **C8:F11** cells. Therefore, just follow along.

📌 **Steps:**

- In the first place, navigate to the
**C15**cell >> type in the formula given below.

`=SUM(C8:F11)`

Here, the **C8:F11 **cells refer to the scores of *Andru*, *Alam*, *Sudip*, and *George*.

Lastly, the output should look like the picture shown below.

#### 1.2. Non-Contiguous Range (Irregular Range)

When selecting or highlighting a range of cells that are not adjacent and may not have regular geometrical shapes e.g. square or rectangle, even a single cell, the range is considered to be a non-contiguous (irregular) range. Now, let’s observe the process of finding the summation of the irregular range with the appropriate illustration.

📌 **Steps:**

- To begin with, jump to the
**C15**cell >> type in the formula given below.

`=SUM(E5:F13,C15:F15)`

Here, the **E5:F13** range represents all the scores in *Math* and *Art* while the **C15:F15** range refers to the scores of *Rabada*.

Eventually, the **SUM** function should return the output *1010* as shown below.

### 2. Union Operator (,)

The union operator joins numerous references together to form a single reference. Usually, it is expressed by the **comma (,)** sign. In the following example, we can find the sum of the cells** C7**, **D10**,** E7**, **F11**, and** F5** so let’s see it in action.

📌 **Steps:**

- First and foremost, proceed to the
**C15**cell >> insert the formula below.

`=SUM(C7,D10,E7,F11,F5)`

Here, *comma* (,) joins all the cell values.

Now, the end result should look like the screenshot given below.

#### 2.1 Combination of Union and Range Operator in Excel

If you want to join two separate or adjacent cell ranges, you can do that using the union operator. In this case, we want to find the sum of the *History* and *Math* scores.

📌 **Steps:**

- First, move to the
**C15**cell >> apply the equation as shown below.

`=SUM(C7:C12,E7:E12)`

Specifically, the **C7:C12** and **E7:E12** cell ranges refer to the marks in *History* and *Math* respectively.

Consequently, this should yield the result *520*.

### 3. Intersection Operator ( )

Using the intersection operator, you can create a reference to cells that is common in two or multiple references. It is expressed by the **single space ( ) **and we can find the value of the common cell between the two cell ranges.

📌 **Steps:**

- Initially, navigate to the
**C16**cell >> apply the expression as shown below.

`=C10:F10 E5:E13`

In the following equation, the **C10:F10** and **E5:E13** cell ranges point to the marks of *Sudip* and all the marks in *Math*.

Subsequently, the results should appear in the image below.

#### 3.1. Intersection of Two Named Ranges (Using One Column and One Row)

For one thing, we can use the intersection operator for **Named Ranges**. For example, you want to find the score of *Sudip* in *Science* among all student’s scores in Science. Now, just see the following procedure.

📌 **Steps:**

- To start with, select the
**D5:D13**cells >> in the**Name Box**enter the name*Science*. Likewise, repeat the same process for*Sudip*.

Here, the **D5:D13** cells represent the marks in *Science*.

- Next, jump to the
**C16**cell >> enter the expression given below.

`=Science Sudip`

#### 3.2. Intersection Operator Using Multiple Columns and Rows

Let’s imagine another situation where you want to find total scores of *George* in *Science *and *Math *(multiple columns and rows). Now, allow us to demonstrate the process in the steps below.

📌 **Steps:**

- First, insert the
**Names Ranges**as shown prior >> move to the**C16**cell >> enter the following expression.

`=Science George + Math George`

#### 3.3. Combination of Intersection and Range Operators

More importantly, we can use the combination of intersection and range operator to find any common values. In the previous example, we saw the addition sign (+) in between ranges. Besides, we can also use the **SUM** function for finding similar outputs. So, let’s see the process in detail.

📌 **Steps:**

- At the very beginning, make the
**Names Ranges**, as shown above >> proceed to the**C16**cell >> type in the following expression.

`=SUM(Science:Math George)`

### 4. Pound (#) Reference Operator in Excel

The *pound* (**#**) operator indicates the lack of space in the cell. Hence, If you widen the column, you’ll see the value properly.

### 5. At (@) Reference Operator in Excel

In a formula, the *at* reference operator is used to denote an implicit intersection.

`@C1:C2`

*📃 Note: Please note that this operator is not supported by older versions of Excel.*

## How to Use Arithmetic Operators in Excel

As the name suggests, **arithmetic operators** perform operations like addition, subtraction, multiplication, division, percentage, and exponential. So, let’s have a quick overview of the arithmetic operators, so you don’t have to spend all day on this.

Operator | Description |
---|---|

Addition (+) | Adds two or more numeric values |

Subtraction (-) | Subtracts one numeric value from another |

Multiplication (*) | Returns the product of numbers |

Division (/) | Performs division of numerator with the denominator |

Percentage (%) | Calculates the percentage of a number by dividing it with 100 |

Exponential (^) | Raises a number to a power |

**Addition:**

Specifically, the addition operator sums numeric values to return a numeric value, as shown in the image below.

`=B5+C5`

On this occasion, the **B5 **and **C5** cells point to **Number 1** and **Number 2** respectively.

**Subtraction:**

Alternatively, the subtraction operator subtracts one value from another to yield a number.

`=B5-C5`

**Multiplication:**

Here, the multiplication operator calculates the product of numbers.

`=B5*C5`

**Division:**

Conversely, the division operator divides one number by another.

`=B5/C5`

**Percentage:**

In this case, the percentage operator divides a number by **100**.

`=B5/%`

**Exponential:**

Lastly, the exponential operator raises a number to a power.

`=B5^C5`

Subsequently, the results look like the picture shown below.

## How to Use Logical Operators in Excel

For one thing, logical operators consist of **greater than, less than**, and equal symbols which return **TRUE **or **FALSE **values. So, let’s see the process in detail.

📌 ** Steps**:

- First of all, move to the
**E5**cell >> enter the expression given below.

`=C5>D5`

Here, the **C5 **and **D5** cells represent the **Cost** and **Selling Prices** respectively.

- Next, navigate to the
**F5**cell >> type in the formula given below.

`=B5>"apple"`

In this case, the **B5** cell refers to the **Product**.

Eventually, the final output should look like the image given below.

Admittedly, I have skipped the **uses of the logical operators in Excel** which you may explore if you wish.

**Read More:** **How to Use Less Than Or Equal to Operator in Excel (8 Examples)**

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

## Conclusion

To sum up, we hope this tutorial has provided you with helpful knowledge on how to use reference operator in Excel. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.