How to color duplicate values or duplicate rows in Excel?
When working with large datasets in Excel, it is common to encounter repeated or duplicate entries—whether these are individual values scattered throughout a column, or entire rows bearing identical data across multiple fields. Manually identifying and coloring these duplicates, especially in complex spreadsheets, can be not only time-consuming but also prone to error. Efficiently distinguishing duplicate values or rows with color highlights can simplify data review, help with data validation, and aid in quick analysis or reporting. Below, you’ll find several practical solutions for coloring duplicate data in Excel, from built-in functionality to specialized tools and advanced automation.
Color duplicate values or rows with Conditional Formatting
Color duplicate values or rows with Kutools for Excel 
Color duplicate values or rows using VBA macro automation
Color duplicate values or rows with Conditional Formatting
Excel’s Conditional Formatting feature provides a straightforward method for visually highlighting duplicate values or rows. This is especially helpful for quickly reviewing and validating datasets where repeated information requires attention or where data integrity must be ensured. However, this method primarily handles visible data and is most suitable for one-off tasks rather than constant updates or very large dynamic ranges.
Color duplicate values
1. Select the data range containing the values you want to check for duplicates. Then navigate to the Home tab, click on Conditional Formatting, go to Highlight Cells Rules, and choose Duplicate Values. Refer to the screenshot below:
2. In the dialog box that appears, choose a highlight color from the drop-down list to apply to your duplicate values. This allows you to visually distinguish duplicated entries according to your preferences and color scheme. See screenshot:
3. Click OK to apply the formatting. All duplicate values—including the first occurrence—will now be highlighted using your selected color, making them easy to spot at a glance.
Practical tip: In cases where you want to highlight duplicates but exclude the first occurrence, Conditional Formatting does not support this directly. You may need to add a helper column with a formula or use a more advanced solution, as shown in later sections.
Color duplicate rows in the selected range
If your task requires you to color entire rows based on duplicates in a particular column, follow these steps:
1. Select the data range that contains the rows you want to evaluate. On the Home tab, select Conditional Formatting, then New Rule. See screenshot:
2. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. Enter this formula in the provided box (replace the range and the column reference as needed):
=COUNTIF($D$2:$D$10,$D2)>1
Tip: In this formula, D2:D10 represents the target column range for finding duplicates, and D2 is the starting cell of your selection. Adjust these references according to your actual data range. For example, if your dataset starts at row 5, change D2 to D5, and so on.
3. Click Format to open the Format Cells window. Under the Fill tab, choose a background color to mark duplicate rows. This color should contrast well with your original data for ease of visibility. See screenshot:
4. Click OK in the Format Cells dialog, then OK again in the New Formatting Rule dialog to finish. All rows where the specified column has duplicate values will now be highlighted, including the first instance of each duplicate.
This technique offers a visual summary without modifying your data, allowing for easy filtering, sorting, or further editing. Remember, you need to adapt the formula if your data range or columns change, and there’s a limit to how complex the duplicate logic can be using only Conditional Formatting.
However, since this method requires remembering and entering formulas, it may not be the most convenient for all users, especially for dynamic or frequently-updated tables. If you need to highlight duplicates (excluding or including the first instance) with greater flexibility, or if you wish to color duplicate rows rather than just cells, you may want to try one of the advanced solutions described below.
Color duplicate values or rows with Kutools for Excel
Kutools for Excel provides an efficient, user-friendly tool that can locate and color not only duplicate values, but entire rows, allowing custom color selection and detailed control over whether the first occurrence should be included.
After freely installing Kutools for Excel, please do as below:
1. Select your target list or data range, then click Kutools > Select > Select Duplicate & Unique Cells. This utility allows for flexible selections and customization to suit a variety of duplicate identification needs. See screenshot:
2. In the Select Duplicate & Unique Cells dialog, pick either the Duplicates (Except 1st one) or All duplicates (Including 1st one) option. You can then check Fill backcolor and select the desired highlight color from the dropdown menu below. This approach gives you granular control over which duplicates to color and allows direct preview of the results. See screenshot:
3. Click Ok. When prompted, a dialog will inform you of the number of selected cells. Simply click OK to proceed. See screenshot:
The duplicate values you specified are now selected and colored according to your settings. This helps you visually assess your data’s uniqueness and spot repeated entries.
Note:
To highlight duplicate rows, just select the entire range (all columns for the rows you want to examine), open Select Duplicate & Unique Cells, and make sure you check the Select entire rows option in addition to the appropriate duplicate criteria. This will ensure that all duplicate rows, not just individual duplicate cells, are colored. See the example below for guidance:
Click here to know more about Select Duplicate & Unique Cells.
Advantages of this method include not having to remember formulas, a visual interface for option selection, and support for processing large tables easily. However, this solution requires Kutools to be installed. If you often need to perform this kind of task, Kutools can greatly accelerate your workflow, and its features go beyond what Excel’s built-in tools offer.
Color duplicate values or rows using VBA macro automation
For those who require more advanced control—such as batch highlighting across multiple sheets, custom coloring criteria, or regular automated updates—a VBA macro can automate the process of identifying and coloring duplicates. This approach is especially suitable for users comfortable with programming or for scenarios where duplicate detection must be repeated frequently, or where Conditional Formatting cannot handle complex rules.
Caution: Always save your workbook before running macros, as VBA actions cannot be undone using the Undo function. If your data already has color formatting, the macro may overwrite it in the affected areas.
Automate coloring duplicate values in a selected column
1. Click Developer Tools > Visual Basic. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, then copy and paste the code below into the module window:
Sub HighlightDuplicateValues()
Dim Rng As Range
Dim Cell As Range
Dim dict As Object
Dim FirstColor As Long
Dim DupColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set dict = CreateObject("Scripting.Dictionary")
Set Rng = Application.InputBox("Select column to check for duplicates", xTitleId, Type:=8)
If Rng Is Nothing Then Exit Sub
FirstColor = vbYellow ' Color for first occurrence, change as needed
DupColor = vbRed ' Color for duplicates, change as needed
' Clear previous coloring in the range
Rng.Interior.ColorIndex = xlNone
For Each Cell In Rng
If Not IsEmpty(Cell.Value) Then
If dict.exists(Cell.Value) Then
Cell.Interior.Color = DupColor
Else
dict.Add Cell.Value, 1
Cell.Interior.Color = FirstColor
End If
End If
Next
End Sub2. Press F5 or click the 
button to execute the code. When prompted, select the cell range (the column you want to check for duplicates) and click OK. The macro will then scan the column, coloring the first occurrence yellow (editable in the code) and any duplicates red (also editable).
Automate coloring of duplicate rows based on an entire row’s content
This macro highlights entire rows that are duplicated across all the columns in your selection.
1. Open the VBA editor as described above (Developer Tools > Visual Basic > Insert > Module), then paste in the following code:
Sub HighlightDuplicateRows()
Dim Rng As Range
Dim RowArea As Range
Dim dict As Object
Dim i As Long
Dim rowString As String
Dim FirstColor As Long
Dim DupColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set dict = CreateObject("Scripting.Dictionary")
Set Rng = Application.InputBox("Select the range including all columns to evaluate for duplicate rows", xTitleId, Type:=8)
If Rng Is Nothing Then Exit Sub
FirstColor = vbYellow ' Change color as needed
DupColor = vbRed ' Change color as needed
' Clear previous coloring
Rng.EntireRow.Interior.ColorIndex = xlNone
For i = 1 To Rng.Rows.Count
rowString = ""
For Each RowArea In Rng.Rows(i).Cells
rowString = rowString & "|" & CStr(RowArea.Value)
Next
If dict.exists(rowString) Then
Rng.Rows(i).Interior.Color = DupColor
Else
dict.Add rowString, 1
Rng.Rows(i).Interior.Color = FirstColor
End If
Next
End Sub2. Click Run or press F5, then select the appropriate range covering all columns you wish to compare. The macro will color the first appearance of each unique row in yellow (customizable) and mark all subsequent identical rows in red.
Parameter explanations and practical notes:
- The variable
FirstColorsets the fill color for first occurrences;DupColorsets the color for duplicate values/rows. You may changevbYelloworvbRedto other color constants or RGB values as needed. - The code treats blank cells as unique and does not color them. If your data includes empty cells that should be handled differently, the code may be modified accordingly.
- For very large datasets, processing time may increase; be patient if running on thousands of rows.
- If you wish to clear previous formatting before rerunning the macro, keep the color reset line (
ColorIndex = xlNone) enabled.
This VBA method is highly versatile, supporting complex customization, batch processing, and automation scenarios not possible with standard Excel tools. It does, however, require enabling macros and some basic VBA familiarity.
Troubleshooting and summary suggestions: If you encounter issues with coloring, make sure that the selected ranges are correct, that macros are enabled, and that no conflicting Conditional Formatting is active. Always back up your data before running VBA for the first time. For regular and complex duplicate marking, consider saving your macro-enabled workbook as a template.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in