Formatting Issues

Highlight Serial Number Rows by Test Count in Excel

Nimra MalikNimra Malik Nov 18, 2026 868 views

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.

Highlight Serial Number Rows by Test Count in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the data rows

Select the rows and columns you want to color, such as A2:C1000. Leave the header row out of the selection.

2
Create the Test1 rule

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.

3
Add rules for the other tests

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.

4
Set rule priority and range

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.

Use COUNTIFS conditional formatting for each test
When multiple tests qualify: A serial number can qualify for more than one color. Because one cell cannot display multiple fill colors at once, the higher-priority rule determines the visible fill.
WPS Spreadsheets

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. 1. Open the worksheet: Open your workbook in WPS Spreadsheets and identify the serial-number and test-name columns.
  2. 2. Select the rows: Select the data range to highlight, such as A2:C1000, excluding the headers.
  3. 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. 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.
Automatically color serial-number groups by test countOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and formatting controls
microsoft office alternative - wps office

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.