Formatting Issues

Excel Conditional Formatting: Highlight Duplicate Shipment and SKU Combinations Across Sheets

Nimra MalikNimra Malik Nov 12, 2026 870 views

Question details

The user needs to highlight an entire row when the Shipment No. in column A and the SKU in column E appear together on another worksheet.

Excel Conditional Formatting: Highlight Duplicate Shipment and SKU Combinations Across Sheets
Product
Microsoft Excel
Device & OS
not provided
Scenario
A conditional-formatting rule is being used to compare shipment and SKU combinations between worksheets, with the goal of identifying matching records.
Observed behavior
A row is highlighted, but the user is unsure whether the result is expected and needs to verify the column locations, Excel version, and formula logic.
Before you start

Confirm that Shipment No. is in column A and SKU is in column E on both worksheets. Note the exact sheet names and confirm whether the workbook is opened in Excel 2016 or later, Excel for Microsoft 365, or another spreadsheet platform.

Solution 1Recommended

Use COUNTIFS to highlight matching shipment and SKU pairs

Use this method when both values must match the same record on another worksheet before the row is highlighted.

For example, to format rows on Sheet2 when the Shipment No. in column A and SKU in column E also occur together on Sheet1, apply a COUNTIFS rule to the full row range. Replace Sheet1 with the actual worksheet name and adjust the row limit as needed.

1
Select the rows to format

On Sheet2, select the range to which the formatting should apply, such as A2:E1000. Start the selection on the first data row, not the header row.

2
Open the conditional-formatting rule

Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.

3
Enter the matching formula

Enter =AND($A2<>"",$E2<>"",COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$E:$E,$E2)>0). If the sheet name contains spaces, use the format 'Shipment Data'!$A:$A and 'Shipment Data'!$E:$E.

4
Choose the highlight style

Click Format, select a fill color or font style, click OK, and then click OK again to save the rule.

5
Test the highlighted row

Compare the values in columns A and E of a highlighted row with Sheet1. The row should be highlighted only when both values occur together in the same matching record set.

Both columns are checked together: COUNTIFS prevents a false match where the Shipment No. exists on the other sheet but is paired with a different SKU. The row is formatted only when the two criteria are satisfied together.

Highlight Duplicate Shipment and SKU Pairs in WPS Spreadsheet

WPS Spreadsheet supports COUNTIFS formulas and conditional formatting, allowing you to compare Shipment No. and SKU values across worksheets and highlight matching rows with a familiar Excel-compatible workflow.

  1. 1. Select the target rows: Open the workbook in WPS Spreadsheet, select the rows on the destination sheet, and include the columns that should receive the highlight.
  2. 2. Create a formula rule: Choose Home > Conditional Formatting > New Rule, select the formula-based rule, and enter a COUNTIFS formula that checks columns A and E on the comparison sheet.
  3. 3. Set the formatting: Choose a fill color or other style, apply the rule, and verify that the Applies to range starts on the correct first data row.
  4. 4. Verify the pair match: Compare a highlighted row with the source worksheet to confirm that both the Shipment No. and SKU appear together.
Highlight rows only when the Shipment No. and SKU match as a pair across sheets.Use familiar Home > Conditional Formatting tools with COUNTIFS formulas.Open and edit Microsoft Excel XLSX files with strong format compatibility.Work in a lightweight spreadsheet interface without rebuilding the workbook.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the rule need COUNTIFS instead of two separate COUNTIF tests?

COUNTIFS checks the Shipment No. and SKU as a combined pair. Two separate COUNTIF tests could highlight a row when each value exists somewhere on the other sheet but the values are not associated with the same record.

How can I highlight the entire row instead of only columns A and E?

Select the full row range to format, such as A2:E1000, before creating the rule. Keep the formula references anchored by column, such as $A2 and $E2, so the result controls formatting across the selected row.

What should I change if the SKU is not in column E?

Replace every $E reference with the actual SKU column. For example, if SKU is in column F, use $F2 and the comparison range Sheet1!$F:$F in the COUNTIFS formula.

Why is a row highlighted when the values look identical but should not match?

Check for leading or trailing spaces, different capitalization, leading zeros, and numbers stored as text. Also verify that the formula points to the intended sheet and that the Applies to range begins on the same row used in the formula.

Can I compare a sheet against multiple worksheets?

Yes. Add one COUNTIFS test for each comparison sheet and combine them with plus signs, then test whether the total is greater than zero. For example, use (COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$E:$E,$E2)+COUNTIFS(Sheet3!$A:$A,$A2,Sheet3!$E:$E,$E2))>0.