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