Get the Find Duplicates Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every method in this tutorial.
- Practice all 6 methods to find and highlight duplicates in Excel
- See how to flag duplicate rows and highlight full rows with formulas
- Includes examples for duplicate reports with FILTER, Power Query, and Copilot
Enter your email below for instant access.
How Do You Find Duplicates in Excel with Conditional Formatting?
First, let’s learn how to highlight duplicates in Excel with an example. Suppose you need to find duplicate product codes in two columns, labeled “Segment 1” and “Segment 2”.
- Step 1: Select the codes in “Segment 1”. To also select codes in “Segment 2” without losing your first selection, hold down the Ctrl key and click on the second column.
- Step 2: Click on the ‘Home’ tab at the top of Excel. Then, under the ‘Styles’ section, click ‘Conditional Formatting’. Choose ‘Highlight Cells Rules’, then ‘Duplicate Values’.
- Step 3: In the dialog box, you can choose in the drop-down:
- Duplicate: In Excel, duplicates are entries found more than once in a selected range. For example, the number “123” in both “Segment 1” and “Segment 2,” or multiple times in a column, is a duplicate.
- Unique: Unique values are those that appear only once within the entire selection. This means the value does not have any match or repeat anywhere else in the columns you’re comparing. For example, if “456” is in “Segment 1” but not in “Segment 2”, and it appears only once in “Segment 1”, then “456” is unique.
This is how to find and highlight duplicates in Excel.
💡 This method looks for any number that appears more than once in either column or across both columns. It will highlight these numbers to show they repeat. It does not check if a number from the first column is also in the second column only. For example, if a number shows up three times in the first column, it will be highlighted, even if it doesn’t appear in the second column at all.
❗ This technique does not work to find duplicates in a Pivot Table. If you need to find duplicates in a pivot table, you can try copying the data from the pivot table into a new worksheet. Then, use the same method described here to highlight duplicates in the new sheet.
How Do You Find Duplicate Rows in Excel?
Sometimes you need to check if entire rows in a table are duplicates of each other. Here’s an easy way to do that without checking each cell one by one:
Step 1: Create a Helper Column with CONCAT
- First, we’ll make a new column that combines all the information from each row into one cell. This makes it easier to compare whole rows.
- We will use the Excel CONCAT function to join the data from each row into one single cell. In the first cell of your new column, type:
=CONCAT(A4:C4)
- Drag this formula down to fill the column.
Step 2: Highlight the Duplicate Rows
- Select the column you just filled. Go to the ‘Home’ tab, click ‘Conditional Formatting’, then ‘Highlight Cells Rules’, and select ‘Duplicate Values’. This will color any cell that has data appearing more than once.
Step 3: Hide the Helper Column
- To keep your sheet tidy, you might want to hide the combined data in the helper column. Click on the column header to select it, press Ctrl + 1 to open the format cells window, and under ‘Custom’, type ;;; (three semicolons). This makes the text in the cells invisible.
- You can also make the column narrower so it takes less space.
Now, any duplicate rows will be highlighted, and your table will look cleaner without visible helper data.
How Do You Use COUNTIF to Find Duplicates in Excel?
Instead of just coloring cells with duplicates, you might want to display “True” if a value is duplicated, or “False” if it’s not, or even show how many times each entry appears.
How Do You Count How Many Times a Value Appears?
Let’s start with using a formula to find duplicates within a single column.
To determine the number of times an entry occurs in a list, use Excel’s COUNTIF Function.
Enter this formula in a new column next to your data:
= COUNTIF($B$4:$B$18, B4)Here, $B$4:$B$18 is the range where you’re looking for duplicates, and B4 is the cell you’re checking. Adjust the range according to your data.
How Do You Mark Duplicates as TRUE or FALSE?
To find out if a value is a duplicate, modify the formula to show “True” if a value appears more than once, and “False” otherwise.
=COUNTIF($B$4:$B$18, B4) > 1This will return “True” for duplicates and “False” for unique entries.
How Do You Use COUNTIF to Find Duplicate Rows?
To use a formula to locate duplicate rows of data in a table, combine the previous examples of using a “helper column”, the CONCAT Function, and the COUNTIF Functions.
Begin by creating a “helper column” that concatenates all values from a single row.
=CONCAT(B4:D4)
Next, use the COUNTIF Function to determine if each value exists more than one time in the list of helper results.
=COUNTIF($E$4:$E$18, E4) > 1
NOTE: These two steps could be combined into a single formula, but it’s often easier to work each step as separate formulas to keep track of the logic more easily.
How Do You Display Emoji Symbols Instead of TRUE/FALSE?
Let’s make this interesting by displaying emojis (i.e., symbols) in place of the “true” and “false” results.
Wrap the COUNTIF Function inside an IF Function like so…
=IF(COUNTIF($E$4:$E$18) > 1,Now, for the [True] argument, we’ll press Win+period (Win+.) to bring up the Windows Emoji Library and select a symbol we wish to reflect a “hit” when we have a duplicate entry.
We’ll use a symbol for “true” and display nothing for “false”. Make sure you place the Emoji in a set of quotation marks.
To put a bit of polish on this, we’ll hide the “helper column” (Column E) and color the symbols red using a red font color for the cells in Column F.
For a complete guide on COUNTIF with wildcards, dates, and logical operators, see our COUNTIF tutorial.
How Do You Highlight an Entire Row of Duplicates in Excel?
Suppose you wish to highlight the entire row of duplicate information based on the results of the previously created IF – COUNTIF formula.
Since we have already identified the duplicate rows using the formulas in Column F, we’ll check to see if a result in Column F is an Emoji symbol and if it is, color the entire row of the table in column B thru D.
- Select the table of original values (B4 thru D18).
- Select Home (tab) – Styles (group) – Conditional Formatting – New Rule.
- For the Rule Type, select “Use a formula to determine which cells to format”, and enter the following formula (making sure to lock the column reference but not the row reference):
- Set the formatting to apply a light red cell fill.
=$F4= "⛔"
The results are as follows:
Featured Course
Master Excel’s Essential Modern Functions
How Do You Generate a List of All Duplicates in Excel?
Instead of flagging or highlighting duplicates in a table, suppose you need to generate a separate list of values that are duplicates.
Using the “helper column” of concatenated data, we can use the FILTER Function to reduce the list of source data using the COUNTIF Function as the filter criteria, only keeping entries that occur more than 1 time.
=FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "")
Although this DID return a list of the duplicate entries, it would be nice to see them sorted in a way where each duplicate is next to its corresponding duplicate entry. This can be done by wrapping the entire formula inside a SORT Function.
=SORT(FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "") )
And, if you don’t need to see the duplicates represented for each instance, you can reduce the list by incorporating a UNIQUE Function.
=SORT(UNIQUE(FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "") ) )
For more on using FILTER with multiple criteria, see our FILTER function guide.
How Do You Find Duplicates with Copilot in Excel?
If you have a Microsoft 365 Copilot license, you can find and highlight duplicates using plain language instead of menus or formulas.
Open the Copilot pane, switch to Edit with Copilot, type what you need, and Copilot applies the conditional formatting for you.
Here is how to set it up and use it step by step.
Requirements Before You Start
Copilot needs three things to work in Excel:
- Your file must be .xlsx and saved to OneDrive or SharePoint (not locally).
- AutoSave must be turned on.
- Your data should be formatted as an Excel Table (select your range and press Ctrl + T). Copilot works on ranges too, but tables give more reliable results.
Step 1: Open the Copilot Pane
Click the Copilot button on the Home tab. The Edit with Copilot pane opens on the right side of your screen.
Step 2: Type Your Prompt
In the chat box, type a prompt like:
Highlight every value that appears in both Segment 1 and Segment 2
Highlight any item that exists in both Segment 1 and Segment 2
Copilot creates a conditional formatting rule and applies it to your table.
Duplicate values get highlighted based on the formatting it chooses (usually a yellow or red fill).
Step 3: Refine If Needed
If Copilot highlights duplicates across all columns and you only want one specific column, type a follow-up prompt like “Only highlight duplicates in the Segment 1 column.”
Edit in Copilot will undo the previous rule and apply a new one.
What Edit in Copilot Cannot Do (Yet)
There are a few limitations to keep in mind as of 2026:
- Copilot can only apply simple conditional formatting (one condition at a time). It cannot combine two conditions, like “highlight rows that have a duplicate product code AND a total over 500.”
- It works best with single-column duplicate checks. For multi-column duplicate rows, the CONCAT + COUNTIF method or Power Query (covered below) are more reliable.
- Results can vary depending on how your data is structured. Always review what Copilot applied before moving on.
💡 Tip: Copilot is ideal for a quick visual check on small to mid-size tables. For large datasets (10,000+ rows) or recurring reports, use Power Query instead.
How Do You Find Duplicates Using Power Query?
Power Query (called Get & Transform Data in the Data tab) lets you find, isolate, or remove duplicates without writing a single formula.
It is non-destructive, meaning your original data stays untouched. And once you set it up, you can refresh it with one click whenever your data changes.
This method is best for large datasets (10,000+ rows), recurring imports, or when you need to compare data across multiple sheets. Conditional Formatting and COUNTIF cannot natively compare across separate worksheets. Power Query can.
Here is how it works at a high level:
- Select your data and go to Data > From Table/Range to load it into the Power Query Editor.
- Click the column(s) you want to check, then go to Home > Keep Rows > Keep Duplicates to isolate only the repeated rows.
- Click Close & Load to send the results back to a new worksheet.
Your original data stays intact, and you can re-run the entire check anytime by right-clicking the output table and selecting Refresh.
💡 Important: Power Query is case-sensitive. “ABC-100” and “abc-100” are treated as different values. Add a Transform > Format > UPPERCASE step before checking duplicates if your data has inconsistent casing.
For the full step-by-step walkthrough with screenshots, check out our complete guide: How to Remove Duplicates in Excel.
How Do You Remove Duplicates in Excel?
To remove duplicates in Excel, there are many easy ways to do this. For step-by-step instructions on each method, check our complete guide here.
Before removing duplicates, you may want to combine columns first so you’re comparing full values rather than partial matches.
Frequently Asked Questions About Duplicates in Excel
Can You Find Duplicates Across Multiple Sheets in Excel?
The built-in Duplicate Values rule (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values) only works within a single selected range on one sheet. It cannot scan across worksheets.
However, you can create a formula-based conditional formatting rule that references another sheet. For example, apply a New Rule with this formula to highlight values in Sheet1 that also exist in Sheet2:
=COUNTIF(Sheet2!A:A,A1)>0This works, but you have to create a separate rule for each sheet you want to compare against. It does not scale well beyond two or three sheets.
For a more practical approach, use Power Query. Append the sheets into one table, then use Keep Duplicates to isolate matching rows in a single step. The result auto-refreshes whenever your data changes.
Does COUNTIF Find Case-Sensitive Duplicates?
No. COUNTIF is case-insensitive. It treats “ABC” and “abc” as the same value. If you need case-sensitive duplicate detection (common with product codes, part numbers, or IDs), use this formula instead:
=IF(SUM(--(EXACT(A$2: A$100,A2)))>1,"Duplicate","")The EXACT function compares each cell character by character, including uppercase and lowercase. In Excel 365 and Excel 2024, this formula spills automatically. In older versions, press Ctrl + Shift + Enter to confirm it as an array formula.
An alternative approach is to use SUMPRODUCT, which does not require array entry:
=IF(SUMPRODUCT(--(EXACT(A$2:A$100,A2)))>1,"Duplicate","")Can You Find Duplicates in a Pivot Table?
Not with the Duplicate Values rule. Microsoft confirms that certain conditional formatting rules do not work in the Values area of a PivotTable, and the Duplicate Values rule is one of them.
You can still apply other types of conditional formatting to PivotTables (like color scales, top/bottom rules, or formula-based rules), but not the built-in duplicate check.
If you need to find duplicates in PivotTable data, you have two options. Copy the PivotTable output to a new range using Paste as Values, then apply Conditional Formatting or COUNTIF on the pasted data.
Or go back to the source data behind the pivot and check for duplicates there before building the PivotTable.
How Do You Find Duplicates Without Removing Them?
Use Conditional Formatting to highlight duplicates visually (Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values). This does not delete anything. It only changes cell colors.
You can also add a COUNTIF helper column that returns TRUE for duplicates and FALSE for unique values:
=COUNTIF(A$2: A$100,A2)>1Both methods flag duplicates while keeping all your data intact. If you want even more detail, change the formula to =COUNTIF(A$2: A$100,A2) without the >1 to see the exact count of how many times each value appears.
What Is the Fastest Way to Find Duplicates in a Large Dataset?
It depends on the size:
- Under 10,000 rows: Conditional Formatting is instant and visual. Select your range, apply the Duplicate Values rule, and you are done.
- 10,000 to 500,000 rows: Power Query handles large ranges without slowing down your workbook. Load the data, click Keep Duplicates, and load the results to a new sheet.
Quick visual check on any size: Copilot (Edit with Copilot) can highlight duplicates with a single prompt, but review the results carefully on very large tables.
For recurring data imports, Power Query is the best long-term option because you can refresh the duplicate check automatically whenever the source data changes.
Download the Workbook
Enhance your learning experience by downloading our practice workbook. Practice the techniques discussed in real-time and master how to find and highlight duplicates in Excel with hands-on examples.
Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Power Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
