No. In Excel for Microsoft 365 on Windows, Data > Remove Duplicates treats text that differs only by capitalization as duplicate, so it will not keep both Maple and maple. To identify duplicates while respecting case, use a helper column with =SUMPRODUCT(--EXACT(A2,$A$2:A2)) when the values are in A2:A20, then keep rows whose result is 1.
What Excel considers a duplicate
The built-in Remove Duplicates command compares cell values without distinguishing uppercase from lowercase letters. For example, if a column contains North, north, and NORTH, Excel treats those entries as the same text for duplicate removal. It keeps one occurrence and removes the others.
That behavior applies whether you are removing duplicates from one column or comparing multiple selected columns as a row. If a record has a matching name and department but the name’s capitalization differs, the command does not use that difference to preserve both rows. For a multi-column selection, it considers the selected columns together: rows are duplicates when their values match across those columns, ignoring text case.
Case-insensitive matching can be useful for cleaning a contact list where capitalization is inconsistent. It can also cause trouble when capitalization carries meaning, such as product codes, usernames, or identifiers that treat AB12 and ab12 as different values.
How to use Remove Duplicates safely
In Excel for Microsoft 365 on Windows, select a cell in the data, then choose Data > Remove Duplicates. In the dialog, select the columns Excel should compare. If the first row contains labels, leave My data has headers selected. Click OK to remove repeated records; Excel reports how many duplicates it removed and how many unique values remain.
Choose columns deliberately. Selecting only the email column removes repeated email addresses even if other fields differ. Selecting every column removes a row only when the entire selected record matches another row, aside from letter case. If you select the wrong fields, you can discard information you meant to keep.
- Make a copy of the worksheet or workbook before removing rows, especially if the source data is valuable.
- Sort first if you want a particular version of a record to be kept. Excel retains one occurrence, and sorting can help put the preferred record first.
- Confirm the column checkboxes before clicking OK. The operation changes the selected data rather than merely highlighting duplicates.
On Excel for Mac, look for Data > Remove Duplicates; the exact dialog and wording can vary by release. The case-insensitive behavior is the same. Excel for the web also offers duplicate-removal tools in supported versions, though its ribbon layout can differ. If you cannot find the command, check the Data tab or open the workbook in the desktop app.
Case-sensitive duplicate detection with a helper column
The EXACT function compares text while distinguishing uppercase and lowercase. Combined with SUMPRODUCT, it can count how many times the exact spelling has appeared from the first data row through the current row. With values in A2:A20, enter this in B2 and fill it down alongside the list:
=SUMPRODUCT(--EXACT(A2,$A$2:A2))
For example, suppose A2:A6 contains Maple, maple, Maple, OAK, and oak. The helper results are 1, 1, 2, 1, and 1. The second Maple is an exact, case-sensitive repeat, while Maple and maple each count as their own first occurrence.
To remove only exact-case repeats, copy the helper results and use Home > Paste > Paste Values over the helper column. Then filter that column for values greater than 1 and delete those rows, or filter for 1 and copy the retained records to a new range. Converting the formulas to values first prevents the flags from changing as rows are deleted.
This method keeps the first occurrence of each case-sensitive value. If you would rather keep the last occurrence, sort the records into the desired order before generating the flags, or use a more tailored approach. The formula follows the order in the worksheet; it does not decide which record is most complete or most recent.
Handling duplicates across multiple columns
Sometimes a duplicate is defined by a combination of fields, such as product code and region. With product codes in A2:A20 and regions in B2:B20, enter this formula in C2 and fill down to count exact, case-sensitive matches of both fields in the rows seen so far:
=SUMPRODUCT(--EXACT(A2,$A$2:A2),--EXACT(B2,$B$2:B2))
The result is 1 for the first occurrence of an exact pair and 2 or higher for later occurrences. Thus AB12 paired with West is different from ab12 paired with West, and both are different from AB12 paired with East. Add another --EXACT comparison for each additional field that defines a duplicate.
As with the single-column formula, turn the results into values before deleting flagged rows. This helper approach is transparent: you can inspect which fields define a repeat and check the result before changing the data. It is safer than building one combined text key, because simple concatenation can create collisions—for example, two fields containing AB and C versus A and BC can produce the same joined text if no separator is used.
Important differences between common Excel tools
Several Excel features can identify or remove duplicates, but their matching rules and effects differ. The key distinction is that the built-in Remove Duplicates command is convenient and destructive, while a formula can explicitly apply a case-sensitive comparison and let you review the results first.
- Remove Duplicates: Permanently removes repeated rows from the selected range and does not distinguish text by case.
- EXACT with a helper column: Distinguishes case and shows a count you can filter. It does not delete rows on its own.
- Conditional formatting: Useful for visually highlighting repeated values, but standard duplicate-highlighting rules do not provide the same case-sensitive test as
EXACT. - COUNTIF: Often used to count occurrences, but its text comparisons are not case-sensitive. It is not a substitute for
EXACTwhen capitalization matters.
There is an important distinction between spotting a duplicate and removing it. A formula or highlight can show which entries match under a chosen rule; removing rows is a separate action. Review records before deleting them, particularly when duplicates may have different dates, notes, or other useful details.
Common problems and data-cleaning details
Case-sensitive comparison does not automatically normalize other differences. Leading or trailing spaces can make two strings differ, and punctuation, spelling, and accented characters can also affect whether text matches. If Maple and Maple appear identical on screen, the trailing space may still cause an exact comparison to treat them as different. Clean or inspect the source values before deciding which differences should count.
Blank cells are another consideration. In the helper formula, repeated blanks can be counted as repeats, just like other repeated values. If blank rows should not be flagged, add a separate check or filter them out before applying the process. Also check that the formula is filled through every data row and that the starting row in its range matches the first row of your list.
For a multi-column comparison, make sure every formula range covers the same rows. If one field is omitted, Excel may flag records as duplicates even though the omitted field differs. Conversely, including a notes column that changes on every row may prevent otherwise repeated records from being identified.
When you need the most recent record rather than simply the first one, sort by date before flagging duplicates so the record you want to retain appears first. Check date values and sort order carefully; text that looks like a date may not sort chronologically. Save a copy before deleting rows so you can recover the original records if the chosen duplicate rule proves too broad.
Choosing the right method
Use the built-in command when capitalization is irrelevant and you want to remove duplicates quickly. It is a good fit for common cleanup tasks such as collapsing repeated entries in a list of categories, provided you select the correct columns and keep a backup.
Use the helper formula when uppercase and lowercase variants must remain separate, or when you want to inspect exactly which rows will be treated as repeats. For a one-column list, the formula is short and easy to audit. For a key made from several columns, compare each field with EXACT so that both the case rule and the definition of a matching record are explicit.
If your workbook is refreshed regularly, consider keeping the original data intact and placing your cleaned results on another sheet. That makes the process easier to review and repeat, and avoids accidentally losing information in the source table. Whatever method you choose, test it on a small copy of representative rows—including uppercase and lowercase variants, blanks, and records that differ in only one field—before applying it to a large dataset.
Frequently Asked Questions
Does Excel Remove Duplicates ignore capitalization?
Yes. The built-in command treats text such as Oak and oak as duplicates. Use an EXACT-based helper formula if capitalization should make them distinct.
How do I keep uppercase and lowercase entries separate?
With the values in A2:A20, put =SUMPRODUCT(--EXACT(A2,$A$2:A2)) in B2 and fill down. Keep rows with a result of 1; results above 1 mark later exact-case repeats.
Can Remove Duplicates compare more than one column?
Yes. Select all columns that define a duplicate in the Remove Duplicates dialog. Excel compares the selected fields together, but its text comparison still ignores case.
Does COUNTIF distinguish uppercase from lowercase?
No. COUNTIF is case-insensitive for text. For a case-sensitive count, use EXACT, often combined with SUMPRODUCT.
Will Excel keep the first or last duplicate?
Remove Duplicates retains one occurrence, generally the first in the current row order. If you need a particular record retained, sort the data into the desired order first and verify the result.
Related
- Excel Protect Sheet Greyed Out? Causes and Fixes
- Excel Protected Sheet: Can a Table Still Grow?
- Excel Protect Sheet Options: What Each Setting Does
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets European Date Format: Set It CorrectlyTo use a European-style date in Google Sheets, set the spreadsheet’s region under File > Settings > General…
- Google Sheets Data Validation: How to Set It Up EasilyYes. In Google Sheets in a web browser, select the cells, choose Data > Data validation, then click…
- Excel All Caps Shortcut: The Fastest Ways to Change CaseExcel has no built-in keyboard shortcut that changes selected text to all caps. To convert text reliably, enter…
- Present in PowerPoint With Notes on Screen: How ToYes. In Microsoft PowerPoint for Microsoft 365 on Windows, turn on Presenter View and use an extended display:…
- Subscript vs Superscript in Excel: What’s the Difference?In Excel, subscript places selected characters below the text baseline, while superscript places them above it. To apply…
- How to Double Space in Word for the Web, Step by StepTo double-space text in Word for the web, select the text, then choose Home > Line and Paragraph…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.