Excel Reference Operators
There are 3 Reference Operators in Excel:
- “:” (colon) is the Range operator, which produces one reference to all the cells between two cells references, including the cells written in the reference text, like: A11:A13
- “,” (comma) is the Union operator, which combines multiple Ranges into one reference, like A11:A13 , A11:C11 , A11 (you can refer to simple cells too, not only to ranges with multiple cells)
- ” ” (space) is the Intersection operator, which returns a reference to the cell or to the range of cells found at the intersection of the ranges. In this example, only cell A11 is found in both ranges: A11:A13 A11:C11
Because only practicing the methods described in this article will consolidate your knowledge, you can download a sample workbook to play with the methods described in this article.
Download Sample File: Excel Reference Operators.xlsx
Before diving into details, let me clarify a few terms used in this article:
– A Range can contain one or more worksheet cells. A range can be a contiguous range, when all the cells are contained in a rectangle. A non – contiguous Range will contain multiple rectangles, even single cells.
Contiguous range sample reference: A10:D18 (refer to the image below)
Non – Contiguous range sample reference: G10:G15, I10:I12, K14:L18, I17, G21:J22 (this reference contains 5 ranges, combined using the Union Operator – “,” , refer to the image below)
– A reference can contain one or more cells or ranges, contiguous or non – contiguous, separated by any of the 3 Reference Operators. Here are some examples of references:
- Reference sample 1: A11:A13 , A11:C11 , A11 (this reference contains 3 ranges, combined using the Union Operator – “,”)
- Reference sample 2: A12 A11:A14 (this reference contains 2 ranges, combined using the Intersection Operator – ” ” (space) )
- Reference sample 3: A12
The Reference Operators are most often used in Excel Functions, but you can use them to select ranges, by typing the references in the Name Box, which is located under Excel’s Ribbon, to the left of the Formula Bar.
Name Box Location image:
You can use the Name Box to select cells or ranges, even if they are in different sheets. If you want to select a range from another sheet, simply type into the Name Box the worksheet name followed by an exclamation mark before the range reference: Sheet1!A1:A20.
Important: if the worksheet name contains spaces, you have to wrap the worksheet name between single quotes (or apostrophies) when you want to use this reference to select a range using the Name Box, or in a formula:
Excel Intellisense will assist you only when building formulas, to provide function lists and arguments, or to create the references, but not when using the Name Box.
Let’s take a closer look at how we can use the Reference Operators to select ranges using the Name Box or in formulas:
The Range Operator: “:” – (colon)
The most simple use of the Range Operator is to select a range of cells between (and including) 2 cell references:
Most likely, you already knew this. Less known is the fact that you can use the colon multiple times in a reference. Please type the following text in the Name Box, you’ll see what i mean:
By default, Excel will select all the cells from the first cell reference (A11) and the last cell reference (C14) in the range. In other words, Excel will create and select a contiguous range with these cells as rectangle corners: A11, C11, A14, C14. This might not be what you expected, and you should avoid using multiple colon ranges in your formulas, otherwise you might get unexpected results if you are not aware of this default behavior. If you have a useful way to use multiple colon ranges, please let me know, I am very curious 🙂
The Union Operator: “,” – (comma)
If used in the Name Box, all the ranges you typed in, separated by commas, will be selected:
A11:A14 , A11:C11
The only limit on how many ranges you can type into the Name Box is the 255 characters limit, so it’s quite enough, assuming that you will not type more than a few ranges, contiguous or non – contiguous:
A11:A12 , A14 , C12:C13 , B12
There is an important difference between typing multiple ranges into the Name Box or into a formula:
Take a look at the following image:
When you type A11:A14,A11:C11 into the Name Box, because the A11:A14 , A11:C11 ranges have a common cell – A11, knowing that a cell cannot be selected twice, you will see that the Excel Status Bar shows a count of 6 selected cells. If you use the very same reference in a simple SUM formula: =SUM(A11:A14,A11:C11), Excel will add the values from each duplicate reference! (make sure the formula is not applied in a cell within the formula reference, to avoid circular references)
The Intersection Operator: ” ” – (space)
If you type into the Name Box this reference:
Excel will select only the common cells, which in this case is A11.
If there are no common cells within the ranges you typed into the Name Box, Excel will display this error message:
If the ranges that you specified in a formula do not intersect, the formula returns a #NULL! error. For example, the ranges in the formula =SUM(A11:A14 B11:C11) do not intersect, so the formula returns a #NULL! error.
As you already know by now, you can type a reference containing multiple ranges separated only by spaces into the Name Box or into a formula, to create an Intersection between 3 ranges:
A11:A14 A11:C11 A11:B12
The only problem when using the Intersection Operator with multiple ranges, is that you have to keep in mind that ALL ranges must intersect! In other words, all ranges must have at least one common cell, otherwise you will get the #NULL! error if you use the ranges in a formula, or the Reference error message as shown in the above image, if the reference is typed into the Name Box.
General Rules for using Reference Operators in Name Box or in formulas:
- You can use spaces between references, Excel is able to identify the Reference Operator! For example the reference:
A11:A14 , A11:C11 , A10:B12
has at least 2 spaces before and after the commas. Knowing that the ” “ (space) character is the Intersection Operator and the “,” (comma) character is the Union Operator, you might expect Excel to be confused when you use a space followed by a Range Operator or a Union Operator. The key is that, if there are only spaces with no other Reference Operators (Range Operator – “:”, Union Operator – “,”), only then will Excel treat the references as Intersections.
If Excel finds a Range Operator – “:” or a Union Operator – “,”), even if you have multiple spaces between ranges Excel will treat the references as Ranges, if Range Operator (“:”) is used, or as combined Ranges if Union Operator (“,”) is used.
Only when you have one or multiple spaces between ranges, like:
only then will Excel treat the reference as an Intersection between 2 or multiple ranges.
- You can combine 2 or all 3 Reference Operators to create a reference:
A13:A14 , A11:A14 A11:C11
The above range reference typed into the Name Box can be read as: “the range A13:A14 AND the intersection between ranges A11:A14 A11:C11″. Because the intersection between ranges A11:A14 A11:C11 will be only cell A11, Excel will select only 3 cells: A13, A14, AND A11.