Summary

This article shows you how to find duplicate values in a spreadsheet and mark them so you can decide what to do next — which is a different job from deleting them. You will highlight repeats in one column or across several, count them, mark only the second and later copies, compare two sheets, filter down to the marked rows, and clear the formatting afterwards. If you already know the duplicates have to go, How to Remove Duplicate Rows in Excel covers the deletion side.

In this guide

• 1. Highlight Duplicate Values in a Single Column
• 2. Highlight Entire Rows That Repeat
• 3. Flag Only the Second and Later Copies
• 4. Count How Many Duplicates You Have
• 5. Find Values That Appear on Two Different Sheets
• 6. Filter to Show Only the Duplicates
• 7. Clear the Highlighting When You Are Done

Before You Start

• Microsoft Excel on Windows, Mac, or the web.
• A spreadsheet with a header row and at least one column you suspect contains repeated values.
• No admin rights required.

Instructions

1. Highlight Duplicate Values in a Single Column

This is the two-click answer, and it covers most situations — a list of email addresses, invoice numbers, or staff IDs where any repeat is a problem.

1. Select the cells you want to check. Click the column header to take the whole column, or drag over the range if the column has notes below the data.
2. On the Home tab, click Conditional Formatting in the Styles group.
3. Point at Highlight Cells Rules, then click Duplicate Values.
4. A small Duplicate Values dialog opens. The first dropdown reads Duplicate — leave it there. Switch it to Unique if you want the opposite: everything that appears exactly once.
5. Use the values with dropdown to pick a fill. Light Red Fill with Dark Red Text is the default and reads well against a white sheet.
6. Click OK.

Every repeated value in the selection now carries the fill you chose. The first occurrence is colored too — the rule marks all copies, including the original.

Note: The rule compares values cell by cell within the range you selected. Selecting two columns at once treats them as one long list, so a value in column A matching a value in column D counts as a duplicate. Select one column at a time unless that is what you want.

Verification: Type an existing value into the first empty cell below the data. It changes color as soon as you press Enter, and the value it matches changes with it. Press Ctrl + Z to undo the test entry.

2. Highlight Entire Rows That Repeat

A colored cell tells you which value repeats. A colored row tells you which record to look at. This needs a formula rule.

1. Select the entire data range, from the first cell of the header row's data down to the last column and last row — A2:F500, for example. Do not include the header row.
2. Click Home > Conditional Formatting > New Rule.
3. In the New Formatting Rule dialog, click Use a formula to determine which cells to format in the Select a Rule Type list at the top.
4. In the Format values where this formula is true box, enter the formula below, changing A to whichever column decides whether a row is a duplicate:

=COUNTIF($A:$A,$A2)>1

5. Click Format, open the Fill tab, choose a color, and click OK.
6. Click OK again to apply the rule.

Every row whose column A value appears more than once is now filled edge to edge.

The dollar signs are doing the work here. $A locks the column so every cell in the row looks at column A, while the row number 2 stays relative so it moves down the range. Change either one and the rule shades the wrong things — Understanding Absolute and Relative Cell References in Excel explains why.

To treat a row as a duplicate only when two or more columns match — first name and last name, or customer and order date — use COUNTIFS instead:

=COUNTIFS($A:$A,$A2,$B:$B,$B2)>1

Tip: Start the formula at the same row your selected range starts at. If you selected A2:F500, the formula refers to row 2. Selecting from row 5 and writing a formula that refers to row 2 shifts the whole rule three rows out of step.

3. Flag Only the Second and Later Copies

The built-in rule marks every copy, which is unhelpful when you are deciding what to delete — you want to see the extras, not the originals.

1. Select the data range, again starting at the first data row.
2. Click Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter this formula:

=COUNTIF($A$1:$A1,$A1)>1

4. Click Format, pick a fill, and click OK twice.

The first appearance of each value stays plain and every later copy is colored. The trick is the expanding range: $A$1:$A1 is anchored at the top but grows as the rule moves down the column, so each cell counts only the occurrences above it plus itself.

Note: Adjust the row numbers to match where your data starts. With a header row and data beginning in row 2, select from row 2 and use =COUNTIF($A$2:$A2,$A2)>1.

Tip: Sort the column before you apply this rule and the marked cells group together, which makes reviewing them far quicker. How to Sort and Filter Data in Excel covers sorting without scrambling the rest of the row.

4. Count How Many Duplicates You Have

Before you spend an afternoon on this, find out how big the problem actually is. Put these formulas in empty cells to the side of your data.

How many times each value appears — put this in a helper column beside the data and fill it down:

=COUNTIF($A:$A,$A2)

How many cells are part of a duplicate group:

=SUMPRODUCT(--(COUNTIF(A2:A500,A2:A500)>1))

How many rows you would lose if you removed the duplicates — the count of extra copies, in Microsoft 365 and Excel 2021:

=COUNTA(A2:A500)-COUNTA(UNIQUE(A2:A500))

On Excel 2019, which has no UNIQUE function, use this instead:

