# Cell ranges: 99% of users don’t know this

I have never been taught how to use cell ranges in Excel; I doubt you have either. They are just too basic for anybody to teach, right? Select some cells, and there is a : (colon) symbol placed between the start and end cells. Maybe we could throw in a few \$ (dollar signs) to fix the position of a cell reference. It’s hardly rocket science.

For most Excel users, that is everything they know about ranges, and everything they think they need to know. But that’s just the starting point; there is much more to uncover.

Ranges are the building blocks of Excel, so let’s ensure we understand how to use them correctly.

This post uncovers the methods for working with ranges that every Excel user should know.

Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post.

File name:

• 0179 Cell Range Basics.xlsx
• 0179 Cell Range Basic Cheat Sheet.pdf

## Range operators

Range operators are the special characters we place between individual cell references to create a range.

There are 3 operators:

• Range operator: Creates a single 4-sided range area.
• Union operator: Creates a range by joining two or more ranges together.
• Intersection operator: The range shared by two or more ranges.

Let’s start by understanding these operators.

### Range operator

The range operator is the : (colon) symbol. We see this symbol when selecting a range using the mouse or keyboard shortcuts.

Since this is the operator that most Excel users know, we won’t spend too long on this one.

Here is an example of the range operator in use.

``=SUM(B2:D4)``

Result: 42

The range operator combines all the cells (including the two cells in the reference) into a single 4-sided range.

Nothing too complex here.

Once joined with the union operator or intersection operator, the range operator becomes even more powerful.

### Union operator

The union operator is the , (comma) symbol.

You may have seen the comma used in the in the following situation:

``=SUM(B2,D3,C4)``

Result: 14

However, in this scenario, the comma is not the union operator but the argument separator.

Within a function, arguments are separated by a comma. For example, the SUM function can include 255 items to aggregate, each separated by a comma. In this example, we provided SUM with 3 arguments; we have not used the union operator.

We must include additional brackets to use the union operator within a function, so Excel knows not to treat the comma as an argument separator.

``=SUM((B2,D3,C4))``

Result: 14

In the example, the calculation returns the same result, but that will not always be the case.

It may appear we are simply listing cells. While that is how we list items in a sentence, or in Power Query, that’s not how cell references work.

The calculation combines the individual ranges into a single range before executing.

The power of the union operator is seen when combined with the range operator. Look at the image below; we are summing the values in the two ranges as if there were a single range.

``=SUM(B2:D2,C4:E4)``

Result: 29

Remember:

The comma can be the argument separator or the union operator. Use brackets within a formula to ensure Excel treats the comma as the union operator.

### Intersection operator

The Intersection operator is the (space) character.

The intersection operator returns the range shared by two or more ranges.

Look at the example below:

``=SUM(B3:E4 C2:D5)``

Result: 19

The formula in cell G2 calculates the range where the two other ranges intersect (i.e., the area where the ranges cross over) at cells C3:D4.

When using the intersection operator, we can include multiple ranges. For example, B3:E4 C2:D5 D2:E4 would be acceptable syntax for 3 ranges, returning D3:D4.

### Order of precedence

Now, what happens if we mix operators in a single range? How does that impact calculation?

Within Excel’s calculation rules, there is a calculation order already set.

1. Range operator
2. Intersection operator
3. Union operator

Let’s take a look at 3 examples to see this in operation.

#### Example #1

Look at the following, what will the result be?

``=SUM((B3:E3 C2:C5,D4))``
1. B3:E3 and C2:C5 calculate first
2. Next, the intersection between the two ranges calculates, leaving just cell C3
3. The union operator evaluates, giving a range of C3,D4.
4. Therefore, the final calculation is =SUM((C3,D4))

Result: 10

#### Example #2

If we want to force the calculation order, we can use brackets (just like with any nested functions in Excel).

``=SUM((B3:E3 (C2:C5,D4)))``
1. The brackets evaluate first:
• Range operator C2:C5 is calculated.
• Next, as there is no intersection in the brackets, the union operator evaluates, leaving the range C2:C5,D4.
2. Next, the intersection is evaluated. The only cell that intersects between B3:E3 and C2:C5,D4 is C3.
3. Therefore, the final calculation is =SUM((C3)).

