Have you ever found yourself paying the same invoice twice? Did you pay a huge commission to a sales rep because of duplicate sales entries in your worksheet? Or you might be reporting inflated customer acquisition reports to your supervisor just because you’ve counted duplicate profiles.

If you ever experienced any or all of the above scenarios, you wish that you knew how to highlight duplicates in Excel.

Read this effortless and quick Excel tutorial to learn all the clever ways to quickly spot all duplicate values in your Excel worksheets and workbooks for data cleansing purposes.

Excel Highlight Duplicates Using the Filter Tool

If you already know how to use the Filter tool of Excel, you can use that skill to spot duplicate values too.

Go to the target dataset and click on the column header. Then, press the Ctrl + Shift + L keys to activate the Filter tool.

Now, you must decide in which column the duplicate value could exist. For example, in the above dataset, it’s important to find the duplicates in the Product column.

So, I’d click the Filter drop-down arrow in the Product column to see a unique list of the items in the column.

Now, uncheck the Select All checkbox and checkmark the item you want to test if a duplicate exists against it. Click OK on the Sort & Filter menu to apply the filter you’ve just created.

If there’s a duplicate for the selected item, you shall see two rows for it. If there are more rows for the same product, that could be triplicate, quadruplicate, and so on.

Highlight Duplicates Using Conditional Formatting

There’s a preconfigured Duplicate Values rule. It allows you to highlight duplicate values in the chosen cell range.

On the target worksheet, select the input dataset and click on the Conditional Formatting drop-down button. There, hover the cursor over Highlight Cells Rules and click on the Duplicate Values rule.

Excel shall immediately highlight all the duplicate values in the range. You shall also see the Duplicate Values configuration dialog.

On the Duplicate Values dialog, click the values with drop down and choose the color formatting you want.

You can also choose the Custom Format option if you prefer to create your own formatting style to spot duplicate values effectively.

Conditional Formatting With Formula

The above rule highlights all the cells that contain similar values. As you can see the rule has spotted the number 13 in the Security Camera row as well as for the Smart Speaker row. However, the item Security Camera doesn’t have a duplicate value under the Product column.

If you wish to avoid such inconveniences, you can use Conditional Formula with an Excel function like COUNTIF.

Select your input dataset and click on the Conditional Formula button. In the context menu that opens, click on the New Rule option.

The New Formatting Rule dialog shall open. There, select the Use a formula to… option. In the Format values where… field enter the following formula:

=COUNTIF($A$2:$A$15, A2)+COUNTIF($B$2:$B$15, A2)+COUNTIF($C$2:$C$15, A2)>1

Click the Format button to open the Format Cells dialog. Here, you can create a custom formatting for the cells to be highlighted using various elements from the Number, Font, Border, and Fill tab. Click OK to apply formatting.

Click OK on the New Formatting Rule dialog to apply the rule to the selected dataset.

Excel shall accurately highlight the rows where the item names, their stock counts, and shelf locations contain duplicate values.

When you’re using this formula in your own Conditional Formatting rule, adjust the cell references according to your own dataset.

Excel Highlight Duplicates Using A Formula

Suppose, you’d like to match all the columns in a selected cell range for duplicates and then highlight the rows. You can achieve this by using a formula in Excel.

This method involves the use of a helper column. The formula will be placed in the helper column and it shall generate the text Duplicate if found matching data in all the cells of the rows.

Navigate to the worksheet that contains the input dataset. Create a new column named Helper on the right side of the dataset.

Select the first cell below the Helper column and enter the following formula into it:

=IF(AND(COUNTIF($A$2:$A$15, A2)>1, COUNTIF($B$2:$B$15, B2)>1, COUNTIF($C$2:$C$15, C2)>1), "Duplicate", "")

Hit Enter to calculate the cell.

Excel shall show the text Duplicate if the cell fulfills the logic of the function you used.

Now, use the fill handle to copy the same formula down the Helper column until the relevant data is available in the adjacent column of the input dataset.

