Hello Excellers, welcome back to another blog in the #formulafriday series.  Today let’s look at how to combine conditional formatting with an Excel formula.  What are we going to achieve with that?.  Well, we can easily identify duplicate values using the combination of these features.  So let’s take the problem that John has.  He has two lists of guests to a retirement party.  Another colleague of his has also been collating a list and John needs to identify duplicate names quickly and get one master list used for the party.  He has the two lists List 1 and List 2.

So, the method John wants to use is to highlight visually any duplicate names on the two lists he and his colleague have.  Of course with as little effort as possible.  The results he wants to see are in the screenshot below.

Conditional Formatting Basics.

Excel comes with a number of preset conditions that you can use to conditionally format your Excel cells.  You can see the options some super quick options available by selecting the following options

Home Tab| Conditional Formatting

If you want to get a bit more flexible then select the following options

Home Tab | Conditional Formatting | Create New Rule. 

The last option in the New Formatting Rule dialogue box is where we can get a bit more creative.  Use your own custom formulas to create any number of conditional formatting rules.  The Conditional Formatting must return a TRUE or FALSE or numerical equivalent.  An example of some formulas that work perfectly with this type of formatting.

=A2>50

=ISNUMBER

=ISODD

When the formatting is applied to a range of cells, the key is to enter the formula cell references that begin with the first row and column (or the top left cell) in the range.  The formula is then applied to each cell in the range respectively.  Just think of how you normally create formulas and then copy the formula to the rest of the cells you want to.  So, back to John’s issue today, how to write a conditional formatting formula to identify duplicated name entries in hi party lists

Let’s Write Our Conditional Formatting Formula

John’s Solution is s simple one.  We can use a formula to highlight any cells that contain duplicate data which returns TRUE if a cell value appears more than once.

• Select Home Tab | Conditional Formatting | Create New Rule
• In the Edit rule Description | Format where this formula value is true, type the following formula

=COUNTIF($B$3:$D$14,B3)>1

• Then select your formatting is the result if TRUE
• Hit Ok

It is  important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B3 in this case.

How This Formula Works.

COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule.

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but B4 is fully relative. So, the rule is evaluated for each cell in the range, with B3 changing and $B$3:$D$14 remaining unchanged.

 

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

 

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.  All you need to do is choose the Excel task that most closely describes what you need to be done.  Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements.  Try It!.  Need Help With An Excel VBA Macro?.  Of course, you don’t need to ask how to list all files in a directory as it is right here for free.