Errors can occur whether data input is being done manually or through an external source. Microsoft Excel's conditional formatting makes it easy to quickly determine whether duplicate data exists.

You may have information on clients, locations, suppliers, or other entities where duplicate data can be problematic. However, duplication could be a problem if your data includes IDs for products, orders, or payments. Here's a quick way to search through your Excel sheet for duplicate data.

Conditionally Formatting Duplicate Values in Excel

Complexity might occasionally arise while setting up conditional formatting rules. It's a good thing that using the feature to find duplicates in your Excel sheet doesn't happen in certain circumstances. Actually, you only need a few clicks to do this work.

The first step is to choose the cells you wish to check for duplicates in. The triangle in the top-left corner of the spreadsheet can be clicked to select the complete document if your entire spreadsheet is in doubt.

Navigate to the Home tab and Styles area of the ribbon. Select "Duplicate Values" from the pop-out menu under "Conditional Formatting," then click "Highlight Cell Rules" to select a cell rule.

Your duplicates should be highlighted and formatted according to default formatting when the Duplicate Values box appears. But if you'd want, you may modify this.

Verify that the first drop-down box's "Duplicate" option is visible. After that, choose a different format by clicking the second drop-down box. For the format to be applied to your duplicate data, click "OK."

Click "Custom Format" in the drop-down box to choose a font, border, or fill style in the next pop-up window if the format you like isn't provided. Press "OK."

The Custom Format will be instantly seen applied to the cell. Click "OK" to apply it if you like it.

With conditional formatting, you can identify duplicate values in excel and then make the necessary changes or revisions. As long as it isn't duplicated somewhere else in your cell selection after you apply the formatting, it will vanish.

Remove Duplicates

Let's look at how to remove duplication from a list now that you understand how to spot them. The word "remove" is key in this first approach, which means that the feature really deletes the duplicate values in Excel. Accordingly, if you require a complete set of all the values, make sure you first make a backup of the list you're processing:

1. Choose a cell from your list by clicking on it.
2. Activate Data menu.
3. Press the Remove Duplicates button.
4. In the Remove Duplicates dialog box, click OK.
5. When the window asking you how many duplicate values were identified and how many still exist appears, click OK.

​If you discover that you removed duplicates hastily and need your original list back, click Undo or press Ctrl-Z.

UNIQUE Function

If you're using Microsoft 365 or Excel 2021, you can also get rid of duplicates using a worksheet function. The UNIQUE function operates in real time and dynamically offers a list of unique things, in contrast to the manual nature of Remove Duplicates. Additionally, because UNIQUE generates a list of unique values in a different location in your spreadsheet, your original list remains unchanged. The UNIQUE function has 3 arguments:

• Array: The range of cells from which you want to display distinct values.
• By_col: By default, this parameter retrieves unique values from a column and is set to TRUE. If you want to return unique values from across a row, enter FALSE.
• Exactly_once: Enter FALSE to display a single instance of each unique value regardless of how many times it appears in the list, or TRUE to display the items that exist in a list precisely once.

The options "by col" and "exactly once" are optional and should only be used when necessary. Additional "UNIQUE" will produce #CALC! if the exactly once argument is set to TRUE but no items in your list display exactly once.

The UNIQUE function is a dynamic array function, therefore even if the formula is only entered in one cell, the results will be displayed in as many extra cells as are required. A #SPILL! error will appear if any data is in the way or if you alter the results of the UNIQUE function. You may resolve this problem by deleting any unnecessary data from the region where UNIQUE likes to return its results.

When you click on the #SPILL! error, a border will show up around this area. The figure below illustrates how to use the SORT and UNIQUE functions to automatically sort your list of unique values.