Formatting Issues

How to Highlight Consecutive Duplicate SFN and Slot Rows in Excel

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 869 views

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.

How to Highlight Consecutive Duplicate SFN and Slot Rows in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the Data Range

Highlight the entire data range where you want the formatting applied, for example, A2:B22.

2
Open Conditional Formatting

Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

3
Enter the Logical Formula

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))

4
Apply Formatting

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 Logical AND/OR Formula for Conditional Formatting
Formula Reference Check: Ensure that the row references in the formula ($A2, $A1, $A3) match the exact starting row of your selected data range.
Efficient Spreadsheet Solution

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. 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the SFN and slot data.
  2. 2. Select the Target Range: Click and drag to highlight the specific data range you wish to format (e.g., A2:B22).
  3. 3. Access Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting, and choose New Rule.
  4. 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. 5. Format and Confirm: Click the Format button, choose your desired fill color, and click OK to instantly highlight the adjacent duplicates.
Fully compatible with Microsoft Excel file formats (.xlsx)Supports advanced logical and text conditional formatting formulasLightweight, fast, and completely free to useIntuitive ribbon interface identical to standard spreadsheet software
microsoft office alternative - wps office

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.