Excel Conditional Formatting – Highlighting Duplicates

Ever need to find duplicate cells in Excel? There are many ways you can do this. You can use a formula, Excel’s built-in “remove duplicates” functionality, even code a solution yourself in VBA.  However, you can use conditional formatting to see where the duplicate rows are without deleting them.  Normally, people use conditional formatting for highlighting simple things like if a cell is above or below a specific number, but with a simple formula, we can create out own custom conditional formatting scheme.

With that said, let’s say we have some data like so:

Excel Conditional Formatting - Data

We want to see where there are multiple Unique IDs since we don’t want any data with duplicate Unique IDs.

Before we apply a conditional format, let’s select the entire A column.

TIP: The reason for selecting the entire A column is to apply the formatting whenever your data increases to more rows.  If we just selected the data you have now, the conditional format will not apply to later cells when your data grows.

Now go to the Home tab –> Styles Group –> Conditional Formatting –> New Rule…

Excel Conditional Formatting

The New Formatting Rule dialogue box pops up.  Select “Use a formula to determine which cells to format” and the formula is the following:

Excel Conditional Formatting - new formatting rule

The first equal sign is to denote that we’re going to enter a formula.  Then the condition becomes A1=A2 which means to say if the value of A1 is the same as A2, then the condition is true and we want to apply a format.

Now let’s setup a format.  Click on the Format… button and go to Fill to put a light red background color.

Excel Conditional Formatting - format cells

Press OK.  You will be brought back to the New Formatting Rule dialogue box.  Click OK on this box, too.  Now the sheet is applying the format:

Excel Conditional Formatting - format applied

This is great, but how does it work?

We entered the formula as =A1=A2, so how does it know A11=A12?  The way that conditional formatting works is that it evaluates each cell that the conditional format is applied to.  And when it moves from cell to cell, so does the formula.  If you remember my cell references for beginners post, this formula is using relative referencing.  So you can imagine this formula as being copied from cell to cell, so the formula changes in each cell.  The conditional format formula in cell A11 is actually =A11=A12.

Wait a second…now the entire A column is pink after the data!

Excel Conditional Formatting

Nice catch!  Can you figure out why?  In cell A51, what would the conditional format formula be?

If you guessed =A51=A52, you deserve a cookie.  Go ahead, I won’t tell anyone 😉

So how do we fix this?

We have to tell the conditional format formula to ignore blank cells.  We need to change the formula for all of the cells that we applied the conditional format to.  This time, go to the Conditional Format Manager by going to Home tab –> Styles group –> Conditional Formatting –> Manage Rules…

Excel Conditional Formatting Manager

Depending on the cell you have selected, you may or may not see the conditional format you applied.  This is because the Conditional Formatting Rules Manager opens by showing you the conditional formatting of the current selection.  This is an important point to remember when editing conditional formats.  You wouldn’t want to fix just one cell’s conditional format formula when you meant to fix all of them.

Select “This Worksheet” for the “Show formatting rules for” dropdown to ensure we are editing the conditional formatting for the entire range we want.

Excel Conditional Formatting Rules Manager

Now select the rule we created by clicking in the text of “Formula: =A1=A2” and click “Edit Rule…”  Now change the formula to:

=AND(A2<>"",A1=A2)

We added the AND() function.  Check out Microsoft’s documentation on the AND() function for more info.  Basically, the AND() function helps us apply two conditions where both must be true to apply the formatting:

  1. The next cell below the current one must not be blank
  2. The current cell value equals the value of the next cell below

Press OK and then OK again.  Look below your data:

Excel Conditional Formatting

That looks much better 🙂

 

If you like this content and would like to be notified of new posts, please subscribe!

Questions?  Comments?  Please post a comment below!  I’d love to hear anything you would like me to write about or if you need me to clarify anything.

 

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

Suggest the next post!

