Easily Use Conditional Formatting to Find Duplicate Data in Excel - WindowsTips.net - Windows Tips and Tricks with Geek

Thursday, August 19, 2021

Easily Use Conditional Formatting to Find Duplicate Data in Excel

 Microsoft Excel Logo

Highlight Duplicate Data in Excel

Setting up conditional formatting rules can sometimes get complicated. Fortunately, locating duplicates in your Excel sheet using the feature isn’t one of those situations. You can actually accomplish this task in just a few clicks.

Start by selecting the cells you want to review for duplicates. If your entire spreadsheet is in question, you can select it instead by clicking the triangle in the upper left corner of the sheet.

Click the triangle to select the entire sheet in Excel

Go to the Home tab and the Styles section of the ribbon. Click “Conditional Formatting,” move to “Highlight Cell Rules,” and choose “Duplicate Values” in the pop-out menu.

Click Conditional Formatting, Highlight Cell Rules, Duplicate Values

When the Duplicate Values window displays, you should immediately see your duplicates highlighted with the default formatting applied. However, you can change this if you wish.

Conditional formatting defaults for duplicates

Confirm that “Duplicate” displays in the first drop-down box. Then, click the second drop-down box to select a different format. Click “OK” to apply the format to your duplicate data.

Pick a format style from the list

If you prefer a format that isn’t listed, click “Custom Format” in the drop-down box to pick a font, border, or fill style in the subsequent pop-up window. Click “OK.”

Create a Custom Format

You’ll see the Custom Format applied to the cell immediately. If you like it, click “OK” to apply it.

Custom Conditional Formatting in Excel

Once you have your duplicate data highlighted using conditional formatting, you can make the corrections or adjustments you need to. And once you do, the formatting will disappear as long as it’s not duplicated elsewhere in your cell selection.

Duplicates formatting and corrected in Excel

No comments:

Post a Comment