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.
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. Using Conditional Formatting to Highlight Duplicates in Multiple Columns in Excel
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.
- 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.
- You will see the duplicate values get our selected highlight color.
Thus, we can say that our process worked successfully.
Read More: Highlight Cells If There Are More Than 3 Duplicates in Excel
2. Inserting Excel 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.
- A dialog box entitled New Formatting Rule dialog box will appear.
- 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.
- You will see the duplicate values of columns C and D get our chosen highlight cell color.
In the end, we can say that our highlighting process and formula worked successfully.
Read More: How to Highlight Duplicates in Two Columns in Excel
3. Combining AND and COUNTIF Functions to Highlight Duplicates
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.
- A dialog box entitled New Formatting Rule dialog box will appear.
- Now, choose the Use a formula to determine which cells to format option.
- 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.
- You will see the cells contain duplicate values in columns C and D got our chosen cell format.
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.
Read More: How to Highlight Duplicates in Two Columns Using Excel Formula
4. Applying VBA Macro to Highlight Duplicates in Multiple Columns
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.
- A dialog box will appear.
- Now, in the Insert tab on that box, click Module.
- 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 Macro will appear. Select Highlight_Duplicate_in_Multiple_Column.
- Click on the Run button to run this code.
- At last, you will see that the cells contained the similar get the highlight color.
Finally, we can say that our visual code worked successfully and we are able to highlight duplicates in multiple columns in the Excel datasheet.
Read More: How to Highlight Duplicates but Keep One in Excel
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
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.
