Home > Format > Conditional > Duplicates Excel Highlight Duplicate Values In some Excel lists, duplicate values might be a problem. You can use Excel conditional formatting to highlight duplicate values, so it's easy to spot them quickly. And in a report, duplicate headings can be hidden, so the list is easier to read!

1) Highlight Duplicates in Column

Use Excel conditional formatting to highlight values that are duplicate entries in a specific column, or in a range of cells (multiple rows and columns).

• ✅  To follow along, get the Excel sample file in the download section

To highlight all the duplicate values, follow these steps:

1. Select the cells to format -- range A2:A7 in this example
2. On the Ribbon's Home tab, click Conditional Formatting
3. Click Highlight Cell Rules, then click Duplicate Values
4. In the Duplicate Values dialog box, the default format is Light Red Fill with Dark Red Text
   • If you prefer, select one of the other formatting options
   • Or, for more formatting options, click the Custom Format command
5. Click the OK button, to apply the conditional formatting.

On the worksheet, all of the duplicate numbers in column A are highlighted with light red fill colour, and dark red font.

2) Highlight Duplicate Records in a List

To highlight the duplicate records in a list, you can use conditional formatting. Add a formula in one column first, to string all the data together.

You can see the steps in this video, and the written instructions are below the video.

Highlight Duplicate Records in a List

You can use Excel conditional formatting to highlight duplicate records in a list.

First, you'll set up a formula to combine all the fields into one column, then test that column for duplicates.

Combine Date with Formula

First, create a formula to combine the data:

1. In this example, the data is in cells A2:F8
2. In cell G1, add the column heading "AllData"
3. In cell G2, enter the formula to combine all the data:
   =CONCATENATE(A2,B2,C2,D2,E2,F2)
4. Copy the formula down to the last row of data

Highlight Duplicate Records

Next, follow these steps, to add the conditional formatting which will format values that are duplicates:

• Select the cells to format -- range A2:F8 in this example
• On the Ribbon's Home tab, click Conditional Formatting
• Next, click New Rule, to open the New Formatting Rule dialog box
• Click Use a Formula to Determine Which Cells to Format
• For the formula, enter use COUNTIF function (see notes below)
  • =COUNTIF($G$2:$G$8,$G2)>1
• Click the Format button.
• Select a font colour for highlighting - light red fill in the screen shot below.
• Click OK, click OK

COUNTIF Formula Notes

1) Occurrences: The COUNTIF function counts the occurrences of each row's combined text, starting from row 2, and down to the formula's row. If there is more than one occurrence, the row is highlighted.

2) Character Limit: The COUNTIF function only works for 255 characters or fewer. For longer strings use the following formula:

• =COUNT(FIND($G5,$G$5:$G$11))>1

3) First Instance: If you only want to highlight the duplicate records, and not the first instance of a duplicated record, use the following conditional formatting formula:

• =COUNTIF($G$2:$G2,$G2)>1

3) Hide Duplicate Values

Use Excel conditional formatting to hide duplicate headings on a worksheet, to make a long list easier to read. This video shows you the steps, and the written instructions are below the video.

Hide Duplicate Values

In an Excel table, each row should have all the data entered, so you can sort and filter the data correctly.

However, you can use Excel conditional formatting to hide duplicate values in the label columns, and make the list easier to read.

In the screen shot below, the table is sorted by Region.

I've formatted the Region names, so the second (and subsequent) occurrences of each region name will have white font colour. That makes it "hidden", when you're looking at the list.

• Tip: Select a region name cell, and you can see the hidden text in the formula bar

How to Hide Duplicate Headings

Follow these steps to hide the duplicate labels in a column:

1. Select all the cells that you want to format - cells A2:A6 in this example
2. On the Ribbon's Home tab, click the Conditional Formatting button, then click New Rule
3. Click Use a Formula to Determine Which Cells to Format
4. For the formula, enter
   =A2=A1
5. Click the Format button.
6. Select a font colour to match the cell colour.
7. Click OK, click OK

Get the Excel Files ✅

• Highlight Duplicates: Download the sample Highlight Duplicate Values file, with examples from this page. The zipped Excel file is in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Conditional Formatting Intro

Conditional Formatting Examples

Color Scales | Data Bars | Icon Sets

Fix Conditional Formatting Extra Rules

Conditional Formatting Multiple Cells

Conditional Formatting - Currency

Conditional Formatting Documentation