Result: 2

#### Example #3

Take a look at the following example; what will that calculate? This is a tricky one.

``=SUM(B2:(D3,C4))``
1. Brackets calculate first:
• There is only a union operator within the brackets, so this evaluates as D3,C4.
2. The only operator remaining is the range operator. The purpose of the range operator is to create a single 4-sided range. So, Excel creates a range that encompasses all 3 ranges. This can only be B2:D4.
3. This leads to the final calculation of =SUM(B2:D4).

Result: 42

Even though D4 was not one of the cells in the formula, it is required to include D3 and C4.

If you tell me this didn’t blow your mind, I won’t believe you.

## Functions that return cell ranges

One of the challenges of working with ranges, is when we don’t know the address of the cells to include in the formula. This occurs when we need a calculation to determine the range to use; known as a dynamic range.

Thankfully, there are several functions we can use to calculate a range address.

Instead of referencing a cell, we can include a formula that calculates the range.

Let’s use a simple example:

``=SUM(B2:IF(A1=1,D4,D10))``
• When the value in cell A1 is 1, IF returns a cell reference of D4. This creates a range of =SUM(B2:D4).
• When the value in cell A1 is not 1, IF returns a cell reference of D10. This creates a formula of =SUM(B2:D10).

So, depending on the value in cell A1, the range changes in size.

The syntax may seem confusing initially, as there is a formula directly after the colon. Excel calculates the formula first, then returns the cell address to create the range for the subsequent formula.

This creates enormous flexibility because we can create ranges on the fly inside formulas.

The functions that return ranges are: CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET, SWITCH, and XLOOKUP. Let’s take a brief look at each of these functions.

To know more about these functions, check out Alan Murray’s book, Advanced Excel Formulas.

### CHOOSE

The CHOOSE function can select different cell ranges based on an index number.

CHOOSE

Description

Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number.

Syntax

CHOOSE(index_num, value1, [value2], …)

• Index_num: Specifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
• If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
• If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
• If index_num is a fraction, it is truncated to the lowest integer before being used.
• Value1, value2, … Value 1 is required, subsequent values are optional. 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

Let’s look at an example of using CHOOSE to select a range to return.

``=SUM(CHOOSE(G4,B2:B5,C2:C5,D2:D5,E2:E5))``

The first argument of the CHOOSE function is the index number. The remaining arguments are a list of possible results.

Using the example above:

• The index number in cell G4 is 2.
• Therefore, the 2nd item in the remaining arguments is returned, which is cells C2:C5.
• The formula above becomes =SUM(C2:C5).

Result: 16

### IF

The IF function can return a range based on conditional logic.

IF

Description

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Syntax

IF(logical_test, value_if_true, [value_if_false])

• logical_test: The condition to test.
• value_if_true: The value to return if the result of logical_test is TRUE.
• value_if_false (optional): The value to return if the result of logical_test is FALSE.

The following is an example of using IF as one side of the range operator.

``=SUM(C2:IF(G4=2,C5,D5))``

Using the example above:

• The IF evaluates to TRUE because cell G4 equals 2.
• The range created is C2:C5.
• The formula above becomes =SUM(C2:C5).

Result: 16

### IFS

The IFS function can calculate a range based on a series of logic tests and returns the first value that evaluates to TRUE.

IFS (Excel 2019 and later)

Description

The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition.

Syntax

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3…)

The following uses IFS to return an entire range into the SUM function.

``=SUM(IFS(G4=1,B2:B5,G4=2,C2:C5,G4=3,D2:D5,G4=4,E2:E5))``
• Cell G4 is equal to 2; therefore, the range C2:C5 is returned.
• The formula above becomes =SUM(C2:C5).

Result: 16

### INDEX

The INDEX function can return the nth row and nth column from a range as a cell reference.

INDEX (array form)

Description

Returns the value of an element in a table or an array, selected by the row and column number indexes.

Syntax

INDEX(array, row_num, [column_num])

