Formatting Issues

How to Highlight Duplicate Excel Rows by Patient and Service Date

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to identify and highlight duplicate records based on patient and service date groups in an Excel sheet, without deleting them, and leaving the entry with the most recent pay date unhighlighted.

Product: Excel
Device & OS: not provided
Scenario: Managing a database of patient records where multiple entries exist for the same patient and service date, and needing a visual way to spot older duplicate entries based on the pay date.
Observed behavior: The goal is to sort the data so the newest pay date is prioritized, then apply conditional formatting to automatically highlight any subsequent identical patient and service-date rows.

Before you start

Ensure your dataset is organized in a tabular format without any entirely blank rows or merged cells, as these can disrupt the sorting logic and formula references used in conditional formatting.

Solution 1Recommended

Sort Data and Apply Custom Conditional Formatting

Sort your dataset first to ensure the newest pay dates appear at the top of each group, then use a custom formula in conditional formatting to highlight the older duplicates below it.

To achieve this, the data must first be grouped properly using the Sort function. By sorting the Pay Date from newest to oldest as the final sorting level, the most recent record will always sit at the top of any duplicate group. A conditional formatting formula can then compare each row to the one above it to highlight duplicates.

Sort the Dataset

Highlight your entire data range. Navigate to the Data tab and click Sort. Add levels to sort by Patient ID, then Specialty, then Service Date, and finally Pay Date. Ensure the Pay Date is sorted from Newest to Oldest.

Select the Data Range for Formatting

Select the range where you want the highlighting to appear (for example, A2:F100). Do not include the header row in this selection.

Create a New Formatting Rule

Go to the Home tab, click Conditional Formatting, and select New Rule. Choose 'Use a formula to determine which cells to format'.

Enter the Formula

Assuming Patient ID is in column A, Service Date is in column C, and your selection starts at row 2, enter the formula: =AND($A2=$A1, $C2=$C1). This checks if the current row matches the row immediately above it.

Apply Highlight Formatting

Click the Format button, go to the Fill tab, and choose your desired highlight color. Click OK to apply the rule. Older duplicates will now be highlighted, leaving the first (newest) row untouched.

Absolute and Relative References: Pay close attention to the dollar signs ($) in the formula. They lock the column letters but leave the row numbers relative so the formula can correctly check each row sequentially down the sheet.

Easily Manage Duplicate Data

Use WPS Spreadsheet to Handle Duplicate Records Effortlessly

WPS Spreadsheet provides robust multi-level sorting and advanced conditional formatting features, making it incredibly easy to group complex patient records and highlight specific duplicates without risking data loss.

1. Open Your File: Launch WPS Spreadsheet and open your existing workbook containing the patient records.
2. Sort the Records: Select your data, go to the Data tab, and choose Sort to organize by Patient ID and Pay Date (Descending).
3. Apply Formatting: Navigate to Home > Conditional Formatting > New Rule, and input your duplicate-checking formula.
4. Save and Review: Apply the color fill and save your document with the new visual grouping applied.

Fully compatible with Microsoft Excel (.xlsx) file formats and formula syntax.Intuitive multi-level sorting interface to easily prioritize recent dates.Advanced conditional formatting tools to visually manage large datasets.Lightweight, fast, and free to use for daily data processing.

microsoft office alternative - wps office

Frequently Asked Questions

Can I delete the highlighted duplicate rows instead of just coloring them?

Yes. Once the duplicates are highlighted, you can turn on Data Filters, filter the dataset by cell color to show only the highlighted rows, and then delete them. Alternatively, you can use the 'Remove Duplicates' feature on the Data tab, ensuring your data is sorted with the newest pay date first beforehand.

Why is my conditional formatting highlighting the wrong rows?

This generally happens if your dataset was not sorted properly before applying the rule, or if your formula references don't match your selection. Ensure the row numbers in your formula (e.g., $A2=$A1) exactly correspond to the top-left cell of the range you selected.

How do I group by more than two columns?

You can add as many conditions as needed inside the AND formula. For example, to check Patient ID, Specialty, and Service Date, use a formula like =AND($A2=$A1, $B2=$B1, $C2=$C1).

What if I want to keep the oldest pay date instead of the newest?

Simply change your sorting order. When sorting the dataset, set the Pay Date column to sort from Oldest to Newest. The conditional formatting formula remains exactly the same, but it will now skip the first (oldest) entry and highlight the newer ones.

Document & File Issues