How to Highlight Rows Based on Two Criteria in Excel
Question details
The user wants to highlight an entire row in a spreadsheet when two specific conditions in different columns are met simultaneously.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing data where specific rows need to stand out visually based on the combined values of two different columns (e.g., Column C is "Vendors" and Column D is "Non-Compliant").
- Observed behavior
- Users need a way to apply a format across an entire row, rather than just single cells, when multiple text criteria are satisfied in that row.
Ensure your data is organized in clear columns without merged cells, and identify the exact columns that contain your two criteria.
Use the AND Function in a Conditional Formatting Rule
By using a formula-based conditional formatting rule with the AND function, you can check multiple columns simultaneously and apply the formatting to the entire row.
Using the AND function within a Conditional Formatting rule allows you to specify multiple conditions that must all be true. By applying absolute column references (using the $ symbol), you lock the evaluation to specific columns while allowing the formatting to stretch across the entire row.
Highlight the entire dataset you want to format, such as A2:D100. Ensure the active cell in your selection is in the top row (e.g., row 2).
Navigate to the Home tab on the top ribbon, click on "Conditional Formatting", and select "New Rule" from the dropdown menu.
In the New Formatting Rule dialog box, select the option that says "Use a formula to determine which cells to format".
Input the formula: =AND($C2="Vendors",$D2="Non-Compliant"). The dollar signs ($) are crucial as they lock the columns C and D for the criteria check.
Click the "Format" button, go to the "Fill" tab, choose your preferred highlight color, and click "OK" twice to apply the rule to your data.
Highlight Rows with Multiple Criteria in WPS Spreadsheet
WPS Spreadsheet offers powerful conditional formatting tools identical to Microsoft Excel. You can easily apply formula-based rules to highlight rows, manage large datasets, and customize your data visualization for free.
- 1. Select Your Data Range: Open your document in WPS Spreadsheet and select the data range you want to highlight (e.g., A2:D100).
- 2. Access Conditional Formatting: Go to the Home tab, click "Conditional Formatting", and select "New Rule".
- 3. Apply the Formula: Choose "Use a formula to determine which cells to format", enter your =AND() formula using absolute column references, set the format color, and click OK.
Frequently Asked Questions
Why is only the first cell highlighting instead of the entire row?
This usually happens if you forgot to add the dollar sign ($) before the column letters in your formula (e.g., C2 instead of $C2). The absolute reference is required to lock the condition to that specific column for the whole row.
Can I use more than two criteria with the AND function?
Yes, the AND function can handle multiple criteria. Simply add more conditions inside the parentheses separated by commas, like =AND($C2="Vendors", $D2="Non-Compliant", $E2="Overdue").
How do I edit or delete the conditional formatting rule later?
Go to the Home tab, click Conditional Formatting, and choose Manage Rules. From there, you can select your rule to edit the formula, change the highlight color, or delete the rule entirely.
