How to Highlight Only the First Duplicate in WPS Spreadsheet Using Conditional Formatting
Question details
The user needs to highlight only the first occurrence of duplicate values in a selected WPS Spreadsheet range using a formula-based conditional formatting rule.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a range such as A2:A500 containing repeated values and wants to identify the first occurrence of each value without adding a helper column.
- Observed behavior
- The desired result is that only the first occurrence of each repeated value in the range is highlighted; later duplicate occurrences remain unhighlighted.
Select the exact range to format, for example A2:A500, and confirm which cell is active because the formula uses the active cell reference. Avoid merged cells inside the range so the rule is applied consistently.
Create a formula-based rule to highlight only the first duplicate
Use this solution when your values are in a fixed range such as A2:A500 and you want only the first duplicate occurrence to be highlighted.
The first COUNTIF checks whether a value appears more than once in the whole range. The second COUNTIF expands only down to the current row, so the rule triggers only for the first occurrence of each repeated value.
Select cells A2:A500. Ensure A2 is the active cell in the selection.
Go to Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
Enter =AND(COUNTIF(A$2:A$500,A2)>1,COUNTIF(A$2:A2,A2)=1) in the formula box.
Click Format, choose a fill or font style, then click OK to save the rule and return to the worksheet.
Manage the rule for a table-based changing data range
Use this approach if you want to work with an Excel table or expect your data range to grow and need a clearer maintenance path.
Highlight Only the First Duplicate in WPS Spreadsheet
WPS Spreadsheet supports formula-based conditional formatting, so you can create a COUNTIF rule that highlights only the first duplicate in a selected range without adding helper columns.
- 1. Select your data range: Open WPS Spreadsheet and select the range such as A2:A500, making A2 the active cell.
- 2. Create a new conditional formatting rule: Click Home > Conditional Formatting > New Rule.
- 3. Enter the first-duplicate formula: Choose Use a formula to determine which cells to format and enter =AND(COUNTIF(A$2:A$500,A2)>1,COUNTIF(A$2:A2,A2)=1).
- 4. Choose a highlight format: Click Format, pick a fill color or font style, and click OK.
- 5. Apply and review: Click OK again to apply the rule and confirm only the first duplicate in each group is highlighted.
Frequently Asked Questions
Why does the formula use A$2:A$500 and A$2:A2?
A$2:A$500 locks the entire search range and checks whether a value repeats anywhere in the range. A$2:A2 expands relative to the active row, so it only counts up to the current row and ensures the first occurrence is highlighted.
Can I use a structured table reference inside the conditional formatting formula?
No. Conditional formatting formulas do not support structured table-reference syntax in this context. Use standard references such as A2, and manually update the Applies to range after the table size changes.
How do I change the highlight color later?
Select the same range, go to Home > Conditional Formatting > Manage Rules, choose the rule, then click Edit Rule and Format to change the fill or font style.
What should I do if my data range grows after applying the rule?
Open Home > Conditional Formatting > Manage Rules and update the Applies to range to include the new rows, because conditional formatting ranges do not resize automatically.
