How to Find Duplicate Rows Without Deleting Them in Excel
Question details
The user needs a method to identify duplicate records across multiple specific fields (such as ID, name, date, and code) without permanently removing the data.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reviewing data, auditing patient records, or cleaning up datasets where duplicate entries need to be visually flagged for verification rather than automatically deleted.
- Observed behavior
- Duplicate rows need to remain intact within the dataset but must be highlighted or filtered so they can be easily identified and reviewed.
Ensure your dataset has clear column headers and no merged cells, as these can interfere with formula-based conditional formatting rules.
Use Conditional Formatting with the COUNTIFS Function
Applying a custom COUNTIFS formula within Conditional Formatting is the most reliable way to highlight rows that have matching data across multiple columns without deleting them.
The standard 'Duplicate Values' feature in Excel only checks single cells. To find duplicate rows based on multiple columns (like Patient ID in Column A and Service Date in Column B), you must use a custom formula.
Highlight the entire dataset where you want to identify duplicates, starting from the first row of data (for example, select A2:F100). Do not include the header row.
Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter your COUNTIFS formula targeting your specific columns. For example: =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. Add more ranges and criteria pairs for each column you want to match.
Click the 'Format' button, go to the 'Fill' tab, and choose a distinct color (such as yellow or red) to highlight the duplicate rows. Click 'OK' to apply.
To view only the highlighted duplicates together, select your header row, go to the 'Data' tab, and click 'Filter'. Click the filter dropdown arrow on any column header and select 'Filter by Color' to display only the flagged rows.

Easily Highlight Duplicate Rows in WPS Spreadsheet
WPS Spreadsheet provides powerful advanced formula support and an intuitive Conditional Formatting interface, making it incredibly easy to identify complex duplicates without deleting your valuable data.
- 1. Select Data: Open your spreadsheet in WPS Office and highlight the data range you want to analyze.
- 2. Create Rule: Go to the 'Home' tab, click 'Conditional Formatting', and choose 'New Rule'.
- 3. Input Formula: Select 'Use a formula to format cells', enter your COUNTIFS formula to check across multiple columns, choose a highlight color, and click 'OK'.

Frequently Asked Questions
Can I find duplicates based on just a single column instead of multiple columns?
Yes. If you only need to evaluate one column (like just Patient IDs), select that specific column, go to Home > Conditional Formatting > Highlight Cells Rules, and select 'Duplicate Values'. This is much faster than writing a formula but will not check other columns.
Why is my COUNTIFS formula highlighting the wrong rows?
This typically happens due to incorrect cell references. Check that your lookup arrays are locked with dollar signs (e.g., $A$2:$A$500) so they don't shift, and ensure the criteria cells only have the column locked (e.g., $A2) so the rule accurately adapts row by row.
How can I extract these duplicate rows to a new sheet after highlighting them?
Once the duplicates are highlighted using conditional formatting, apply a filter to your data headers. Choose 'Filter by Color' and select your highlight color. You can then highlight the visible rows, press Ctrl+C to copy them, and paste them into a new worksheet.




