Learn How to Highlight Rows in Excel Based on Duplicates

Spreadsheets and graphs on a desk
xfgiro/istock

As you may be aware, Excel’s Conditional Formatting feature allows you to identify duplicates in a column. This technique requires some tech savvy in certain versions of Excel, but only a couple of mouse clicks in Excel 2007 and later. A reader named Chris asked me if it’s possible to extend this functionality to not only identify duplicates in a single column, but further highlight the adjacent cells in the same row. In this article I’ll first cover show how to identify duplicate values in a single column, and then show how to extend the functionality in the fashion Chris is seeking. I’ll do so first for Excel 2007 and later, but will then provide alternate instructions for Excel 2003 and Excel 2011 for Mac.

Chris has data structured somewhat like Figure 1, so let’s first identify the duplicate instances of Joe in column A in Excel 2007 and later:

  1. Select cells A1:A8.
  2. On Excel’s Home tab choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.
  3. In this case, Excel displays the Duplicate Values dialog box and also provides a preview of the formatting to be applied to your worksheet, as shown in Figure 1. Notice that the Duplicate Values dialog box is dual-purpose, as you can choose Unique Values instead of Duplicates.
  4. If the default red/pink combination isn’t to your liking, click the drop-down list on the right and then choose Custom Format to display a Format Cells dialog box from which you can make a variety of choices. Click to confirm your choices.

Figure 1: Use Conditional Formatting to highlight duplicates within your data.

Although fast and easy, this approach only applies to a given range, and cannot affect cells outside of the range that you selected. We’ll need to dig a bit deeper to accomplish Chris’ goal by creating a custom rule. Applying conditional formatting in this fashion is nuanced, so read closely:

  1. Select cells A1:D8. In this case we want to select the entire table of data, instead of a single column.
  2. Choose Conditional Formatting from the Home Tab, and then choose New Rule.
  3. Choose Use a Formula to Determine Which Cells to Format from the New Formatting Rule dialog box.
  4. Enter the following formula in to the Format Values Where This Formula is True field:

=COUNTIF($A$1:$A$8,$A1)>1

The COUNTIF function has two arguments:

  • Range: In this, our range is cells $A$1:$A$8. The dollar signs instruct Excel to look at all rows in the first column of our data set.
  • Criteria: The Criteria in this case is $A1. Notice that there’s no dollar sign in front of the row. We want Excel to always look at column A, but to shift the row number for each row of our data set.

The >1 portion of the formula instructs Excel that we only want the rule to get applied when the result of the COUNTIF function is greater than 1, which means only rows with a duplicate value in column A will be formatted.

  1. Click the Format button, make formatting selections to your liking, and then click OK twice to apply the formatting. The end result should look like Figure 2.

Figure 2: The rows with a duplicate value in column A are now formatted.

When applying conditional formatting in this fashion the dollar signs around the cell references are critical. There’s an intangible aspect to Conditional Formatting because you’re entering one formula that needs to cover multiple rows. Thus, if you omit any of the dollar signs referenced above the range that the conditional formatting looks at, it may shift and as a result either not find the duplicate values or not highlight the given rows. The old quote “If at first you don’t succeed, try, try again” definitely applies to Conditional Formatting. If your rule doesn’t work as expected, choose Conditional Formatting, Edit Rule, and then double-click on your rule and verify your formula.

To replicate the Highlight Duplicates feature in Excel 2003, as illustrated in Figure 3:

1. Select cells A1:A8.

2. Choose Format and then Conditional Formatting.

3. Change Cell Value Is to Formula Is and then enter this formula: =COUNTIF($A$1:$A$8,$A1)>1

4. Click the Format button, choose the desired formatting, and then click OK twice.

The steps to highlight the entire row are almost identical, simply select cells A1:D8, and then repeat steps 2 through 4 above.

Figure 3: Follow these steps to highlight duplicate values in Excel 2003.