The text Duplicate in the Helper column means that there are multiple entries for the same product, stock count, and location in the above dataset.

Don’t forget to modify the reference cell ranges according to your dataset in the above formula. If there are more than 3 columns, simply duplicate one COUNTIF instance, like COUNTIF($B$2:$B$15, B2)>1, and replace the cell range reference according to the additional column.

If you find this formula a bit complicated, you can use the following Excel formulas to highlight duplicates easily:

Excel Highlight Duplicates Using A PivotTable

If you know how to create a PivotTable to count the occurrences of certain values in a column, you can also use that to highlight duplicates.

Firstly, highlight the cell range where you suspect duplicate values exist for certain items, like the items below the Product column in the above dataset.

Click the Insert tab and click on the PivotTable button inside the Tables block.

You shall now see the PivotTable dialog. There, select the Existing Worksheet option and highlight a cell range on the worksheet where you want to set up the PivotTable. Click OK to start building the table.

Drag the column header to the Rows and Values fields in the PivotTable Fields console on the right side. You must choose the column header for which you want to locate duplicates. In the current tutorial, it’s the Product column.

You shall now see a PivotTable showing counts of the items inside the selected column header, like the Product column.

By so far, you must have got a basic idea that the items for which the Count of Product value is 2 in the PivotTable are duplicates in the Product column.

To highlight the duplicate cells in the Product column, you can use a Conditional Formatting rule using the Use a formula to… rule in the New Formatting Rule dialog box.

In the Format values where… field, enter this formula:

=VLOOKUP(A2,$E$2:$F$10,2,FALSE)>1

Apply the cell formatting of your choice.

The Conditional Formatting rule shall apply the selected formatting to duplicate cells instantly.

Here’s how you can customize the above formula:

• A2 should be the cell reference where you suspect a duplicate value exists
• $E$2:$F$10 represents the PivotTable range

Excel Highlight Duplicates in Power Query

If you’re working on a huge data column containing multiple duplicate values, export the dataset to Power Query. Then, you can easily organize the duplicates in pairs for easy identification.

Firstly, select the input dataset in your worksheet and click on the From Table/Range command inside the Get & Transform Data block of the Data tab.

Click OK on the Create Table dialog to export the dataset to Power Query.

In the Power Query Editor, select the target column where you suspect duplicate values exist. In this example, it’s the Product column.

Click the Reduce Rows drop-down and choose the Keep Rows menu. In the new context menu, select the Keep Duplicates option.

Power Query Editor shall remove all unique entries from the column and keep duplicates only.

Click the drop-down icon in the Product column and choose the Sort Ascending option in the context menu.

You shall now see paired duplicate values in the Power Query Editor.

If you wish to export the transformed dataset into your worksheet, click the File tab and choose the Close & Load To option.

You shall see the Import Data dialog. Click the Existing worksheet option and highlight a cell range on the worksheet.

Now you’ve got a new dataset of duplicate values highlighted with table formatting.

Excel Highlight Duplicates Using Excel VBA

If you wish to automate the duplicate highlighting task in Excel, you can use Excel VBA. The following script shall highlight duplicates in groups with the same color coding:

Sub HighlightDuplicates()
    Dim rng As Range
    Dim cell As Range
    Dim dict As Object
    Dim colorDict As Object
    Dim colorIndex As Integer
    Dim lightColors As Variant

    Set rng = ActiveSheet.UsedRange
    Set dict = CreateObject("Scripting.Dictionary")
    Set colorDict = CreateObject("Scripting.Dictionary")
    lightColors = Array(RGB(204, 255, 255), RGB(255, 204, 153), RGB(204, 204, 204), RGB(255, 204, 102), RGB(204, 255, 204), RGB(255, 204, 255), RGB(255, 102, 102), RGB(255, 102, 255))
    colorIndex = 0

    Application.ScreenUpdating = False

    ' First pass to populate the dictionary
    For Each cell In rng
        If cell.Value <> "" Then
            If Not dict.exists(cell.Value) Then
                dict.Add cell.Value, 1
                colorDict.Add cell.Value, lightColors(colorIndex Mod 8)
                colorIndex = colorIndex + 1
            Else
                dict(cell.Value) = dict(cell.Value) + 1
            End If
        End If
    Next cell

    ' Second pass to highlight duplicates
    For Each cell In rng
        If cell.Value <> "" And dict(cell.Value) > 1 Then
            cell.Interior.Color = colorDict(cell.Value)
        End If
    Next cell

    Application.ScreenUpdating = True
