How to Highlight Duplicates in Excel

Highlight Duplicates in Excel. There can be Duplicates in Excel.As data sets grow larger, it becomes harder to control, accidentally entering the same data again, and when done, the data in the wrong row starts to get annoying after a while. Assume that a market that sells fruit and vegetables follows product data excel and produce a sample data set.

Duplicates in Excel data is sometimes useful, and sometimes it makes it difficult to understand . Use conditional formatting to find and Highlight Duplicates in Excel. So you can decide whether to remove duplicates or delete them.

How to Highlight Duplicates in Excel

Highlight Duplicates in Excel

1-Select the cells you want to check for duplicates.

2-Click Entry> Conditional Formatting> Highlight Cell Rules> Duplicate Values.

3-In the box next to Contains values, select the format you want to apply to duplicate values, and click OK.

How to Remove Duplicates in Excel

When you use the Remove Duplicates feature, the duplicate data is permanently deleted. Before remove duplicates, it is a good idea to copy the original data to another worksheet to avoid losing any data by mistake.

1-Select the cell range containing the duplicate values that you want to remove.

2-Click Data> Remove Duplicates, and under Columns, check or uncheck the columns for which you want to remove duplicates.

3-In the Remove Duplicates box, check the data you want to remove.

4-Click OK.

Apply conditional formatting to Duplicates in Excel

Quick formatting

1-In the range, select one or more cells in the table or PivotTable report.

2-In the Style group of the entry tab, click one of the following beside the Conditional Formatting option, and then click Highlight Cell Rules.

3-Select the Duplicate Values item.

4-Enter the values you want to use and select a format.

Advanced formatting

1-In the range, select one or more cells in the table or PivotTable report.

2-In the Styles group of the Entry tab, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

-To add a conditional format, click New Rule.

The New Formatting dialog box appears.

-To change the conditional formatting, do the following:

Show formatting rules: make sure that the appropriate worksheet or table is selected in the list box.

-Optionally, to change the cell spacing temporarily hide the dialog box by clicking the Collapse Dialog Box button in the Applies To places, select the new cell range on the worksheet, and then select Expand Dialog Box image.

-Select the rule and click Edit rule.

The Format Edit Rule dialog box appears.

4-Under Select Rule Type, click Format only unique or duplicate values.

5-Under Edit Rule Description, select the unique or duplicate item in the Format all list box.

6-Click the Format option to display the Format Cells dialog box.

7-Cell value, when conditioned; select the number, font, border, or padding format that you want to apply, and then click OK.

You can select multiple formats. The formats you selected appear in the Preview pane.[:]

How to Highlight Duplicates in Excel
5 (100%) 1 vote

You might also like