How to Highlight Consecutive Duplicate SFN and Slot Rows in Excel
Question details
The user needs to highlight adjacent rows in a spreadsheet where the combination of SFN (System Frame Number) and slot values are identical.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing and visualizing data logs where SFN and slot combinations periodically repeat or reset, requiring clear identification of consecutive duplicates.
- Observed behavior
- Consecutive rows containing identical SFN and slot combinations need to be highlighted dynamically using a conditional formatting rule.
Ensure your dataset is sorted logically by time or sequence so that the duplicate SFN and slot combinations appear in adjacent rows before applying these formulas.
Use Logical AND/OR Formula for Conditional Formatting
This method compares the target row with the row immediately above and below it using logical AND/OR functions to detect adjacent duplicates.
By utilizing mixed references (locking the column but not the row), Excel will evaluate each row independently to see if it matches the previous or next row in both the SFN and slot columns.
Highlight the entire data range where you want the formatting applied, for example, A2:B22.
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
Choose 'Use a formula to determine which cells to format' and enter the formula: =OR(AND($A2=$A3,$B2=$B3),AND($A2=$A1,$B2=$B1))
Click the Format button, go to the Fill tab to select a highlight color, click OK, and then OK again to apply the rule.

Use a Concatenation Formula with a Separator
This method combines the SFN and slot values into a single text string for comparison, using a separator to prevent false matches when numbers have varying digit lengths.
Highlight Duplicate Rows Easily in WPS Office
WPS Spreadsheet fully supports advanced logical and text-based conditional formatting formulas, allowing you to highlight consecutive duplicates just as seamlessly as in Excel.
- 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the SFN and slot data.
- 2. Select the Target Range: Click and drag to highlight the specific data range you wish to format (e.g., A2:B22).
- 3. Access Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting, and choose New Rule.
- 4. Input the Custom Formula: Select 'Use a formula to determine which cells to format' and paste =OR(AND($A2=$A3,$B2=$B3),AND($A2=$A1,$B2=$B1)).
- 5. Format and Confirm: Click the Format button, choose your desired fill color, and click OK to instantly highlight the adjacent duplicates.

Frequently Asked Questions
Why does conditional formatting highlight incorrect rows when using the formula?
This usually happens if the absolute and relative references are incorrect. Ensure you lock only the column (e.g., $A2) and leave the row relative so the formula adapts as it moves down the dataset. Also, ensure the formula references match the exact first row of your selected range.
How do I highlight all duplicates across the whole sheet, not just consecutive ones?
If you want to find duplicates anywhere in the column regardless of order, use the built-in tool: go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For multi-column duplicates, use a COUNTIFS formula like =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2)>1.
Why is a separator needed in the concatenation formula?
A separator prevents false positive matches when combining numbers of varying lengths. For instance, concatenating A=12 and B=3 yields '123'. Concatenating A=1 and B=23 also yields '123'. Adding a period makes them '12.3' and '1.23', avoiding incorrect highlighting.
Can I apply this highlighting to the entire row instead of just the two columns?
Yes. Select the entire row range (e.g., A2:Z22) before creating the new rule. As long as your formula uses absolute column references (like $A2 and $B2), the formatting will extend across the entire highlighted row when the condition is met.




