Unlike regular cell references, double-clicking a formula with indirect cell references will not highlight (with a dotted border) the range. To highlight indirect ranges, you need to employ a custom formula within conditional formatting in Google Sheets.

This will highlight the range with your preferred color. Choose a light color, and you are set. When you modify the cell references within the INDIRECT function, the highlighted area will adjust accordingly.

This is essentially a workaround solution to visualize the formula range by double-clicking with regular cell references. Instead, in this case, we employ a highlight rule. Keep in mind that it won’t respond to the double-click, but it serves the purpose of highlighting the specified range.

What is an Indirect Range in Google Sheets?

An “indirect range” typically refers to using the INDIRECT function in a way that dynamically references a range of cells.

For clarification, A1:C10 in a formula is a cell range reference, whereas “A1:C10” is a string. If you want to use a text string as a cell range reference, you should wrap it with INDIRECT.

The INDIRECT function in Google Sheets interprets a text string as a cell reference and returns the value of the cell.

For example, to sum the values in A1:A10, we usually use =SUM(A1:A10). In the case of INDIRECT use, it would be like =SUM(INDIRECT("A1:A10")) or, alternatively, you can enter A1:A10 in any other cell that doesn’t fall within A1:A10, like B1, and use the formula =SUM(INDIRECT(B1)).

The use of INDIRECT ranges is especially valuable for creating dynamic ranges.

Dynamic Indirect Range Example in Google Sheets

Let’s say you want to look up a value in a column and sum values up to that range in an adjoining column. If the value to find is “C” and the range to look for this value is B1:B, you can use XMATCH as follows:

=XMATCH("C",B1:B)

Syntax of the XMATCH Function:

XMATCH(search_key, lookup_range, [match_mode], [search_mode])

Where:

• "C": This is the search_key that you want to find in the range.
• B1:B: This is the lookup_range where you want to search for the item.

To total values up to that row in column C, concatenate the XMATCH result with “C1:C” to create a dynamic range string.

="C1:C"&XMATCH("C",B1:B)

You just need to use this output within INDIRECT and wrap it with SUM to calculate the total.

This setup allows you to dynamically adjust the range based on the position of the value “C” in column B, providing a flexible and automated way to sum values in Google Sheets.

Similar: Dynamic H&V Named Range in Google Sheets

How to Highlight an Indirect Range in Google Sheets

We can explore various combinations of functions for highlighting an indirect range in Google Sheets. I personally tested a few and chose the one with better performance.

Here is the formula for highlighting an indirect range in Google Sheets. I’ll explain the adjustments you should make to adapt it to your range, how to apply this within conditional formatting, and then provide an explanation of the formula.

=LET(range, $D$1, AND(
     ISBETWEEN(
          ROW(), 
          ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), 
          ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2)))
     ), 
     ISBETWEEN(
          COLUMN(), 
          COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), 
          COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2)))
     )
))

Modifying Formula to Highlight Indirect Range in Google Sheets

What changes should I make to this formula to highlight the indirect range in my sheet?

In my example, cell D1 contains the range string. So, replace $D$1 with the cell that contains the range string.

Alternatively, you can hardcode the range string within the formula. In that case, replace $D$1 with the range string such as "B5:C20".

Applying the Conditional Formatting Rule in Google Sheets

How do I apply this rule in conditional formatting?

To highlight the indirect range, use the above highlight rule as follows. The key point is that the smaller the range, the better the performance.

The rule should be applied from row #1 to the row and column you want. For example, if your sheet contains 1000 rows and 26 columns, you can use A1:Z1000 as the range to apply the formula.

But if you expect the range to be within columns B to G, then the apply-to range can be B1:G1000. You can further enhance performance by reducing the row numbers. If your indirect range will always fall within B10:G100, specify B1:G100 as the apply-to range.

Even if the range starts from the 50th row, the apply-to range must start from the first row. Here is how to apply it.

1. Select the range, for example, B1:E.
2. Click on Format > Conditional formatting.
3. Copy-paste the above highlight rule under the Custom formula.
4. Choose a light fill color and click Done.

This method allows you to highlight an indirect range in Google Sheets. The highlighting will adjust based on the range string in cell D1 or the cell you used in your formula.

Anatomy of the Formula (Highlight Rule)

The formula used to highlight an indirect range is straightforward and consists of two key parts.

Part #1:

ISBETWEEN(
          ROW(), 
          ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), 
          ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2)))
     )

Syntax of the ISBETWEEN:

ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_value_is_inclusive], [upper_value_is_inclusive])

Where:

• value_to_compare: ROW(), representing row #1.
• lower_value: ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), indicating the row number of the first part of the range string.
  • The SPLIT function divides the range string based on the colon (:) delimiter.
  • The CHOOSECOLS function extracts the first part from the split result and uses it as the range string in INDIRECT, and the ROW function returns the row number.
• upper_value: ROW(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), representing the row number of the second part of the range string.

Essentially, ISBETWEEN tests whether the current row number falls between the row numbers in the indirect range.

Part #2:

ISBETWEEN(
          COLUMN(), 
          COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 1))), 
          COLUMN(INDIRECT(CHOOSECOLS(SPLIT(range,":"), 2)))
     )

• This formula checks whether the current column number is within the column numbers of the indirect range.

Finally, the AND function evaluates whether both part #1 and part #2 are TRUE and highlights accordingly.

The role of LET is to name the range string as “range,” enhancing readability and simplifying formula editing.

Tips and Tricks for Highlighting Indirect Ranges

We can use the rule that highlights indirect ranges in many creative ways in Google Sheets. Let’s explore one such example.

We can utilize it to highlight a range based on two lookup values.

In the following example, I have the names of a few drinks in B2:B, table numbers in C2:C, and Qty in D2:D.

I want to highlight the table numbers 2 to 4 in the range B2:D. How can we achieve this?

Enter the search keys (table numbers) in cells E3 and E4. Then use the following XLOOKUP and CELL combo in cell F3:

=CELL("address", XLOOKUP(E3, C:C, B:B))

The XLOOKUP returns the item name “Miami Vice” corresponding to table #2, and the CELL function returns the cell address.

Syntax of the XLOOKUP Function:

XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Where:

• E3: This is the search_key (table number) you are trying to find in the range C:C.
• C:C: This is the lookup_range where you want to search for the value specified in E3.
• B:B: This is the resulut_range from which you want to return the corresponding value once the match is found.

Next, enter the following XLOOKUP and CELL combo in cell F4:

=CELL("address", XLOOKUP(E4, C:C, D:D))

The XLOOKUP returns the quantity (Qty. 1) corresponding to table #4, and the CELL function returns the cell address.

Combine these two strings in cell F1 using the formula:

=F3&":"&F4

In the highlight rule, replace $D$1 with $F$1. The apply-to range can be A1:Z1000, B1:D1000, or a much shorter B1:D50.

That’s all about how to highlight an indirect range in Google Sheets. Thanks for staying. Enjoy!