How to Highlight Duplicates in Multiple Columns in Excel (4 Ways)

When we deal with a large Excel spreadsheet, duplicate values trapped in our dataset often. Or sometimes it becomes required for us to find them for any specific purpose. In this article, we will demonstrate to you 4 different approaches on how to highlight duplicates in multiple columns in Excel. If you are also interested to learn about this feature, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


4 Easy Methods to Highlight Duplicates in Multiple Columns in Excel

To demonstrate the following methods, we consider a dataset of 10 employees of a company. The point scale of this company is in column B. Their performance result for 2 months January and February is also shown in column C and column D. We will try to find out the employees’ names who are listed in both months with their excellent performance. Our dataset is in the range of cells B4:D14.


1. Applying Conditional Formatting to Highlight Duplicates

In this process, we are going to use the Excel built-in feature to find the duplicate data in multiple columns. Our dataset is in the range of cells B4:D14. The steps of this process are given as follows:

📌  Steps:

  • First, select the entire range of cells B4:D14.

  • Now, in the Home tab, select Conditional Formatting.
  • Then, select Highlight Cell Values > Duplicate values.

Applying Conditional Formatting to Highlight Duplicates

  • A dialog box entitled Duplicate Values will appear.
  • After that, keep the first small box in Duplicate and choose the highlighting pattern. In our case, we choose the default Light Red with Dark Red Text option.
  • Click the OK button.

Applying Conditional Formatting to Highlight Duplicates

  • You will see the duplicate values get our selected highlight color.

Thus, we can say that our process worked successfully.


2. Use of COUNTIF Function to Highlight Duplicates in Multiple columns

In this method, the COUNTIF function will help us to highlight duplicate values in multiple columns. We are using the same dataset to show the procedure to you. Our dataset is in the range of cells C5:D14. The method is explained below step by step:

📌  Steps:

  • First of all, select the entire range of cells C5:D14.
  • Now, in the Home tab, select Conditional Formatting > New Rules.

Use of COUNTIF Function to Highlight Duplicates in Multiple columns

  • A dialog box entitled New Formatting Rule dialog box will appear.

Highlight Duplicates in Multiple columns in Excel

  • Select the Use a formula to determine which cells to format option.
  • After that, write down the following formula into the empty box below Format values where this formula is true.

=COUNTIF($C$5:$D$14,C5)=2

  • Now, select the Format option.
  • Another dialog box called Format Cells will appear.
  • Choose your highlighting pattern. Here, we first go to the Font tab and choose the Bold option.

  • Then, in the Fill tab select the cell fill color. You will also see the cell color in an enlarged form in the Sample section.
  • Click OK to close the Format Cells dialog box.

  • Again click OK to close the New Formatting Rule box.

Use of COUNTIF Function to Highlight Duplicates in Multiple columns

 

  • You will see the duplicate values of columns C and D get our chosen highlight cell color.

Use of COUNTIF Function to Highlight Duplicates in Multiple columns

 

In the end, we can say that our highlighting process and formula worked successfully.


3. Using AND and COUNTIF Functions

In this following method, we are going to use the AND and COUNTIF functions to highlight duplicates in multiple columns in the Excel datasheet. Our dataset is in the range of cells C5:D14. The dataset contains the scale of Points in column B and the name of the employees of an institution for the month of January and February in columns C and D respectively. The procedure of this method is given below:

📌  Steps:

  • For beginning this process, select the entire range of cells C5:D14.
  • In the Home tab, select Conditional Formatting > New Rules.

Using AND and COUNTIF Functions to Highlight Duplicates in Multiple Columns

  • A dialog box entitled New Formatting Rule dialog box will appear.
  • Now, choose the Use a formula to determine which cells to format option.

Highlight Duplicates in Multiple columns in Excel

  • Write down the following formula into the empty box below Format values where this formula is true.

=AND(COUNTIF($C$5:$C$14,C5),COUNTIF($D$5:$D$14,C5))

  • After that, select the Format option.
  • Another dialog box called Format Cells will appear.
  • Choose your highlighting pattern. In our case, we first go to the Font tab and choose the Bold option.

  • Then, in the Fill tab select the cell fill color. You will also see the cell color in an enlarged form in the Sample section.
  • Click OK to close the Format Cells dialog box.

  • Again click OK to close the New Formatting Rule box.

Using AND and COUNTIF Functions to Highlight Duplicates in Multiple Columns

  • You will see the cells contain duplicate values in columns C and D got our chosen cell format.

Using AND and COUNTIF Functions to Highlight Duplicates in Multiple Columns

At last, we can say that the highlighting method and the formula worked perfectly.

🔍 Breakdown of the Formula

We are doing this breakdown for cells C5 and D6.

👉 COUNTIF($C$5:$C$14,C5): This function returns 1.

👉 COUNTIF($D$5:$D$14,C5): This function returns 1.

👉 AND(COUNTIF($C$5:$C$14,C5),COUNTIF($D$5:$D$14,C5)): This formula returns True. If both is 1, that means it has found a match.


4. Embedding VBA Code in Excel

Writing a VBA code can also help you to highlight duplicates in multiple columns. For doing this, we are using the same datasheet which we have already used. Our dataset is in the range of cells C5:D14. The steps of this process are given as follows:

📌 Steps:

  • To start the approach, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

Embedding VBA Code to Highlight Duplicates in Multiple Columns

  • A dialog box will appear.
  • Now, in the Insert tab on that box, click Module.

Embedding VBA Code to Highlight Duplicates in Multiple Columns

  • Then, write down the following visual code in that empty editor box.

Sub Highlight_Duplicate_in_Multiple_Column()
Dim rng_1 As Range, rng_2 As Range, cell_1 As Range, cell_2 As Range
Dim output As Range, output2 As Range
xTitleId = "Duplicate in Multiple Columns"

Set rng_1 = Application.Selection
Set rng_1 = Application.InputBox("Select Range1 :", xTitleId, rng_1.Address, Type:=8)
Set rng_2 = Application.InputBox("Select Range2:", xTitleId, Type:=8)

Application.ScreenUpdating = False

For Each cell_1 In rng_1
    Data = cell_1.Value
    For Each cell_2 In rng_2
        If Data = cell_2.Value Then
            cell_1.Interior.ColorIndex = 36
            cell_2.Interior.ColorIndex = 36
        End If
    Next
Next

Application.ScreenUpdating = True
End Sub
  • Close the Editor tab.
  • Now, from the View ribbon, click on Macros > View Macros.

  • A new dialog box called Marco will appear. Select Highlight_Duplicate_in_Multiple_Column.
  • Click on the Run button to run this code.

Embedding VBA Code to Highlight Duplicates in Multiple Columns

  • At last, you will see that the cells contained the similar get the highlight color.

Embedding VBA Code to Highlight Duplicates in Multiple Columns

Finally, we can say that our visual code worked successfully and we are able to highlight duplicates in multiple columns in the Excel datasheet.


Conclusion

That’s the end of this article. I hope that this will be helpful for you and you will be able to highlight duplicates in multiple columns in the Excel datasheet. If you have any further queries or recommendations, please share them with us in the comments section below.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo