Excel Conditional Formatting: Highlight Duplicate Shipment and SKU Combinations Across Sheets
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.

- 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.
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.
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.
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.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
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.
Click Format, select a fill color or font style, click OK, and then click OK again to save the rule.
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.
Troubleshoot unexpected highlights or formula errors
Use this check when the rule highlights the wrong rows, does not work, or is rejected by the spreadsheet application.
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. 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. 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. 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. Verify the pair match: Compare a highlighted row with the source worksheet to confirm that both the Shipment No. and SKU appear together.

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.




