Excel Conditional Formatting with Named Range (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview to Excel Conditional Formatting Named Range


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.

Naming Age Range for Array Formula

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

Selecting New Rule for Conditional Formatting

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:

=C5:C14>AVERAGE(Age_Range)

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

Click on Format…

Inputting Formula for Conditional Formatting

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

Selecting Fill Color

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

Confirming Formatting for Array Formula

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:

=C5:C14<AVERAGE(Age_Range)

Inputting Formula for Below Average

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.

Final Look for Conditional Formattting with Named Range and Array Formula


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.

Naming Name_Range for Another Sheet in Excel Conditional Formatting with Named Range

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

=COUNTIF(Name_Range, B5)>0

Assign appropriate formatting as before.

Formula for Another Sheet

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

Highlighted Children in Special List for Excel Conditional Formatting with Named Range


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.

Creating Table for Dynamic Range


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”.

Naming Table for Excel Dynamic Conditional Formatting with Named Range

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:

=COUNTIF(Name_Range_Table, B5)>0

Inputting Formula for Excel Dynamic Conditional Formatting with Named Range

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

Before Dynamic Entry in Excel Conditional Formatting with Named Range

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.

After Dynamic Entry in Excel Conditional Formatting with Named Range


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo