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:
- Select cells A1:A8.
- On Excel’s Home tab choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.
- 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.
- 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:
- Select cells A1:D8. In this case we want to select the entire table of data, instead of a single column.
- Choose Conditional Formatting from the Home Tab, and then choose New Rule.
- Choose Use a Formula to Determine Which Cells to Format from the New Formatting Rule dialog box.
- Enter the following formula in to the Format Values Where This Formula is True field:
Content seriesView full content series
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.
- 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:
- Select cells A1:D8.
- Choose Conditional Formatting, then New Rule.
- In the New Formatting Rule dialog box change the Style to Classic.
- Change Format Only Top or Bottom Ranked Values to Use a Formula to Determine Which Cells to Format.
- Enter this formula: =COUNTIF($A$1:$A$8,$A1)>1
- 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 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.