How to Group Duplicate Values with Alternating Conditional Formatting in Excel
Question details
The user wants to visually separate groups of identical numeric values in a sorted dataset by applying alternating background colors to each group of duplicates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and visually differentiating clustered data in a large, sorted Excel spreadsheet.
- Observed behavior
- To make the dataset easier to read, each distinct group of duplicate values needs to alternate in color automatically as the value changes.
Ensure your dataset is sorted by the column containing the duplicate values before applying the formula and conditional formatting rules.
Use a Helper Column and Formula-Based Conditional Formatting
Create a helper column with an ISODD and UNIQUE formula to identify alternating groups, then apply conditional formatting based on that helper column.
This method uses an array formula to count the occurrence of unique values and determines if that count is an odd or even number. Based on the result, a conditional formatting rule applies alternating background colors.
Select an empty column next to your data to act as a helper column (for example, column BK).
In the second row of your helper column (e.g., cell BK2), enter the formula =ISODD(COUNTA(UNIQUE($BD$2:$BD2))). Replace 'BD' with the column letter that contains your duplicate values.
Select the cell containing the formula and double-click or drag the fill handle at the bottom-right corner to copy the formula down to the end of your dataset.
Highlight your entire data range starting from row 2. Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. Enter =$BK2 in the formula box, click the 'Format' button to select your desired fill color, and click 'OK' to apply.
Easily Format and Manage Data with WPS Spreadsheet
You can seamlessly apply advanced conditional formatting and modern array formulas like UNIQUE to group data efficiently in WPS Spreadsheet, a powerful and highly compatible alternative to Excel.
- 1. Open Data: Launch WPS Spreadsheet and open your existing dataset.
- 2. Create Helper Column: Add a helper column using the =ISODD(COUNTA(UNIQUE($BD$2:$BD2))) formula and drag the fill handle to apply it down the column.
- 3. Select Range: Highlight your data range starting from the second row.
- 4. Open Conditional Formatting: Navigate to the Home tab on the top menu, click 'Conditional Formatting', and choose 'New Rule'.
- 5. Apply Color: Select formula-based formatting, enter =$BK2 as the formula, choose your preferred highlighting color, and apply.
Frequently Asked Questions
Why is the UNIQUE function returning a #NAME? error?
The UNIQUE function is available in newer versions of Excel (Microsoft 365, Excel 2021) and the latest versions of WPS Office. If you are using an older version, the software won't recognize the function and will return a #NAME? error. In older versions, you can use a combination of IF and COUNTIF logic to create alternating group flags.
Do I absolutely need to sort my data for this formula to work?
Yes, it is highly recommended. The formatting rule relies on counting the distinct groups sequentially. If your duplicate values are scattered randomly throughout the column instead of being grouped together, the formula will treat each reappearance as a new unique value, breaking the intended alternating visual pattern.
Can I format the entire row instead of just specific columns?
Yes. When you are at the step of highlighting your data range before applying the conditional formatting rule, simply select the entire rows (e.g., click and drag across the row numbers on the left) instead of just the data cells. As long as you lock the helper column reference with a dollar sign (e.g., =$BK2), the entire row will be highlighted.