• array: A range of cells or an array constant.
• If array contains only one row or column, the corresponding row_num or column_num argument is optional.
• If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
• row_num: Selects the row in array from which to return a value. If row_num is omitted, column_num is required.
• column_num (optional): Selects the column in array from which to return a value. If column_num is omitted, row_num is required.

The following example uses INDEX as one side of the range operator.

``=SUM(C2:INDEX(B2:E5,G4,G5))``
• Based on range B2:E5, INDEX returns the 4th row (cell G4) and 2nd column (cell G5). Which calculates as C5.
• The range operator creates a range of C2:C5.
• The formula above becomes =SUM(C2:C5).

Result: 16

With the INDEX function, if the row or column value is zero or blank, the value returned is the entire row or column.

``=SUM(INDEX(B2:E5,G4,G5))``
• Based on range B2:E5, INDEX returns all the rows (0 in cell G4) from the 2nd column (cell G5). Which calculates as C2:C5
• The formula above becomes =SUM(C2:C5)

Result: 16

### INDIRECT

INDIRECT converts a text string into a range or cell reference.

INDIRECT

Description

Returns the reference specified by a text string.

Syntax

INDIRECT(ref_text, [a1])

• Ref_text: A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.
• If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
• a1 (optional). A logical value that specifies what type of reference is contained in the cell ref_text.
• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Let’s take a look at an example using INDIRECT.

``=SUM(C2:INDIRECT(G4))``
• INDIRECT(G4) converts the text value C5 into a range.
• The range created by the union operator is C2:C5.
• The formula above becomes =SUM(C2:C5).

Result: 16

Warning

INDIRECT is a volatile function, so should be used with care.

### OFFSET

The OFFSET function can create a range based on a cell position combined with width and height arguments.

OFFSET

Description

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. We can specify the number of rows and the number of columns to be returned.

Syntax

OFFSET(reference, rows, cols, [height], [width])

• Reference: The reference from which to base the offset.
• Rows: The number of rows, up or down, that we want the upper-left cell to refer to. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
• Cols: The number of columns, to the left or right, that we want the upper-left cell of the result to refer to. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
• Height (optional): The height is the number of rows we want the returned reference to be. Height must be a positive number.
• Width (optional): The width is the number of columns we want the returned reference to be. Width must be a positive number.

Let’s take a look at an example.