End Sub

If you wish to find out how to create a VBA macro using the above script, check out this easy Excel tutorial of Visual Basic for Applications:

📒 Read More: How To Use The VBA Code You Find Online

Find above an example dataset where I used the above VBA script to highlight duplicate values.

Here’s another Excel VBA script that allows you to enter a value from the worksheet or type anything and locate duplicates within the worksheet. The script also highlights the duplicate entries.

Sub FindDuplicates()
    Dim rng As Range
    Dim cell As Range
    Dim searchValue As Variant
    Dim duplicateCount As Integer

    ' Prompt the user to select a cell
    On Error Resume Next
    Set rng = Application.InputBox("Select a cell with the value to search for duplicates:", "Find Duplicates", Type:=8)
    On Error GoTo 0

    ' If the user clicked Cancel or didn't select a cell, exit the subroutine
    If rng Is Nothing Then Exit Sub

    ' Get the value from the selected cell
    searchValue = rng.Value

    ' Initialize the duplicate count
    duplicateCount = 0

    ' Loop through each cell in the active worksheet
    For Each cell In ActiveSheet.UsedRange
        ' If the cell's value matches the search value, increment the duplicate count and color the cell
        If cell.Value = searchValue Then
            duplicateCount = duplicateCount + 1
            cell.Interior.Color = RGB(255, 0, 0) ' Color the cell red
        End If
    Next cell

    ' Display a message box with the duplicate count
    MsgBox "Found " & duplicateCount & " duplicates of the value '" & searchValue & "'. The cells with duplicate values have been colored red.", vbInformation, "Find Duplicates"
End Sub

Find above a screenshot that shows the working of the script.

Excel Highlight Duplicates Using Office Scripts

If you wish to automatically find duplicates in Excel on the web, as well as Excel for Microsoft 365 desktop app, you can use the following Office Script:

function main(workbook: ExcelScript.Workbook) {
    let worksheet = workbook.getActiveWorksheet();
    let usedRange = worksheet.getUsedRange();
    let usedValues = usedRange.getValues();

    // Create a map to track duplicates
    let map: Map<string, number> = new Map();

    // First pass: Identify duplicates
    for (let i = 0; i < usedValues.length; i++) {
        for (let j = 0; j < usedValues[i].length; j++) {
            let cellValue = usedValues[i][j].toString();
            if (cellValue !== "") {
                if (map.has(cellValue)) {
                    map.set(cellValue, map.get(cellValue) + 1);
                } else {
                    map.set(cellValue, 1);
                }
            }
        }
    }

    // Second pass: Highlight duplicates
    for (let i = 0; i < usedValues.length; i++) {
        for (let j = 0; j < usedValues[i].length; j++) {
            let cellValue = usedValues[i][j].toString();
            if (cellValue !== "" && map.get(cellValue) > 1) {
                usedRange.getCell(i, j).getFormat().getFill().setColor("Orange");
            }
        }
    }
}

The script shall scan through the entire content of the active worksheet and highlight cells containing duplicate values in orange color.

To find out how to run the script in your Excel web or desktop app, check out the Using Office Scripts section in the following Excel tutorial. Just replace the Office Script with the one provided above.

📒 Read More: Create a Table of Contents in Microsoft Excel

Conclusions

These are all the tried and tested methods to highlight duplicates in Excel. You can easily grasp the technique if you try these once or twice. This skill is truly beneficial if you’re cleaning raw data for further data analytics purposes.

If you’ve found the methods mentioned above useful, share your feedback below. Also, if you know a better technique than the ones mentioned above, mention that in your comment.