Excel Conditional Formatting with Named Range (3 Examples)

In this article, you will learn to use Excel conditional formatting with named range. You will see the use of named range in conditional formatting array formula, multiple sheet referencing, and also in dynamic entries.

Conditional formatting with named ranges has practical applications in various fields. It helps highlight budget variances in finance, project delays in management, low inventory levels in supply chain, and employee performance in HR.

In education, it assists with grade assessment, while in sales and marketing, it identifies high-value opportunities. In health, it flags abnormal medical data, and in fitness, it tracks workout achievements.

Additionally, it aids quality control, environmental monitoring, and more, streamlining data analysis and decision-making.

Download Practice Book

You can download the free Excel workbook here and practice on your own.

How to Use Excel Conditional Formatting with Named Range

Example 1: Using Array Formula with Named Range in Conditional Formatting

First, you must name the range you want to use inside the conditional formatting formula. Select the range C5:C14 and click on Formulas >> Define Name.

A dialogue box named “New Name” will open. Inside Name: field type any appropriate name. Refers to: is filled by default. Click on OK. The range C5:C14 is now named as Age_Range.

Then, select the range you want to format and click on Home >> Conditional Formatting >> New Rule…

Inside the dialogue box named “New Formatting Rule” select Use a formula to determine which cells to format. Under Format values where this formula is true:, type the following formula:

(which is an array formula as it takes array C15:C14 as an input)

Click on Format…

A dialogue box named “Format Cells” will open. Under the Fill tab, choose any appropriate color and click on OK.

Again, inside “New Formatting Rule” click on OK.

This will format the cells corresponding to the children whose ages are greater than the average age with red fill.

Again to highlight cells, corresponding to those whose ages are below the average age, follow the same procedure as before with a slightly modified formula, which is:

The children of ages greater than the average age are in red group and those whose ages are less than the average age are in green group.

Example 2: Using Conditional Formatting with Named Range on Another Sheet

Inside the sheet named “Another_Sheet2”, select the range B5:B9 and click on Formulas >> Define Name. Type an appropriate name. The Refers to: is filled by default. Click on OK.

Then, go to the sheet named “Another_Sheet” and create a new conditional formatting rule like before. Use the following formula:

Assign appropriate formatting as before.

This action will highlight the names in “Another_Sheet”, which are also available inside the range named “Name_Range” inside the “Another Sheet2” sheet.

Example 3: Conditional Formatting with Dynamic Named Range

To make the outcome of example 2 dynamic, first, we need to convert the dataset in sheet “Dynamic_Range2” into an Excel table.

Select the Range B4:C9 and press CTRL+T. Inside the “Create Table” dialogue box, click on OK.

The selected range will convert into an Excel table. Then, give an appropriate name to the table as before which can be “Name_Range_Table”.

Now, go to sheet “Dynamic_Range” and insert a new formatting rule for range B5:B14 as before. Use the following formula inside the formula box:

Now you see the names in sheet “Dynamic_Range” which are also present inside the table in sheet “Dynamic_Range2” are highlighted.

To test if the output is dynamic or not, enter a new name inside the table in sheet “Dynamic_Range2” and observe that the same name in sheet “Dynamic_Range” is highlighted simultaneously.

Things to Remember

• Make sure you correctly define the named range to include the desired cells. The named range should be appropriate for your specific data, and it should not include any unnecessary cells or exclude relevant ones.
• In the case of new naming, avoid the name that is already present in the same workbook.
• The order of the conditional formatting rules matters. Excel applies rules from top to bottom. Make sure to arrange the rules in the desired priority so that the formatting you want takes precedence.

Frequently Asked Questions

• What happens if I modify or delete the named range used in conditional formatting?

If you modify or delete the named range, the conditional formatting rule will lose its reference, and the formatting may not be applied as intended.

• Can I use multiple named ranges in a single conditional formatting rule?

Yes, you can use multiple named ranges in a single conditional formatting rule by incorporating them into the formula as needed.

Conclusion

By going through this article, I hope, you can now effectively use Excel conditional formatting with named range to apply formatting rules consistently and accurately across your Excel worksheets.

To further expand your knowledge and expertise in Excel, I encourage you to visit Exceldemy, a valuable resource for in-depth information on various uses of Microsoft Excel. There, you can explore a wide range of tutorials, tips, and tricks that will empower you to leverage the full potential of this powerful spreadsheet software.