``=SUM(OFFSET(B2,G4,G5,G6,G7)``
• The OFFSET function starts at cell B2 and then creates a range that is 0 rows down (G4), 1 column across (G5), 4 rows high (G6), 1 column wide (G7). This creates the range C2:C5.
• The formula above becomes =SUM(C2:C5).

Result: 16

Warning:

OFFSET is a volatile function, so should be used with care.

### SWITCH

The SWITCH function is a mixture between the CHOOSE and IFS functions. It can return a range based on matching the value in the expression argument.

SWITCH (Excel 2019 onwards)

Description

The SWITCH function evaluates one value (called the expression) against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.

Syntax

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

• expression: Expression is the value (such as a number, date or some text) that will be compared against value1…value126.
• value1…value126: ValueN is a value that will be compared against expression.
• result1…result126: ResultN is the value to return when the corresponding valueN argument matches expression. ResultN and must be supplied for each corresponding valueN argument.
• default (optional): Default is the value to return in case no matches are found in the valueN expressions. The Default argument is identified by having no corresponding resultN expression. Default must be the final argument in the function.

In the following example, SWITCH returns a full range to the SUM function.

=SUM(SWITCH(G4,1,B2:B5,2,C2:C5,3,D2:D5,4,E2:E5))

• The value in G4 is 2. The range following the value 2 in the argument list is C2:C5.
• The formula above becomes =SUM(C2:C5).

Result: 16

### XLOOKUP

XLOOKUP looks up a value from a range and returns the corresponding cells from another range.

XLOOKUP (Excel 2021 onwards)

Description

The XLOOKUP function searches a range or an array, and returns the item corresponding to the first match it finds from another row or array. If no match exists, then XLOOKUP can return the closest (approximate) match.

Syntax

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value to search for
• lookup_array: The array or range to search
• return_array: The array or range to return
• if_not_found (optional): Where a valid match is not found, return the [if_not_found] text. If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
• match_mode (optional): Specify the match type:
• 0: Exact match. If none found, return #N/A. This is the default.
• -1: Exact match. If none found, return the next smaller item.
• 1: Exact match. If none found, return the next larger item.
• 2: A wildcard match where *, ?, and ~ have special meaning.
• search_mode (optional): Specify the search mode to use:
• 1: Perform a search starting at the first item. This is the default.
• 1: Perform a reverse search starting at the last item.
• 2: Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
• -2: Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

In the following example, we will keep it simple by using only the required arguments for the XLOOKUP function.

``=SUM(C2:XLOOKUP(G4,B2:B5,C2:C5))``
• XLOOKUP finds the value 8 (cell G4) from the range B2:B5 and returns the corresponding range from C2:C5. As 8 is the 4th cell in B2:B5, it returns the 4th cell in C2:C5, which is C5
• The range created by the union operator is C2:C5
• The formula above becomes =SUM(C2:C5)

Result: 16

UPDATE:

There are two other functions which we can add to this list, TAKE and DROP.

Find out more here:

TAKE: Returns a specified number of contiguous rows or columns from the start or end of an array.
https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003

DROP: Excludes a specified number of rows or columns from the start or end of an array.
https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34

## Other operators and syntax

The range operators in the first section deal with basic range creation from standard cell references. But there are other operators we need to be aware of too.

• Spill operator: For working with dynamic arrays.
• Intersection operator: For working with cell references in the same row or column.

Let’s take a look at each of these.

### Absolute marker (\$)

The absolute marker is the \$ (dollar) symbol. This is a symbol that most users understand and are familiar with.

Where a \$ symbol exists before a column letter or row number, that row or column becomes frozen when copying/pasting the formula to another location.

Where either the column or row is frozen, it is known as a mixed reference. When both the row and column are frozen, it is known as an absolute reference.

• Relative reference: A1
• Mixed references: \$A1 or A\$1
• Absolute reference: \$A\$1

After selecting a cell reference, pressing the F4 key cycles through the 4 options: \$A\$1, A\$1, \$A1, A1.

This \$ symbol is ignored by Excel when it evaluates formulas; it is only used when copying or dragging formulas into other cells.

Mixing absolute, relative, and mixed references in a single range is permitted. For example, A1:\$D\$10, \$E4 is valid syntax and contains relative, absolute, and mixed references.

### Spill operator

The spill operator is the # (hash/pound) symbol. This symbol obtains the full range occupied for a dynamic array calculation.

In the example below, the UNIQUE function generates a list of unique values from the original source. The data could have an unknown number of unique values; therefore, the range filled by UNIQUE is of an unknown size.

The formula in cell G2 is:

``=UNIQUE(E2:E5)``

The formula in cell G7 is:

=SUM(G2#)

G2# provides a way to reference a range returned by the formula in G2, no matter the number of rows or columns produced.

Reference:

For more information about dynamic array formulas, check out our post: Dynamic arrays in Excel – Everything you NEED to know

### Implicit intersection operator

The implicit intersection operator is the @ (at) symbol. This symbol is used to return only the cell that exists in the same row or column as the formula that is referencing the range.

In the example below, the range C2:C5 is used in every formula. The @ symbol returns only the cell which exists in the same row.

• Cell G2 returns the value in cell C2
• Cell G4 returns the value in cell C4
• Cell G6 returns the #VALUE! error as there are no values in C2:C5 that exist in the same row as the formula.

This approach works for both rows and columns.

## Conclusion

You’re now an Excel Range ninja. You’ve got all the tools you need to create very powerful (yet simple) Excel models. The only limit now, is your creativity.

Discover how you can automate your work with our Excel courses and tools.

The complete program for saving time by automating Excel.

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

### 3 thoughts on “Cell ranges: 99% of users don’t know this”

1. Great article! Outside the basics I wasn’t aware of all the other operators, etc. Thanks for sharing.

I’m trying to think of how I might use these more in my job. Besides the working examples you used do you have practical examples you could share?