How to Highlight the Fifth Occurrence of a Value in Excel with Conditional Formatting
Question details
The user needs a conditional formatting rule that highlights the fifth occurrence of each value in a column, and only the fifth occurrence when the value appears more than five times.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a column of repeated values and wants to visually mark the fifth instance of each distinct value for analysis or review.
- Observed behavior
- The goal state is a conditional formatting rule that identifies the fifth occurrence of a value, including values that occur exactly five times, without highlighting later occurrences beyond the fifth.
Make sure your data begins in a consistent column (for example, A2:A31) and that the first data row is selected before creating the rule. If your data uses headers, start the formula from the first value cell so the running count is correct.
Highlight the fifth occurrence with a COUNTIF-based conditional formatting formula
Use this method when you need Excel-compatible conditional formatting that counts each value as it appears down the column and applies a format only when the running count reaches five.
The formula combines a running count of each value from the top of the range through the current row with a total count of that value in the full range. The AND condition limits the highlight to the row where the running count equals five, so values that appear more than five times are not highlighted again later.
Select the range you want to format, for example A2:A31 in your WPS Office Spreadsheets worksheet.
On the Home tab, click Conditional Formatting, then choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, enter: =LET(counter,COUNTIF($A$2:$A2,$A2),total,COUNTIF($A$2:$A$31,$A2),AND(counter=5,counter<total+1)). Adjust the range A2:A31 to match your data.
Click Format, choose a fill color or font style, then click OK twice to save and apply the rule. The fifth occurrence of each value in the selected range is highlighted.
Highlight the fifth occurrence of a value in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same COUNTIF and conditional formatting rule logic as Excel, so you can apply the fifth-occurrence highlight directly in a familiar interface. Follow these steps to set up the rule and visually mark the target cell without changing your data.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains the column of values you want to format.
- 2. Select the data range: Select the range where the rule should apply, such as A2:A31. Keep the active cell at the first row of the selection for correct relative references.
- 3. Create a formula-based conditional formatting rule: Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =LET(counter,COUNTIF($A$2:$A2,$A2),total,COUNTIF($A$2:$A$31,$A2),AND(counter=5,counter<total+1)).
- 4. Choose a highlight format and apply: Click Format to set the fill color, font, or border for the fifth occurrence, then click OK to apply the rule. Check that only the fifth instance of each value is highlighted.
Frequently Asked Questions
Can I change the formula to highlight a different occurrence, such as the third or tenth?
Yes. Change the condition counter=5 to the occurrence you need, for example counter=3 or counter=10, and adjust the data range to match your column.
Why does the formula use both $A$2:$A2 and $A$2:$A$31?
$A$2:$A2 creates a running count that expands as the rule evaluates each row, while $A$2:$A$31 counts the value in the full data range so the rule can confirm whether the value occurs at least five times.
Does this rule work if my data starts in a different row or column?
Yes. Replace A2:A31 with your actual range and make sure the first cell in the running range is the first data cell, such as $B$4:$B4 and $B$4:$B$100.
What happens if a value appears more than five times?
Only the fifth occurrence is highlighted. The running count no longer equals five after that row, so later occurrences are not formatted.
Can I open this workbook in Microsoft Excel after applying the rule?
Yes. WPS Office Spreadsheets preserves conditional formatting rules in Excel-compatible .xlsx files, so the same rule works when the workbook is opened in Microsoft Excel.
