About This Article
This article is generated via an automated workflow leveraging generative AI. Referencing Microsoft Support documentation on "Find and remove duplicates" and "Filter for unique values or remove duplicate values," this guide clarifies operational differences and preliminary verification procedures.
Verification Status: 📘 Microsoft Official Specifications Confirmed / Excel Device Not VerifiedExcel's "Remove Duplicates" is convenient, but running it deletes duplicate rows within the target range. Relying on assumptions like "delete this because the same agent appears twice" is risky. Even if the agent is the same, different reception dates or case numbers mean they may be separate records.
- Supported Environments, Capabilities, and Limitations (Official Confirmation: October 15, 2026)
- Four-stage procedure to preserve the original
- Functional differences with Copilot
- Why judgment before deletion is required even for 100% matches
- 1. First, define the meaning of "duplicate"
- 2. Find by highlighting instead of deleting initially
- 3. Extracting a Unique List While Preserving Original Data
- 4. Pay Attention to Column Selection When Deleting
- 5. Pitfalls when formulas or display formats differ
- 6. Checklist to prevent failures
- 7. How to choose the right method
- Official information and primary sources
Supported Environments, Capabilities, and Limitations (Official Confirmation: October 15, 2026)
| Item | Usage Conditions and Constraints |
|---|---|
| Target Feature | Excel Remove Duplicates, Conditional Formatting, Unique Data ExtractionSeparate from Copilot file organization |
| Supported | Supported products such as Microsoft 365 Excel and Excel 2024/2021. UI differs between Windows and Web. |
| License | Eligible Excel license rights. Copilot paid add-on is not required. |
| Work Target | Cell ranges and tables in the open workbook. Does not automatically process other files on OneDrive. |
| Capabilities | Displaying duplicate candidates, extracting data according to conditions, and removing duplicates within the target range. |
| What cannot be done | Confirming whether identical appearances represent the same business case |
| Limits | Depends on maximum sheet rows and columns, used range, and file sharing status. Separate from Copilot limits |
| Security | Deletion is a destructive operation. Back up with a different name, list candidates, and verify counts beforehand |
Four-stage procedure to preserve the original
First, create a dummy reception form with four rows: 'Reception Number A001, Sato, PC Application', 'A002, Sato, Account Application', 'A003, Suzuki, PC Application', and 'A001, Sato, PC Application'. Even though the handler Sato appears twice, the first two rows are different applications. Using only the handler name as a duplicate key may result in deleting A002, which should be retained.
Phase 1Highlight duplicate candidates based on formatting.Phase 2Determine whether only the reception numbers match or all columns match.Phase 3Create a copy of the source data and execute [Remove Duplicates] only on the copy.Phase 4Compare the row counts and removed records before and after deletion. Knowing only that 'two rows were deleted' does not confirm whether the deletion was appropriate, so check the reception number and application details together.
Functional differences with Copilot
Attaching an Excel file to Copilot Chat and asking it to 'find duplicates' may generate candidates and explanations. However, this is not the same as opening the original Excel file and executing [Remove Duplicates]. When requesting table modifications using Copilot in Excel, verify the target workbook, edit permissions, and actual cell updates and save results.
If the goal is simply to 'create a unique list', you can also extract candidates using the UNIQUE function or filters in compatible Excel versions. Because the original data remains intact compared to deletion, creating a unique list first and interposing business judgment is the safer approach.
Why judgment before deletion is required even for 100% matches
Even for records with identical columns, having multiple rows for reception, retransmission, or approval histories may be correct from a business perspective. Whether data can be deleted depends not on Excel features, but on data retention policies and report usage. Particularly when operating on ledgers co-edited in OneDrive, consider checking version history and notifying other editors, as it affects sync destinations and other users.
Official:Extracting and deleting duplicate values in Excel. This article describes a testing procedure that has not been verified on actual devices.
Before rows disappear with Excel's remove duplicates feature: safely verifying via conditional formatting and extraction
uses a fictional reception ledger to introduce the sequence ofcoloring -> extracting a unique list -> determining deletion conditions -> deleting on a copy. This is a practical procedure for working while protecting the source data.
1. First, define the meaning of "duplicate"
Consider the following ledger.
| Reception Number | Person in Charge | Reception Date | Details |
|---|---|---|---|
| A001 | Sato | 10/1 | PC Request |
| A002 | Sato | 10/2 | Account Request |
| A003 | Suzuki | 10/2 | PC Request |
| A001 | Sato | 10/1 | PC Request |
If duplicates are checked solely by the assignee column, the first two rows are both "Sato" and will be considered duplicates. However, because their reception numbers differ and their details are different, both are necessary records.
On the other hand, the two rows for A001 have identical values across all items. In this example, they can be considered candidates for duplicate registration. However, if the source data registration history is unknown, do not delete them unconditionally just because they are an exact match.
The key for duplicate determinationdepends on the purpose: use the reception number to manage reception numbers uniquely, the assignee to create a list of the same person, or all columns to check for exact match rows.
2. Find by highlighting instead of deleting initially
Microsoft's officialFind and delete duplicatesguides you on how to highlight and review duplicates using conditional formatting.
Save the original file under a different name to prepare a working copy.
If checking only the assignee, select the data range of the assignee column, excluding the header row.
Go to Home -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.
Select a highlight color and apply it.
Verify the business logic to ensure that the highlighted values are indeed targeted for deletion.
This action onlydisplaysduplicates and does not delete rows. If you want to determine duplicates based on a combination of multiple columns, do not make a final decision based solely on single-column coloring.
3. Extracting a Unique List While Preserving Original Data
Official Microsoft documentation on extracting unique values and removing duplicatesdistinguishes between hiding duplicates and deleting data.
If you want to create a unique list of assignees elsewhere, use extraction first.
Select the target column or table.
Open Data -> Advanced.
Select "Copy to another location" and specify the destination range.
Check the box for "Unique records only".
Verify the resulting list.
Using the method of copying extraction results to another location allows you to create a unique list without deleting the original rows. Note that the definition of "unique" changes depending on the combination of selected columns.
4. Pay Attention to Column Selection When Deleting
If you determine that deletion is necessary, always perform it on a working copy.
Select the entire table targeted for deletion.
Open Data -> Remove Duplicates.
Check for the presence of headers.
Select only the columns used for comparison.
Before execution, re-verify what constitutes the same record.
After execution, cross-check the record count, remaining reception numbers, and necessary records.
For example, if you check only the "handler", different cases handled by the same person will also be targeted for deletion. Even when deciding based on the "reception number", if the design allows multiple identical reception numbers to exist as update histories or detail lines, simple deletion is inappropriate.
Microsoft's official documentation also recommends copying to another sheet first, as removing duplicates permanently deletes data.
5. Pitfalls when formulas or display formats differ
Duplicate detection does not necessarily compare the internal cell representations as-is. According to Microsoft'sfilter for unique values or remove duplicate valuesguide, the operation is based onthe values displayed in the cells.
For example, even if the formulas differ, if the displayed results are the same, they may be considered duplicates. Conversely, even with the same numerical value, differences in display formats may cause them to be treated as distinct values.
Therefore, when comparing reception numbers or product codes, check in advance for mixed strings and numbers, leading zeros, date display formats, and inputs containing spaces. Rather than concluding "it looks the same, so it's fine" after deletion, inspect whether the business records that should be retained are still present.
6. Checklist to prevent failures
Did you save the original workbook under a different name?
Did you determine the key for duplicate detection?
Did you check for cases where handlers are the same but the matters are different?
Did you inspect the contents using conditional formatting or unique value extraction before deletion?
Do the columns checked in "Remove Duplicates" match your objective?
Have you verified the record count before and after deletion against important reception numbers?
Have you backed up the original data so it can be restored if necessary?
7. How to choose the right method
| Objective | Recommended method | Impact on original data |
|---|---|---|
| Visually inspect potential duplicates | Conditional formatting | Do not delete rows |
| Create a unique list in another location | Extract using advanced settings | Original data is preserved |
| Actually remove unnecessary duplicate rows | Remove duplicates | Delete row data in the target range |
Finding a duplicate is not the same as confirming it is safe to delete. For reception logs, customer ledgers, asset registers, and similar records, verify the determination keys and business context before proceeding with deletion.
Official information and primary sources
Microsoft Support — Filter for unique values or remove duplicate values
Microsoft Support — Filter for unique values or remove duplicate values
License
Unless otherwise specified, the text and self-created charts and diagrams in this article owned by this site are available under CC BY 4.0. This includes content created and edited using generative AI. For code, if there is a separate license notice or a license in the linked GitHub repository, those terms take precedence. Quotations, third-party materials, images, trademarks, and the like are excluded from this license.
