Reference Operator in Excel [Basics + Special Uses]

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:

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:

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:

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

Addition:

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

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.

Multiplication:

Here, the multiplication operator calculates the product of numbers.

Division:

Conversely, the division operator divides one number by another.

Percentage:

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

Exponential:

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

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.

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.

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.

Further Readings

• What is the Order of Operations in Excel (An Ultimate Guide)
• Excel Boolean Operators: How to Use Them?
• How to Apply ‘If Greater Than’ Condition In Excel (9 Ways)
• ‘Not Equal to’ Operator in Excel (With 5 Examples)