How to remove duplicates that are case sensitive in Excel?
Normally, the Remove Duplicates feature in Excel can help you remove the duplicate values quickly and easily, however, this feature is not case sensitive. Sometimes, you want to remove duplicates that are case sensitive to get the following result, how can you handle this task in Excel?
Remove duplicates but keep the first that are case sensitive with formula
Remove all duplicates that are case sensitive with helper formula
Remove duplicates that are case sensitive with Kutools for Excel
| Feature | Formula – Keep First | Helper Formula + Filter | Kutools for Excel |
|---|---|---|---|
| Best for | Removing later case-sensitive duplicates while keeping the first occurrence | Removing all case-sensitive duplicates and keeping only truly unique values | Quickly selecting case-sensitive duplicates with flexible options |
| Keeps first duplicate | Yes | No; removes all values that belong to a duplicate group | Optional; choose Duplicates (Except 1st one) |
| Helper column required | Yes; formula results are returned in another column | Yes; helper column marks duplicates as TRUE/FALSE | No |
| Main advantage | Formula-based and preserves the first case-sensitive occurrence | Can completely remove every value that has a case-sensitive duplicate | Supports both duplicate-removal scenarios without formulas or filters |
| Main limitation | Formula must be filled down and produces blank cells for later duplicates | Requires a helper formula, filtering, and row deletion | Requires Kutools for Excel |
| Recommended use | When you want a case-sensitive deduplicated list while keeping the first occurrence | When you want to keep only values that occur exactly once | Fast case-sensitive duplicate selection and deletion with more control |
Remove duplicates but keep the first that are case sensitive with formula
The following formula may help you to remove the case sensitive duplicate values and keep the first, please do as follows:
1. Enter this formula =IF(SUMPRODUCT(--EXACT(A2,$C$1:C1)),"",A2) into a blank cell, where you want to put the result, C2, for example, see screenshot:
Note: In the above formula, A2 is the first cell of the list excluding header that you want to use, and C1 is the cell above the cell that you put the formula.
2. Then drag the fill handle down to the cells that you want to apply this formula, and the duplicate values but the first that are case sensitive have been replaced with blank cells. See screenshot
Remove all duplicates that are case sensitive with helper formula
If you want to remove all duplicates that are case sensitive, you can apply a helper formula column, do as follows:
1. Enter the following formula into adjacent cell of your data,B2, for instance, =AND(A2<>"",SUMPRODUCT(--(EXACT(A2,$A$2:$A$15)))>1), see screenshot:
Note: In the above formula, A2 is the first cell of the data range excluding header, and A2:A15 is the used column range that you want to remove the duplicates case sensitive.
2. Then drag the fill handle down to the cells that you want to contain this formula, and all the duplicates are displayed as TRUE, and unique values are displayed as FALSE in the helper column, see screenshot:
3. And then you can filter all the duplicate values based on the helper column. Select the helper column, click Data > Filter to activate the filter, and then click the triangle button at the right corner of cell B1. Check only the TRUE option in the list box. See screenshot:
4. Then click OK, only the duplicate values that are case sensitive have been filtered out, select the filtered data, then click Home > Delete > Delete Sheet Rows, see screenshot:
5. And all the rows of duplicate values that are case sensitive have been deleted at once, then you should cancel the Filter function to show the unique values, see screenshot:
6. At last, you can delete the cell contents of column B as you need.
Tips: If you want to keep the original data, you can copy the filtered data and pasted them to another location instead of removing them.
Remove duplicates that are case sensitive with Kutools for Excel
If you have Kutools for Excel, with its Select Duplicate & Unique Cells utility, you can quickly select or highlight the duplicate values that are case sensitive and then remove them at once.
After installing Kutools for Excel, please do as follows:
1. Select the data range that you want to remove the case sensitive duplicates.
2. Then click Kutools > Select > Select Duplicate & Unique Cells, see screenshot:
3. In the Select Duplicate & Unique Cells dialog box, select Duplicates (Except 1st one) or All duplicates (Including 1st one) you need, and then check Case sensitive option, see screenshot:
4. Then click Ok button:
(1.) If you choose Duplicates (Except 1st one) option, the duplicate values excluding the first record will be selected. Then, press Delete key to remove them all at once, see screenshot:
(2.) If you choose All duplicates (Including 1st one) option, all duplicates that are case sensitive are selected. Then, press Delete key to remove them all at once, see screenshot:
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Conclusion
Case-sensitive duplicate removal is useful when values such as ABC and abc should be treated as different entries rather than the same text.
The Formula – Keep First method is suitable when you want to preserve the first occurrence and remove later case-sensitive duplicates. The Helper Formula + Filter method can remove every value belonging to a duplicate group and leave only values that occur once. For a simpler approach, Kutools for Excel supports both options without requiring helper columns, formulas, or filtering.
Choose a formula-based method when you prefer built-in Excel control, or use Kutools when you need a faster and more flexible way to identify and remove case-sensitive duplicates.
Demo: Remove duplicates that are case sensitive with Kutools for Excel
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