Function Problems

How to Find Duplicate Rows Without Deleting Them in Excel

Emma BrownEmma Brown Nov 2, 2026 870 views

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.

How to Find Duplicate Rows Without Deleting Them in Excel
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.
Before you start

Ensure your dataset has clear column headers and no merged cells, as these can interfere with formula-based conditional formatting rules.

Solution 1Recommended

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.

1
Select the target data range

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.

2
Open the Conditional Formatting menu

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.

3
Enter the custom COUNTIFS formula

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.

4
Set the highlight format

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.

5
Filter the highlighted duplicates

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.

Use Conditional Formatting with the COUNTIFS Function
Important Reference Locking: When typing your formula, ensure you lock the ranges with absolute references (like $A$2:$A$100) but leave the row numbers relative for the criteria (like $A2). This ensures the formatting rule correctly evaluates every row.
WPS Spreadsheet Solution

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. 1. Select Data: Open your spreadsheet in WPS Office and highlight the data range you want to analyze.
  2. 2. Create Rule: Go to the 'Home' tab, click 'Conditional Formatting', and choose 'New Rule'.
  3. 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'.
Fully compatible with Microsoft Excel formulas, including COUNTIFS, VLOOKUP, and IF statements.User-friendly Conditional Formatting tool to instantly visualize data trends and duplicates.Seamlessly open, edit, and save Excel workbooks in .xlsx format with zero compatibility issues.Free, lightweight, and fast performance even when handling large and complex datasets.
microsoft office alternative - wps office

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.