I would love your help to know what I should post about next, so if you enjoyed this content and would like to see more, please let me know what you'd like me to talk about in the comments below. Thank you!

  • Adrian Samson

    Hi there,

    Thanks for the information on here. It’s very helpful.

    I’m using Excel for Mac 2011, and I am having a little difficulty with the conditional formatting. I’m using it to identify duplicates on rows, but it’s highlighting blanks as well. Please advise on what type of formula must I use to avoid that. It’s for rostering for a church, so there could be potential clashes if a person is rostered on to serve in more than one area on a given week.

    Please help.

    Thanks!

    • Hi Adrian,

      I don’t have a Mac, but I was able to use a friend’s real quick and I was able to get the highlighting done using the method I posted here. I would check if the blanks are actually a space that it’s finding. Also, make sure you sort the data by the column where you’re looking for duplicates (don’t forget to expand the sort selection to include the rest of your data).

      I hope this helps. If anything, please email me with a screenshot of what you’re getting and maybe I can help that way.

      Thanks,
      Joseph

      • Adrian Samson

        Thanks for the quick response, Joseph!

        I’ll email you right away with more details on my document.

        Adrian

  • sss

    Hi, I need formula for the \ highlighted one considering both same in B and c only

    It means highlighted one florida in ur screenshot

    • You can add another condition to the AND formula:

      =AND(B2<>“”,B1=B2,C1=C2)

  • Emily

    Hello, Google posted this POST in the first 10 posts of the question I typed.
    I have a Weekly Manpower Spreadsheet. As a contractor, we might have employees who work at one location a whole week and others who work at multiple locations (we have had up to 20 locations in one week’s time where we have crews working). My spreadsheet consists Two Header rows, Row A has 7 cells merged and the Company name goes there, underneath the merged cells in row 2 I have the days of the week(abbreviated). Employee names in Column A, to make sure I don’t have an employee booked in 2 place on the same DAY, I have made a conditional format by Highlighting the Cells in Row 3 that represent Sunday (I have 20 cells highlighted) then I go to “Conditional Formatting / Highlighted Cell Rules / Duplicate Values”, I choose the color for the Alert and the Font. Done, Duplicate this on Row 3 for Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.
    I tried copying and pasting Row 3 into Row 4, but, that doesn’t work, because when you select individual cells, these were Absolute Cell References (the ones with the $ sign in front of the Column AND the Cell Reference).
    I have tried to copy and Past FORMATTING, but it still brings over all of the cell reference from Row 3 into Row 4.
    I tried to Make a Macro using a Word Doc and pasting each day’s formula and changing just the Row #, and copying, but I am unable to make the Macro work.
    Now editing, copying, and pasting is working, but I have a spreadsheet I am trying to build for 200 employees, that’s 1,400 cells I have to individually edit.
    HELP!!! (pretty please)
    Emily

  • Julie Martindale

    Hi Joseph! I’m searching for an answer to my seemingly rare, or just annoying problem with conditional formatting and highlighting duplicates….
    Sometimes (and i haven’t figured out why), when I use conditional formatting to highlight duplicates, they are indicated by the red font, but not the light red fill, so it’s harder to see…in the spreadsheets…
    Any idea why?
    Thanks in advance!
    -Julie

    • Joseph

      Hi Julie,

      It’s hard to tell without seeing the workbook, but here’s my best educated guess:

      It sounds like you have 2 conditional formatting rules set for the cells you’re talking about: one for the red text, and another for the light red background color. Select the cells that you have the conditional formatting for, then go to the Conditional Formatting Rules Manager by clicking on Home->Conditional Formatting->Manager Rules…

      From there, see if you have multiple rules set for the same range of cells. It sounds like your red text rule might not match the light red background rule. If you can combine them into one, then you can click on the conditional formatting rule with the red text and edit it to also have the light reg background color. Then you can delete the other rule (if you’re sure that’s what you can do).

      Be sure to try this out on a test workbook first (open up the problem working, and click File -> Save As… and save it as a copy in case you make any mistakes while playing around with the conditional formatting).

      Best of luck!
      Joseph

      • Julie Martindale

        Thanks Joseph! Although the red fill to indicate duplicates didn’t appear when I cleared all conditional formatting from the worksheet; I was able to get it to work after clearing all formatting first, then applying the conditional formatting to find dupes.
        So strange!
        Anyway, thank you for your help!!
        Julie

      • Julie Martindale

        *conditional formatting and formatting being two different things… 🙂

  • Artemis2002 .

    This formula highlights the first duplicate cell, how would I highlight the second duplicate cell (ie. if I have A1 with a value of 4 and A2 with a value of 4, I need A2 to be highlighted)? I already tried reversing the references, with A2=A1, but it has made no difference. (the order matters, as I create a master list of employee certifications from another spreadsheet, and identify duplicate entries by email address, so I would want to maintain A1 – the originally maintained entry, and see that A2 is the duplicate that was added to the list.)

    Any help would be greatly appreciated!

    • Joseph

      Hi Artemis,

      If you have a Header row, like A1 says “My Data” for example, then what you can do is have 2 Conditional Formatting formulas.

      If your data is in A2:A5, then one conditional formula would be “=A2=A3” like normal. The next formula would be offset by 1 row above, like “=A1=A2”.

      Here’s an image to show you what I mean. I highlighted the duplicates in different colors so you see which rule is affecting either the first or second duplicate (and even a third).

      Hope this helps!
      Joseph