You can access the Conditional Formatting feature from either the menu bar or from Excel’s ribbon in Excel for Mac 2011. If you use the ribbon, identifying duplicate values in Excel for Mac 2011 is identical to the steps in Excel 2007 and later. The steps to highlight the rows with duplicate values are slightly different:

  1. Select cells A1:D8.
  2. Choose Conditional Formatting, then New Rule.
  3. In the New Formatting Rule dialog box change the Style to Classic.
  4. Change Format Only Top or Bottom Ranked Values to Use a Formula to Determine Which Cells to Format.
  5. Enter this formula: =COUNTIF($A$1:$A$8,$A1)>1
  6. If the default red/pink combination isn’t to your liking, click the Format With drop-down list and then choose Custom Format to display a Format Cells dialog box from which you can make a variety of choices. Click OK twice to confirm your choices.

Figure 4: Create a new rule to highlight the rows that contain a duplicate value in column A.

To remove conditional formatting from a range of cells:

  • Excel 2007 and later: The brute-force method is to choose Home, Conditional Formatting, Clear Rules, and then Clear Rules from Entire Sheet. If you wish to be more nuanced about your removal, select a range of cells, and then use Clear Rules from Selected Cells.
  • Excel 2003 and earlier: Select a range of cells that contain Conditional Formatting, and then choose Format, Conditional Formatting, and then click the Delete button. Click the checkboxes for the conditions you wish to delete.
  • Excel 2011 for Mac: Select a range of cells that contain conditional formatting, and then choose Format, Conditional Formatting, and then click the minus sign in the lower left-hand corner of the dialog box after you select the rule you wish to delete.

About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

Replies

Please login or register to join the discussion.

avatar
Dec 7th 2016 08:47

Hi David,

Thanks a lot for the above examples.

Could you please help me out with similar issue I am facing.

I have 63000 entries on Sheet 1 and I need to find 148 entries from those 63000 entries. Is it possible to highlight the cells?

Sorry I forgot to add 63000 entries in Sheet1 and 148 entires in Sheet2.

Thanks

Thanks (2)
avatar
By mwlee
Sep 5th 2017 17:00

Hello. Normally, I have a simple sheet of 2 columns with various values and names in which I need to find duplicates. The easiest way was to use conditional formatting to find the duplicates, which I had done for years. Unfortunately, it no longer works. Either it highlights nothing or randomly highlights cells that I know are not duplicates. I am currently using Excel 2013. Do you know how to get it to work again? Thank you.

Thanks (0)
avatar
By KrisB
Mar 6th 2018 21:11

I got this to work except for if a column contains nothing, then it doesn't count that as a match. I pasted rows that match up on the 5 columns I indicated in my COUNTIFS formula, but I have to remove one of those columns as it typically doesn't have any data entered into it (it's header is "special features," and most entries don't have that.)

If I compare the 4 columns with entries instead of the 5 columns (incl. the one with absence of data), it works.

Here's my formula:
=COUNTIFS($B:$B,$B1,$C:$C,$C1,$D:$D,$D1,$F:$F,$F1,$G:$G,$G1)>1

I want to include column "E", but then the conditional formatting rule fails to identify duplicates.

Why doesn't the conditional formatting rule understand that the absence of an entry is still a value (value of "null"?), and compare?

Thanks (0)
avatar
By priya01
Mar 24th 2018 05:36

Great information, Thanks :)

Thanks (0)
avatar
By Shazam
Oct 1st 2018 09:53

Hi,
I have a sheet with supplier data (on column D), and invoice number on (column B), I would like to highlight duplicated values in column B for a specific supplier, how can I do it.

Thanks (0)
avatar
Oct 19th 2018 11:32

Dear David,

thank you for the above examples. Can you give me an advice?
I have a column A with all phone numbers of the company, Column B with corresponding amount and column C with part of the numbers of the company. So i want to check and mark, which numbers are duplicated from column C in Column A. I tried to use =countif but there is the following message: "you have entered too few arguments for this function" Please help.

Thanks (0)