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, I’ll focus on some special applications of the reference operators along with the basics including real-life examples.

## 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 A1 cell is greater than or equal to B1, the relationship can be expressed as **A1>=B1**.

### 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 A1 cell and B1 cell, 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.

## Reference Operator 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.

Here, **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.

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.

Let’s say, you have selected a cell range A1:A12 in any formula. The cell range includes the cells between A1 and A12.

Now, look at the following example.

We want to calculate the sum of exam scores in science and math. So, we have to sum from **D5 **(science scores start here) to **E13 **(math scores end here) cells.

We can do that in two ways.

`=SUM(D5,D6,...E13) or =SUM(D5:E13)`

The second way reveals the uses of the range operator.

Note.** SUM** is an Excel function that adds cell values.

#### i. 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, the cell range **C8:F11** in the following figure is a contiguous or symmetrical range.

#### ii. 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.

For example, the irregular range in the following figure is** (E5:F13,C15:F15)**.

### 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** by using the below formula.

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

Here, comma (,) is used to join the cell values.

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

Such as you can find the sum of cell ranges **C7:C12** and **E7:E12** using the following formula with the union operator.

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

### 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 ( )**.

In the following screenshot, we see the common cell is E10 between **C10:F10** and **E5:E13** cell ranges.

Now, we can find the common cell by inserting the following formula using a single space between the two cell ranges.

`=C10:F10 E5:E13`

#### i. The intersection of Two Named Ranges (Using One Column and One Row)

If you guys need the use of an intersection operator for named ranges, you can use that.

Before we go into the applications, I wanna ask you a question.

Do you know the use of names in Excel formulas?

If the answer is “yes”. That’s fine.

But if you don’t have the experience, don’t need to be worried.

Just see the following procedure.

Select data> **Formulas** tab>**Create From Selection** dialog box>Select the checkboxes of the **Top** row and **Left** column from the dialog box.

Let’s come to the core point now.

We can use the intersect operator as a straightforward formula for looking things up.

For example, you want to find the score of **Sudip** in **Science** among all student’s scores in Science.

If we insert the named ranges and keep a space between the ranges, we will easily find that.

The formula is

`=Science Sudip`

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

Let’s imagine another situation where you want to find the total scores of **George** in **Science** and **Math** (multiple columns and rows).

Just type the following formula with space between named ranges.

`=Science George + Math George`

You’ll get 90 as the output.

You may use the same formula when you have no named ranges as shown in the following screenshot clearly.

#### iii. Combination of Intersection Operator and Range Operator in Excel

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 output. So, the formula will be-

`=SUM(Science:Math George)`

You’ll get 90 as output (the previous output is also 90)

## More Reference Operator in Excel

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

The **# sign** indicates that there is insufficient space to render the cell. If you widen the column, you’ll see the value properly.

Besides, it is employed as a component of an error name (#VALUE!)

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

In a formula, the at reference operator is used to denote an implicit intersection, which is represented by the **@ symbol** e.g. @C1:C2.

Remember, this operator is not supported by older versions of Excel.

## Conclusion

Now, you’ve got these examples of using various types of reference operators from different aspects in Excel. I think you’ll not face any trouble regarding this topic. However, if you have any issues, please share them with me in the following comments section.

Also, thanks for being with me.