=COUNTA(A2:A500)-SUMPRODUCT(1/COUNTIF(A2:A500,A2:A500))

Note: The last formula returns #DIV/0! when the range contains blank cells. Shrink the range to the rows that actually hold data, or use the helper column approach and count the entries greater than 1.

COUNTIF is doing the heavy lifting in all of these — see How to Use SUMIF and COUNTIF in Excel for what else it handles.

Tip: Select the helper column and read the bottom-right status bar. Excel shows Count, Average, and Sum for the selection without you writing anything at all.

5. Find Values That Appear on Two Different Sheets

This is the "did we already invoice them last month" check. It highlights values on one sheet that also exist on another.

1. Open the sheet you want to mark — the current month's list, for example.
2. Select the column of values to check.
3. Click Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
4. Enter a formula pointing at the other sheet. Replace LastMonth with that sheet's tab name exactly as it appears at the bottom of the window:

=COUNTIF(LastMonth!$A:$A,$A2)>0

5. Click Format, choose a fill, and click OK twice.

Values that exist on both sheets are now colored. To see the opposite — entries that are new this month — change >0 to =0.

Note: A tab name containing a space needs single quotation marks around it, like ='Last Month'!$A:$A. Excel adds them for you if you click the other sheet's tab while building the formula, but typing the reference by hand does not.

Tip: In Excel 2007, cross-sheet references inside conditional formatting rules are blocked. Select the range on the other sheet, type a name such as LastMonthIDs into the Name Box to the left of the formula bar, press Enter, and use =COUNTIF(LastMonthIDs,$A2)>0 instead.

6. Filter to Show Only the Duplicates

Highlighting shows you the problem. Filtering gets everything else out of the way.

1. Click any cell inside your data.
2. On the Data tab, click Filter. Dropdown arrows appear on every header cell. The keyboard shortcut is Ctrl + Shift + L.
3. Click the arrow on the column you highlighted.
4. Point at Filter by Color. A small palette appears under Filter by Cell Color showing the fills in use.
5. Click the fill you applied. Every row that is not highlighted disappears from view.

If Filter by Color is greyed out, the highlighting came from a formula rule that Excel cannot resolve to a single fill. Use the helper column from Step 4 instead: click its filter arrow, choose Number Filters > Greater Than, enter 1, and click OK.

Verification: The row numbers down the left edge turn blue and skip — 4, 11, 12, 27. That is Excel telling you rows are hidden by a filter rather than deleted.

Note: Filtered rows are hidden, not removed. Click the filter arrow and choose Clear Filter From to bring everything back, or press Ctrl + Shift + L twice to turn filtering off and on again.

7. Clear the Highlighting When You Are Done

Conditional formatting rules travel with the file. Clear them before you send the workbook on, or the next person spends ten minutes wondering what the red means.

1. Select the cells you want to clean, or click any cell to work on the whole sheet.
2. Click Home > Conditional Formatting > Clear Rules.
3. Choose Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

To remove one rule and keep the others:

1. Click Home > Conditional Formatting > Manage Rules.
2. In the Show formatting rules for dropdown at the top, select This Worksheet so every rule on the sheet is listed.
3. Click the rule you want to remove, click Delete Rule, then click OK.

Note: Clear Rules removes conditional formatting only. Fills you applied by hand with the Fill Color button stay put — remove those with Home > Clear > Clear Formats, which also strips fonts and number formats from the selection.

Troubleshooting

TIP

Duplicate hunting goes faster when the data is clean first. Run the column through =TRIM(A2) to strip leading and trailing spaces, paste the result back as values, and re-run the rule. Stray spaces are the single most common reason two identical-looking entries fail to match.

Symptom / Error	Potential Cause	Solution
Identical-looking values are not highlighted	Trailing spaces or mixed data types	Clean the column with TRIM, and check whether some entries are numbers stored as text — see How to Fix Numbers Stored as Text in Excel.
ACME and acme are treated as the same value	The built-in rule ignores case	Use a formula rule with =SUMPRODUCT(--EXACT($A$2:$A$500,$A2))>1 for a case-sensitive comparison.
The formula rule colors the wrong rows	Range and formula start on different rows	The formula must refer to the first row of the range you selected. Reselect from the first data row and re-enter the rule.
Everything in the range turns one color	Row reference locked with a dollar sign	Check the formula reads $A2, not $A$2. The row number stays relative so the rule moves down the range.
Filter by Color is greyed out	Formula-based rule, or filtering is off	Turn filtering on with Ctrl + Shift + L, then filter the helper column from Step 4 for values greater than 1.
New Rule has no formula option	Older Excel for the web build	Click Editing > Open in Desktop App at the top right, apply the rule there, and save. The formatting is preserved when the file reopens in the browser.

Related Articles

• How to Remove Duplicate Rows in Excel
• How to Use Conditional Formatting in Excel
• How to Use SUMIF and COUNTIF in Excel
• How to Sort and Filter Data in Excel
• How to Fix Numbers Stored as Text in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?