Highlight Serial Number Rows by Test Count in Excel
Question details
Highlight rows sharing a serial number and use different colors when a test value occurs at least three times for that serial number.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains repeated serial numbers and test names, and the user wants the row colors to update automatically as counts change.
- Observed behavior
- Rows for a qualifying serial number should be colored according to the test value that meets the count threshold.
Identify the serial-number and test-name columns and the full range of rows to format. The examples below assume serial numbers are in column A, test names are in column B, and data starts on row 2.
Use COUNTIFS conditional formatting for each test
Create one color rule per test to highlight every row belonging to a serial number that meets the threshold.
Each rule counts a specific test across all rows with the same serial number. These formulas do not require an auxiliary column.
Select the rows and columns you want to color, such as A2:C1000. Leave the header row out of the selection.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND($A2<>"",COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,"Test1")>=3), choose Format > Fill, select a color, and confirm.
Create two more formula rules using =AND($A2<>"",COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,"Test2")>=3) and =AND($A2<>"",COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,"Test3")>=3). Choose a different fill color for each rule.
Open Home > Conditional Formatting > Manage Rules. Check that each rule applies to the selected data range and arrange the rules in your preferred priority order. If your data extends beyond row 1000, expand both the Applies to range and the row limits in each formula.

Highlight serial-number groups in WPS Spreadsheets
Use COUNTIFS-based conditional formatting in WPS Spreadsheets to color all rows for serial numbers whose test counts reach three.
- 1. Open the worksheet: Open your workbook in WPS Spreadsheets and identify the serial-number and test-name columns.
- 2. Select the rows: Select the data range to highlight, such as A2:C1000, excluding the headers.
- 3. Create a formula rule: On the Home tab, open Conditional Formatting and create a new formula-based rule. For serial numbers in column A and tests in column B, enter =AND($A2<>"",COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,"Test1")>=3) and choose a fill color.
- 4. Add the remaining colors: Create separate rules for Test2 and Test3 by changing the test name in the formula and selecting a different fill for each. Use Conditional Formatting > Manage Rules to check the applied range and priority.

Frequently Asked Questions
Will every row for a qualifying serial number be highlighted?
Yes. The COUNTIFS rule checks the test count for the serial number in each row, so all selected rows with that serial number receive the qualifying color.
What happens if Test1 and Test2 both occur at least three times?
Both rules match, but a cell can display only one fill color. In Conditional Formatting > Manage Rules, place the color you want to show higher in the rule order.
How do I include more than 1,000 rows?
In Conditional Formatting > Manage Rules, expand each rule's Applies to range and change both $A$2:$A$1000 and $B$2:$B$1000 in the formulas to cover the additional rows.
Do I need a helper column for these rules?
No. The COUNTIFS formulas count matching serial numbers and test names directly. An auxiliary column is optional if you prefer to display intermediate counts.




