Formatting Issues

How to Color Excel Table Rows by Groups of Duplicate Values

Huma Ashraf Ch Oct 13, 2026 870 views

Question details

The user wants to format a table so that rows containing identical values in a specific column share the same fill color, with the color alternating whenever the value in that column changes.

How to Color Excel Table Rows by Groups of Duplicate Values

Product: Excel
Device & OS: not provided
Scenario: Applying dynamic shading to visual groups of identical data instead of standard alternating individual rows.
Observed behavior: The table currently uses standard alternating row colors, which ignores the logical groupings of duplicate data in column C.

Before you start

Ensure your data is sorted by the target column (e.g., Column C) so that identical values are grouped consecutively before applying the formatting.

Solution 1Recommended

Use Conditional Formatting with the UNIQUE Function

Apply a conditional formatting rule using the UNIQUE and ISODD functions to automatically switch colors when grouped values change.

This method relies on dynamic array functions to sequentially count unique values. Whenever a new grouped value appears in your sorted list, the total count switches between odd and even numbers, triggering the alternating row color.

Sort your dataset

Select your entire dataset, navigate to the Data tab, and sort by Column C to group all duplicate values together.

Select the target data range

Highlight the rows you want to format. Ensure you start selecting from row 2 (e.g., A2:F100) so that the active cell corresponds to the start of your data.

Create a new formatting rule

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

Enter the formula

Input the formula =ISODD(COUNTA(UNIQUE($C$2:$C2))). If your data starts in a different row or column, adjust the cell references accordingly.

Set the fill color

Click the 'Format' button, navigate to the 'Fill' tab, select your desired background color, and click OK to apply the conditional rule.

Use Conditional Formatting with the UNIQUE Function

Formula Compatibility: The UNIQUE function requires a modern version of Excel or WPS Office. If you are using an older version, you may need to use a helper column to count group changes manually.

WPS Spreadsheet Solutions

Group and Color Duplicate Rows Effortlessly in WPS Office

WPS Office Spreadsheet provides robust conditional formatting and data sorting capabilities, making it incredibly simple to highlight grouped data. It fully supports modern dynamic array functions and maintains perfect compatibility with your existing workbooks.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the table.
2. Sort by the target column: Select your data range, click the 'Data' tab, and select 'Sort' to sequentially group matching records.
3. Apply Conditional Formatting: Go to the 'Home' tab, click 'Conditional Formatting' > 'New Rule', and input your grouping formula.
4. Save in Excel Format: Choose a highlight color, click OK, and save your document seamlessly in the standard Excel format.

Fully compatible with Microsoft Excel (.xlsx) conditional formatting rulesSupports advanced dynamic functions like UNIQUE and COUNTAFree, lightweight, and fast alternative for complex data managementFamiliar ribbon UI ensures a seamless learning curve

Frequently Asked Questions

Can I color rows by duplicate groups without sorting first?

The formula relies on sequential changes in the specified column to trigger the color swap. If the data is not sorted, every new instance of a value will trigger a color change, resulting in inconsistent grouping. Always sort your target column first.

What if my software doesn't support the UNIQUE function?

You can use a helper column. Create a new column (e.g., Column D). Assuming headers are in row 1, enter '1' in D2. In D3, enter the formula =IF(C3=C2, D2, D2+1) and drag it down. Then, apply conditional formatting using the formula =ISODD($D2).

How do I apply this rule to the entire row instead of just one column?

When highlighting your data to apply the rule, ensure you select the entire table range (e.g., A2:F100) and that the column reference in your formula is absolute by using dollar signs (e.g., $C$2:$C2). This locks the condition to evaluate column C while applying the color across all selected columns in the row.

Why does the alternating color break when there are blank cells?

Blank cells are registered as unique values by the UNIQUE function, causing unexpected color shifts. To fix this, you can adjust the conditional formatting formula to include an IF statement that ignores blanks, or simply filter out and remove blank rows before applying the format.

Document & File Issues