Highlight Duplicates with Conditional Formatting in Excel
If you’ve never used conditional formatting in Excel, it can be a little intimidating at first. But once you use it a few times and understand the flexibility and power behind it, you may start using it more and more. For this tutorial, highlighting duplicate data with the conditional formatting feature is no more than a few clicks. So it’s easier than you might think. Open your spreadsheet in Microsoft Excel and follow these steps.
Select the cells you want to apply the formatting to by dragging your cursor through them. If you want to check the entire sheet, click the triangle in the upper-left corner (where column A and row 1 meet).Go to the Home tab and then the Styles section of the ribbon, click Conditional Formatting.In the drop-down box, move your cursor to the first option for Highlight Cell Rules.In the pop-out box, select Duplicate Values.When the small pop-up window displays, confirm that the first drop-down shows Duplicate. In the Values With drop-down box, choose the formatting you want to apply. You can select from colors to fill the cells, for the font, as a border, or a custom format if you prefer.Click OK.
You’ll then see all duplicate data highlighted with the color option you picked. If you’re doing this in Excel on Mac, the steps above are the same. However, the pop-up window to select the formatting is slightly different. Be sure to keep the boxes for Style, Format Only, and Duplicate values as-is and as shown in the screenshot below. Then, use the Format with drop-down box at the bottom to choose the fill, text, or border color, or use a Custom Format.
Create a Custom Format
You can pick from six formatting options. But if you don’t like these choices, you can customize one of your own. In the Values With (Windows) or Format With (Mac) drop-down, choose Custom Format.
When the Format Cells window opens, use the Font, Border, and Fill tabs at the top to create your custom format. As examples, you can use strikethrough for text, a purple dotted border for cells, or a dark blue fill color.
You can also combine multiple formats. For instance, you can use an italic font, colored yellow, with a blue patterned fill, and a bold black border. The key is to use formatting that works well for you to easily identify those duplicates. When you finish creating the custom format, click OK and you’ll see the changes apply to your sheet instantly.
Find Duplicates in Excel Quickly with Conditional Formatting
What you decide to do with your duplicates after you identify them is up to you. But at least you now know how to highlight duplicates in Excel the exact way you want. If you use Google Sheets instead of or in addition to Excel, check out our how-tos for applying conditional formatting in Sheets and highlighting duplicates in Google Sheets too.
