How to Use Named Ranges in Conditional Formatting in Google Sheets

Published on

First of all, let me clarify that we can use named ranges in conditional formatting using INDIRECT in Google Sheets. Before delving into an example, let’s explore some of the benefits of utilizing it in conditional formatting.

The most significant advantage is that the named ranges enhance the readability and understanding of your highlight rules. When you write a formula for other users or in a collaborative sheet, they can easily comprehend the formula.

If you rely on cell references, users may need to inspect the content in the cell/range references to grasp the formula. It is crucial to use meaningful names when assigning ranges.

Another advantage is that coding rules become more manageable because you won’t lose momentum when dealing with lengthy formulas.

But that doesn’t mean there are no drawbacks. Some drawbacks come to my immediate attention, which I have pointed out at the end of the tutorial. You can understand them once you have gone through all the examples below.

Examples of Using Named Ranges in Conditional Formatting

Here are a few examples of using named ranges in conditional formatting in Google Sheets. Once you have gone through these examples, you will become proficient in utilizing them in Google Sheets.

The first example highlights a cell range based on a named range. The other two examples highlight the named range itself. I’ve tried to bring some diversification in choosing the examples.

a. Match Values in the Named Range and Highlight

In the first example, we will name the range A1:A10 with the name “fruits” and highlight all the fruits in column E that are present in the named range. Please pay attention to assigning the name, as in the later examples, I won’t explain those steps again.

Steps:

Select the cell range A1:A10.

Click within the name box (the field left to the formula bar) and type “fruits,” then hit enter. Alternatively, go to Data > Named ranges and enter the name.

Naming the range A1:A10 as 'fruits'.

Go to Format > Conditional formatting and enter the range to highlight below “Apply to range.” Here, enter E1:E.

Below that, under “Format rules,” select “Custom formula is” and enter the following formula:

=MATCH(E1, INDIRECT("fruits"), FALSE)>0

Click Done.

Matching values in the named range and applying highlighting.

That’s it. All the values in column E that are present in fruits will be highlighted.

How does the usage of named ranges in conditional formatting differ from that of cell usage?

In regular formulas, you can use the named range as it is, whereas in conditional formatting, you should use it within the INDIRECT function as a string.

In the above example, we have used INDIRECT("fruits"), whereas in a regular formula, you can use it as fruits.

b. Highlight Values within the Named Range Area

In this example, we have a list of dates in B2:B7, named as holidays.

The objective is to highlight any holiday that falls on today or tomorrow. How do we achieve this using the named range holidays in the highlight rule for conditional formatting?

Here is the highlight rule (formula) to use in the custom formula rule for the apply-to range B2:B7:

=OR(INDIRECT("holidays")=TODAY(), INDIRECT("holidays")=TODAY()+1)
Highlighting values within the named range area in Google Sheets.

We have used an OR logical test to determine whether the dates in holidays match TODAY() OR TODAY()+1.

c. How to Use Named Ranges in Conditional Formatting to Highlight Duplicates within that Range

In our first example, we used the named range fruits for the range A1:A10. Let’s employ it for this example as well.

To highlight duplicates within a named range, we need to utilize the OFFSET function.

Formula Rule:

=COUNTIF(OFFSET(INDIRECT("fruits"), 0, 0, ROW(A1)), OFFSET(INDIRECT("fruits"), ROW(A1)-1, 0, 1))>1

You can apply this formula to the range A1:A10 to highlight duplicates within that range.

How to use named ranges in conditional formatting to highlight duplicates within that range.

This serves as another example of using named ranges in conditional formatting in Google Sheets.

Are There Any Drawbacks to Using Named Ranges in Conditional Formatting in Google Sheets?

I could point out two possible drawbacks:

1. Absence of Dollar Signs in Named Ranges:

In regular ranges, you can employ dollar signs to create relative or absolute references. For instance, using $A$1:A1 in a custom highlight rule, with the apply-to range A1:$A10, adjusts to $A$1:$A2 in row #2, $A$1:$A3 in row #3, and so forth.

When using a named range within conditional formatting, achieving this requires the use of OFFSET, as demonstrated in the last highlight rule.

2. Challenges in Adapting to Dynamic Ranges:

The second drawback involves making the approach adaptable to dynamic named ranges. We can’t use dynamic named ranges for highlighting.

Resources

This article has been a guide to using a named range in conditional formatting in Google Sheets. You can learn more about Google Sheets from the following articles:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.