WPS Spreadsheet: Flag Repeated Values in Column A Based on Column B
Question details
The user needs to flag or highlight values in column A when the corresponding value occurs more than once in column B.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains related values in columns A and B, and repeated entries in column B need to identify the corresponding rows in column A.
- Observed behavior
- The worksheet does not yet automatically identify rows whose corresponding column B value appears more than once.
Confirm which rows contain data and whether row 1 is a header. Select the exact range in column A that should be highlighted, such as A2:A100, before creating the conditional formatting rule.
Use conditional formatting with COUNTIF to highlight matching values in column A
Use this solution when you want WPS Spreadsheet to automatically highlight column A whenever its corresponding value appears more than once in column B.
The COUNTIF function counts how many times the value in each row of column B appears in the entire column. Applying the rule to column A makes the corresponding value in column A stand out without changing the worksheet data.
In WPS Spreadsheet, select the range in column A to format, such as A2:A100.
Go to the Home tab, select Conditional Formatting, and choose New Rule or a formula-based rule option.
Choose Use a formula to determine which cells to format, then enter =AND($B2<>"",COUNTIF($B:$B,$B2)>1). The blank check prevents empty cells from being highlighted.
Click Format, select a fill color or font style, and click OK to apply the rule.
Check the rows in column A whose corresponding values in column B occur more than once. Edit a value in column B to confirm that the highlight updates automatically.
Add a COUNTIF helper formula to label repeated values
Use a helper column when you need a visible text label such as Repeated instead of only a color-based highlight.
Identify repeated column values with WPS Spreadsheet
WPS Spreadsheet lets you combine COUNTIF formulas with conditional formatting to flag the corresponding values in another column. The result updates as your data changes and can be saved in common Excel-compatible formats.
- 1. Open the spreadsheet: Launch WPS Office, open your workbook in WPS Spreadsheet, and select the data range.
- 2. Create the rule: Use Home > Conditional Formatting > New Rule and enter =AND($B2<>"",COUNTIF($B:$B,$B2)>1).
- 3. Format the matches: Choose a fill or font style, then click OK to highlight the corresponding values in column A.
- 4. Save the workbook: Use File > Save As and select XLSX if you need to share the spreadsheet with Microsoft Excel users.
Frequently Asked Questions
What formula highlights column A when the corresponding value in column B is duplicated?
Select the target range in column A and use =AND($B2<>"",COUNTIF($B:$B,$B2)>1) in a conditional formatting rule. The $B2 reference checks the value in column B for each row.
Can I use COUNTIFS instead of COUNTIF in WPS Spreadsheet?
Yes. For example, =COUNTIFS($B:$B,$B2)>1 produces the same duplicate check and can be extended with additional criteria if needed.
How do I avoid highlighting blank cells?
Include the condition $B2<>"" in the rule: =AND($B2<>"",COUNTIF($B:$B,$B2)>1). This ensures that empty cells are ignored.
Can I flag duplicates with text instead of color?
Yes. In a helper column, enter =IF(AND(B2<>"",COUNTIF($B:$B,B2)>1),"Repeated","") and fill the formula down the rows.
Will the conditional formatting update when column B changes?
Yes. Because the rule uses a COUNTIF reference to column B, WPS Spreadsheet recalculates the count and updates the highlight when values are edited.
