Formatting Issues

How to Format Entire Excel Rows for Duplicate Unique Identifiers

Nimra Malik Oct 10, 2026 868 views

Question details

The user wants to format or highlight entire rows in Excel based on duplicate unique identifiers, such as EEIDs, ensuring visual consistency across repeated data.

How to Format Entire Excel Rows for Duplicate Unique Identifiers

Product: Excel
Device & OS: not provided
Scenario: Highlighting adjacent duplicate records in a large dataset to easily identify grouped or repeated unique identifiers.
Observed behavior: Conditional formatting can detect duplicates and apply a consistent color, but it requires a specific formula to highlight the entire row rather than just a single cell.

Before you start

Ensure your data is sorted by the unique identifier column so that all duplicate records are adjacent to each other before applying the formatting rules.

Solution 1Recommended

Use Conditional Formatting with a Formula for Adjacent Duplicates

Apply a custom formula in the Conditional Formatting tool to highlight entire rows when adjacent unique identifiers match.

By default, Excel's built-in duplicate highlighting only formats the specific cells containing duplicates. To format the entire row, you must use a custom formula that locks the column reference while allowing the row reference to change dynamically.

Select the data range

Highlight the entire data range you want to format (e.g., A2:G100). Do not include the header row in your selection to prevent it from being accidentally formatted.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the custom formula

Enter a formula that checks for adjacent duplicates in your identifier column. For example, if your unique identifiers are in column B starting at row 2, type =$B2=$B1.

Apply formatting

Click the Format button, choose your desired fill color or font style for the duplicated rows, and click OK twice to apply the rule.

Use Conditional Formatting with a Formula for Adjacent Duplicates

Absolute and Relative References: Make sure to lock the column reference using a dollar sign (e.g., $B2) while leaving the row reference relative. This ensures the formatting applies across all columns of the current row.

Solution 2

Apply Exact Dynamic Formatting Using VBA

Use a VBA macro if you need to dynamically copy complex exact formatting from an existing row to its duplicates, which standard conditional formatting cannot do.

Effortless Data Formatting

Highlight Duplicate Rows Easily in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive Conditional Formatting tool, allowing you to highlight entire rows based on duplicate identifiers quickly and efficiently.

1. Select Data Range: Open your document in WPS Spreadsheet and select the target data range, excluding headers.
2. Access New Rule: Go to the Home tab, click on Conditional Formatting, and choose New Rule.
3. Input Custom Formula: Select 'Use a formula to determine which cells to format' and input your formula (e.g., =$B2=$B1).
4. Set Style: Click Format, set your desired highlight background color, and click OK to apply the formatting instantly.

Fully compatible with Microsoft Excel conditional formatting formulas and .xlsx files.Lightweight and fast, even when processing large datasets with complex rule sets.User-friendly interface with quick access to powerful data visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting only highlighting one cell instead of the entire row?

This usually happens if you haven't locked the column reference in your formula. Ensure you use a dollar sign before the column letter (e.g., =$B2=$B1) and that you have selected the entire row range (like A2:G100) before applying the rule.

Can I highlight duplicates across the entire dataset without sorting them first?

Yes. Instead of comparing adjacent rows, you can use the COUNTIF function in your conditional formatting formula. Select your data range and use a formula like =COUNTIF($B$2:$B$100, $B2)>1 to highlight any row where the identifier appears more than once.

Will the formatting update automatically if I add new duplicate identifiers?

Yes, as long as the newly added rows fall within the range you initially selected when creating the conditional formatting rule. To ensure rules expand automatically, format your original data range as an Excel Table (Ctrl + T) before applying the rules.

Document & File Issues