Reference Operator in Excel [Basics + Special Uses]

Get FREE Advanced Excel Exercises with Solutions!

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.


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 Comparison Operators in Excel


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.


Reference Operator in Excel: 5 Uses

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.

Dataset for Reference Operator in Excel

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.

Range Operator

Finally, the result looks like the image shown below.

reference operator in excel with range operator


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.

Contiguous Range (Symmetrical Range)

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

reference operator in excel with Non Contiguous Range


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.

Non-Contiguous Range (Irregular Range)

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

reference operator in excel Non-Contiguous Rangewith


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.

Union Operator

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

reference operator in excel with union operator


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.

Combination of Union and Range Operator in Excel

Consequently, this should yield the result 520.

reference operator in excel with Combination of Union and Range Operator


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.

Intersection Reference

Subsequently, the results should appear in the image below.

reference operator in excel with intersection operator


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.

The intersection of Two Named Ranges

  • Next, jump to the C16 cell >> enter the expression given below.
=Science Sudip

Applying Named Range

 


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

Intersection Operator Using Multiple Columns and Rows


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)

Combination of Intersection Operator and Range Operator in Excel


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.

Pound (#) Reference Operator in Excel


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.

How to Use Arithmetic Operators in Excel using Addition

Subtraction:

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

=B5-C5

Using Subtraction

Multiplication:

Here, the multiplication operator calculates the product of numbers.

=B5*C5

Using Multiplication

Division:

Conversely, the division operator divides one number by another.

=B5/C5

Using Division

Percentage:

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

=B5/%

Using Percentage

Exponential:

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

=B5^C5

Subsequently, the results look like the picture shown below.

Using Exponential


How to Use Logical Operators in Excel

For one thing, logical operators in Excel consist of greater than and less than along with 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.

How to Use Logical Operators in Excel

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

=B5>"apple"

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

Logical Operator for Text

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

reference operator in excel with Logical operator

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


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.

Practice Section


Download Practice Workbook


Conclusion

To sum up, we hope this tutorial has provided you with helpful knowledge on how to use reference operators 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


<< Go Back to Excel Operators | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo