Learn How to Highlight Rows in Excel Based on Duplicates – AccountingWEB.com

We’re no longer updating this site but we continue to support the global accounting community and will direct this domain to accountingweb.co.uk soon.
Find out more
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:
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:
=COUNTIF($A$1:$A$8,$A1)>1
The COUNTIF function has two arguments:
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.
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:
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:
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.
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.
Comments for this post are now closed.
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
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.
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?
Great information, Thanks 🙂
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.
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.
Very Helpful. when I used Excel 2010
Does not work with Excel 365 – at least not with mentioned function.
Is there a addon or another function
Thanks

source

Leave a Comment