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

Dataset for Reference Operator in Excel

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.

Range Reference Operator in Excel

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.

Contiguous Reference Operator in Excel

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

Non-contiguous Reference Operator in Excel

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.

Union Reference Operator in Excel

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)

Combination of Union and Range Operator in Excel

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

Intersection Reference Operator in Excel

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

Intersection of Two Named Ranges

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.

Intersection Operator Using Multiple Columns and Rows

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)
[/wpsm_box]

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

Combination of Intersection Operator and Range Operator in Excel

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.

Pound (#) Reference Operator in Excel

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo