The fastest way to check for duplicates in Excel is to select your range and use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, which colours every repeated value instantly. To count how often each value repeats, put =COUNTIF(range,cell) in a helper column; to delete duplicates, use Data → Remove Duplicates. Practice with the free Excel practice test on PracticeTestGeeks (16 questions with answer explanations).
You've got a customer list with 5,000 entries, and somewhere in that list the same customer appears two or three times under slightly different spellings. Or you've combined data from multiple sources and the merged file is clearly longer than it should be. Or your sum total is higher than expected and you suspect certain rows are counted twice.
These are the exact situations where knowing how to check for duplicates in Excel becomes a practical necessity rather than a nice-to-know. The good news is that Excel makes it straightforward — you can find duplicates in seconds using built-in tools, without any add-ins or advanced skills.
Duplicate data in a spreadsheet creates problems — inflated totals, misleading reports, duplicate mailings, double-counted inventory, and incorrect analysis results. Catching duplicates before they cause damage is one of the most common data quality tasks in Excel, and fortunately Excel provides several built-in tools for finding them. Whether you have 50 rows or 500,000, there's a method that works efficiently for your situation.
The quickest way to spot duplicates is conditional formatting — two clicks and every duplicate value in your selected range lights up in colour. For more control, COUNTIF formulas let you flag duplicates in a helper column, which is useful when you need to see exactly how many times each value appears.
Excel's built-in Remove Duplicates feature eliminates duplicates in one step when you don't need to review them first. And for more complex scenarios — checking duplicates across multiple columns, finding duplicates between two lists, or identifying near-duplicates — formulas like COUNTIFS, MATCH, and pivot tables give you the flexibility to handle virtually any duplicate detection task.
The approach you choose depends on what you're trying to accomplish. If you just want to see which values are duplicated, conditional formatting is fastest. If you want a permanent formula-based flag column, COUNTIF is better. If you want to delete duplicates outright, Remove Duplicates is the tool. If you need to compare two separate lists, MATCH or VLOOKUP is the right choice. This guide covers all of these methods with step-by-step instructions, so you can pick the one that fits your data and your goal.
One important clarification before diving in: 'duplicates' can mean different things depending on context. A duplicate value might mean an exact text match, a matching number, a matching combination across multiple columns (for example, same name AND same email address), or even a near-match where values are similar but not identical. The methods below handle exact matches; near-duplicate detection (fuzzy matching) requires additional tools or add-ins beyond standard Excel functionality.
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1–1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Interactive Excel resources on PracticeTestGeeks, free to start:
Each Excel duplicate-checking method differs in whether it changes your data, whether it counts occurrences, and which Excel versions support it. Use this table to pick one.
| Method | What it does | Changes your data? | Shows how many times a value repeats? | Availability |
|---|---|---|---|---|
| Conditional Formatting (Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values) | Highlights every repeated value in the selected range, both copies | No (formatting only) | No | All current desktop versions |
| COUNTIF / COUNTIFS helper column | Counts matches per row; >1 flags a duplicate; COUNTIFS checks several columns at once | No (adds a helper column) | Yes | All versions; COUNTIF is not case sensitive and mis-matches strings over 255 characters |
| Remove Duplicates (Data → Data Tools → Remove Duplicates) | Deletes repeated rows and keeps the first occurrence; compares displayed values | Yes, permanently once saved | No (reports how many were removed) | Excel 2016 through Microsoft 365 |
| Advanced Filter (Data → Sort & Filter → Advanced → Unique records only) | Hides duplicates in place or copies unique rows to another location | No when copying to a new location | No | Excel 2016 through Microsoft 365 |
| UNIQUE function (=UNIQUE(array,[by_col],[exactly_once])) | Returns the distinct values as a spilled list; exactly_once TRUE returns only values that occur once | No (formula output) | No | Excel for Microsoft 365, 2021, 2024, Excel for the web and mobile; not Excel 2019 or earlier |
| PivotTable (Insert → PivotTable) | Lists each value with a count of occurrences | No | Yes | All desktop versions |
Sources: Microsoft Support pages for Filter for unique values or remove duplicate values, the UNIQUE function, and the COUNTIF function.
Click and drag to select the column or range you want to check for duplicates. If your data is in column A from A2 to A500, select that range. Don't include the header row — you want to check data values, not the column title. If you need to check across multiple columns simultaneously, select all relevant columns together.
Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. A dialog box appears where you can choose to highlight either 'Duplicate' or 'Unique' values and select a formatting style (the default is light red fill with dark red text). Click OK. Every value that appears more than once in the selected range is immediately highlighted.
Scan the highlighted cells to identify which values are duplicated. The formatting shows you where duplicates exist but doesn't tell you how many times each value appears — for that, you'd need a COUNTIF formula. If you want to sort duplicates together for easier review, sort the column (Data → Sort A to Z) and all highlighted cells will group together.
Once you've identified duplicates, decide whether to delete them (use Remove Duplicates or manual deletion), merge them (combine data from duplicate rows into one row), or keep them (some duplicates are legitimate — the same customer making multiple purchases, for example). Remove the conditional formatting afterward if you no longer need it: Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.
The COUNTIF function counts how many times a specific value appears in a range. By placing a COUNTIF formula in a helper column next to your data, you can flag every duplicate with a clear indicator — and see exactly how many times each value appears, which conditional formatting alone can't do.
The basic formula is =COUNTIF($A$2:$A$500,A2), where $A$2:$A$500 is the range you're checking (with absolute references so it doesn't shift when you copy the formula down) and A2 is the cell being evaluated. This formula returns the count of how many times A2's value appears in the range. If the result is 1, the value is unique. If it's 2 or more, it's a duplicate.
To create a simple TRUE/FALSE duplicate flag, modify the formula to =COUNTIF($A$2:$A$500,A2)>1. This returns TRUE for any value that appears more than once and FALSE for unique values. You can then filter the helper column to show only TRUE values, giving you a clean list of all duplicated rows. Or use =IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","Unique") for a more descriptive label that's easier to read when scanning the sheet.
A useful variation marks only the second and subsequent occurrences as duplicates while leaving the first occurrence unmarked — useful when you want to keep one copy and identify the extras. The formula for this is =COUNTIF($A$2:A2,A2)>1, where the range starts fixed at $A$2 but the end of the range expands as you copy down.
This counts only the occurrences above the current row, so the first occurrence always returns FALSE (count of 1) while subsequent occurrences return TRUE. This 'first occurrence safe' approach is more useful than the standard COUNTIF when your goal is to identify rows to delete while keeping one copy of each value.
COUNTIF is more versatile than conditional formatting because it works with filters, can be used in further calculations (for example, =SUMPRODUCT((COUNTIF($A$2:$A$500,$A$2:$A$500)>1)*1) counts the total number of duplicate entries), and gives you a permanent, auditable record in the spreadsheet. It also handles partial matching with wildcards — =COUNTIF($A$2:$A$500,"*Smith*") counts how many values contain 'Smith' anywhere in the text, which is useful for detecting near-duplicates where names might appear in slightly different formats.
Excel's built-in Remove Duplicates tool deletes duplicate rows in one step. Select your data range, go to Data → Remove Duplicates, choose which columns to check (all columns or specific ones), and click OK. Excel keeps the first occurrence and deletes subsequent duplicates. Important: this permanently deletes data. Make a copy of your data before using this tool, or Undo (Ctrl+Z) immediately if the result isn't what you expected. Excel compares the values as displayed in the cells, not the stored values, so 3/8/2006 and Mar 8, 2006 count as different. A message tells you how many duplicates were removed and how many unique values remain.
A pivot table shows how many times each value appears without modifying your data. Select your data range, go to Insert → PivotTable, drag the column you're checking into the Rows area and the same column into the Values area (set to Count). The pivot table shows each unique value and its count. Values with a count greater than 1 are duplicates. This method is ideal for exploring large datasets where you want to understand the frequency distribution before deciding how to handle duplicates.
To check whether values in one list appear in another list — for example, checking which customers from list A are also in list B — use MATCH: =MATCH(A2,Sheet2!$A$2:$A$500,0). If the value is found, MATCH returns its position number. If not found, it returns #N/A. Wrap in ISNUMBER to get a TRUE/FALSE: =ISNUMBER(MATCH(A2,Sheet2!$A$2:$A$500,0)). VLOOKUP works similarly but retrieves a value from the matched row rather than just confirming a match exists.
Excel's Advanced Filter can extract unique values from a column to a separate location. Select your data range, go to Data → Advanced, choose 'Copy to another location,' set the copy-to range, and check 'Unique records only.' Excel copies only unique values, leaving duplicates behind. This is useful when you want a clean de-duplicated list without modifying the original data. The original column remains intact with all duplicates still present.
Sometimes a single column isn't enough to determine whether two rows are truly duplicates. Two customers might share the same last name but have different email addresses — that's not a duplicate. To check duplicates across multiple columns simultaneously:
To find duplicates between two different worksheets — for example, checking which items on Sheet1 also appear on Sheet2:
Finding duplicates is only half the job — deciding what to do with them requires understanding why they exist and whether they're legitimate. Not all duplicates are errors. A customer who makes three separate purchases should appear three times in a transaction log. A product that's sold in multiple stores might have the same SKU across multiple rows in an inventory report. Before deleting anything, understand the context of your data.
When duplicates genuinely need to be removed, you have several options. The Remove Duplicates tool (Data → Remove Duplicates) is the fastest for straightforward cases — it keeps the first occurrence and deletes subsequent ones. If you need to choose which specific row to keep (for example, keeping the most recent entry rather than the oldest), sort the data first so the row you want to keep appears first, then run Remove Duplicates.
For more careful deduplication, filter your COUNTIF helper column to show only duplicates, review them manually, and delete specific rows after confirming they're genuine duplicates. This takes longer but prevents accidental deletion of legitimate data. In sensitive datasets — customer records, financial data, medical records — manual review is almost always the safer approach.
Another consideration after removing duplicates: check whether any formulas, pivot tables, or charts referenced the deleted rows. Removing rows can shift cell references in ways that break dependent calculations if those calculations used specific row addresses rather than dynamic ranges or Table references. If your spreadsheet has downstream dependencies, verify that everything still calculates correctly after deduplication. Using Excel Tables (Ctrl+T) for your data largely eliminates this risk because Table references adjust automatically when rows are added or removed.
If duplicates are a recurring problem rather than a one-time cleanup issue, consider implementing data validation at the point of entry. Data validation rules in Excel can prevent duplicates from being entered in the first place — though they don't catch duplicates that already exist. For ongoing datasets, combining entry-time validation with periodic COUNTIF checks provides the most strong protection against duplicate data accumulation.
Exact-match duplicate detection catches identical values but misses near-duplicates — entries that are the same entity represented slightly differently. 'John Smith' and 'john smith' are the same person but different text strings. '123 Main Street' and '123 Main St.' are the same address. 'Microsoft Corp.' and 'Microsoft Corporation' are the same company. Standard COUNTIF and conditional formatting treat these as different values because they compare character by character.
COUNTIF already ignores upper and lower case, so "John Smith" and "JOHN SMITH" count as the same value; Microsoft notes its criteria are not case sensitive. Whitespace is different: COUNTIF will not match "John Smith" with "John Smith " (trailing space), and Microsoft recommends the TRIM or CLEAN function for stray spaces and nonprinting characters. COUNTIF cannot take TRIM() around a whole range, so clean the data first: add a helper column with =TRIM(A2), fill it down, and run COUNTIF on that column. For a case-sensitive check, use =SUMPRODUCT(--EXACT($A$2:$A$500,A2))>1.
For more complex near-duplicate scenarios — abbreviation differences, spelling variations, typos — Excel's standard functions aren't sufficient on their own. You'd need to combine techniques: use SUBSTITUTE() to standardise known abbreviations (replace 'St.' with 'Street'), use LEFT() or MID() to compare partial strings, or calculate similarity scores using a custom approach. Excel add-ins like Fuzzy Lookup (a Microsoft add-in for Excel) can automate fuzzy matching by calculating similarity scores between text strings and identifying probable matches above a confidence threshold.
In practice, the best approach to near-duplicates is prevention: standardise data at the point of entry using data validation drop-downs, consistent formatting rules, and clear data entry guidelines. When cleaning existing data, combining TRIM(), UPPER()/LOWER(), and targeted SUBSTITUTE() calls handles the majority of common variation patterns. Reserve true fuzzy matching tools for large datasets with significant data quality issues that simpler techniques can't resolve.
One practical tip for catching near-duplicates manually: sort your data alphabetically before scanning for issues. Sorting groups similar entries together, making it much easier to spot variations like 'Microsoft Corp' appearing right next to 'Microsoft Corporation' or 'Jon Smith' near 'John Smith.' This visual scan, while manual, often catches common data quality issues faster than building complex formulas — especially when your dataset has varied types of inconsistency that no single formula would catch.
When checking for duplicates in datasets with hundreds of thousands of rows, performance becomes a real consideration. COUNTIF formulas applied to every row create n-squared evaluation complexity — 500,000 COUNTIF formulas each scanning 500,000 cells means 250 billion comparisons on every recalculation. That's enough to make Excel noticeably slow or even unresponsive.
For large datasets, pivot tables are a much more efficient alternative for duplicate analysis. A pivot table with the target column as Rows and Count as Values summarises the whole column far faster than row-by-row formulas, and shows you every value along with its frequency. Filter the pivot table to show only counts greater than 1 to isolate duplicates. This approach is orders of magnitude faster than COUNTIF for large datasets and doesn't add formula overhead to the workbook.
Power Query (Get & Transform Data, available in Excel 2016 and later) is the most scalable option. Load your data into Power Query, use Group By to count occurrences, and filter for counts greater than 1. Power Query processes data outside the worksheet calculation engine, so large datasets do not slow down normal spreadsheet operations. It also handles cross-table duplicate detection through its Merge Queries feature, which functions like a database JOIN operation.
If you must use COUNTIF for large datasets, apply it to a subset rather than the entire column. Use a helper column with the formula only in rows where you suspect duplicates (based on sorting or filtering first), or run the formula once and then convert the results to values (Copy → Paste Special → Values) so the formulas don't recalculate on every edit. This eliminates the ongoing performance impact while preserving the duplicate flag information.
A hybrid approach that works well for recurring analyses: use Power Query to identify duplicates, export the results to a summary sheet, and keep the original data untouched. Power Query can be refreshed on demand when new data is added, so you get up-to-date duplicate detection without maintaining formula overhead in the working spreadsheet.
This is particularly practical for teams that receive new data files regularly and need to check each batch for duplicates before integrating it into the master dataset. Once the Power Query is set up, the entire process runs with a single 'Refresh' click — no formula editing, no re-selecting ranges, and absolutely no risk of accidentally modifying the underlying source data in the process.
If duplicates are a recurring problem in your spreadsheets — particularly in datasets that multiple people edit — preventing duplicates at the point of entry is more efficient than cleaning them up afterward. Excel's Data Validation feature can reject duplicate entries before they're saved to the cell.
To set up duplicate prevention: select the column where you want to prevent duplicates, go to Data → Data Validation, set 'Allow' to 'Custom,' and enter the formula =COUNTIF($A:$A,A1)=1. This formula checks whether the value being entered already exists in the column — if it does (count would be 2 or more), the validation rejects the entry and displays an error message. You can customise the error message in the Error Alert tab to explain why the entry was rejected.
Data validation has limitations. It only prevents new duplicates — it doesn't catch duplicates that already exist in the data before the validation rule was applied. It can be bypassed by pasting values (Paste Special → Values ignores validation rules). And it doesn't work retroactively — you need to run a COUNTIF check first to clean existing duplicates before relying on validation to prevent new ones. For critical datasets, combining data validation (prevention) with periodic COUNTIF audits (detection) provides the most complete protection against duplicate entries.
Another preventive measure is using Excel Tables (Ctrl+T) with structured data entry processes. Tables automatically expand as you add rows, and when combined with data validation rules, they provide a more controlled environment for data entry than an unstructured range. A calculated column in a Table automatically fills new rows with whatever formula you've set — so a COUNTIF duplicate flag column extends itself to check every new entry as soon as it's added, without you needing to manually copy the formula down.
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
In Excel, you can integrate the primary document with the data source. Word is commonly used in mail merging operations.
Answer: A. Client
You can merge the main document with the data source in Excel. In mail merge operations, Word is usually client.
What does the SUMPRODUCT function do in Excel?
Answer: A. Multiplies corresponding elements in arrays and returns the sum of those products
SUMPRODUCT multiplies corresponding elements in two or more arrays (ranges of the same size) and then sums those products, making it extremely versatile for conditional calculations.
A financial analyst needs to determine the optimal product mix to maximize profit, given constraints on production capacity, labor hours, and raw materials. Which Excel tool is most suitable for solving this type of optimization problem?
Answer: B. Solver
Solver is the appropriate tool for this scenario because it is designed to find an optimal value (maximum, minimum, or a specific value) for a formula in one cell, called the objective cell, subject to constraints on the values of other formula cells on a worksheet. Goal Seek, by contrast, only works with a single variable input and a single outcome.
Which tab in Excel's ribbon is used to insert a PivotTable?
Answer: D. Insert
PivotTables are inserted from the Insert tab by clicking the PivotTable button in the Tables group.