2 Criteria Conditional Formatting in Excel: Two Methods

In Excel for Microsoft 365 on Windows, select the cells to format, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula using AND for two conditions that must both be true or OR when either one is enough. This guide covers both approaches, with working row-highlighting examples, setup details, and troubleshooting for people who want rules that respond to spreadsheet data instead of fixed cell values.

1. Use AND when both criteria must be met

An AND-based conditional-formatting rule highlights a row or cell only when every condition in the formula is true, making it suitable for records that qualify through a combination of facts rather than one fact alone. For example, a task list might need to flag a task only when its status is Open and its due date has already passed; an open task due next week should stay unformatted, and a completed overdue task should also stay unformatted. This is worth using for exception lists, such as overdue open work, low-stock items from a particular supplier, or invoices that are both unpaid and past due.

Assume a task table has task names in A2:A50, status in C2:C50, and due dates in D2:D50, with headers in row 1; select A2:D50 so the entire matching row can be highlighted. With that layout and the top-left selected cell at A2, use the formula =AND($C2="Open",$D2<TODAY()) in the conditional-formatting rule; the dollar signs lock the status and date columns while leaving the row number relative, so Excel checks the appropriate C and D values for each row. If row 2 has status Open and a due date of October 30, 2026, the formula evaluates TRUE on November 2, 2026, and Excel applies the chosen format to A2:D2; if the date is November 10, 2026, it evaluates FALSE.

To create it, select the range, then use Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula in the rule box, choose Format to set a fill, font, border, or number format, and select OK to save the rule; choosing a pale red fill and dark text can make overdue work visible without obscuring the task name. Check the Applies to field in Conditional Formatting > Manage Rules: it should cover A2:D50, and the first row in that range should correspond to the row number used in the formula.

The TODAY function makes this rule update as the date changes, so a task can become highlighted the day after its due date without editing the rule. If some due-date cells are empty, Excel treats a blank like a zero in a date comparison, which is earlier than today; the two-condition rule can therefore flag open tasks with missing dates as if they were overdue. Prevent that by requiring a date to be present as an additional condition, or by validating the date column; if adding a condition, use =AND($C2="Open",$D2<>"",$D2<TODAY()) and apply it to the same range.

2. Use OR when either criterion is enough

An OR-based rule highlights a record when at least one of its conditions is true, even if the other condition is false. It fits a queue where a row deserves attention either because its priority is Urgent or because its status is Blocked; an urgent task still stands out while it is in progress, and a blocked normal-priority task also stands out. This approach is useful when several independent signals should lead to the same visual action, such as flagging a product that is either discontinued or out of stock.

Assume a work table has task names in A2:A50, priority in B2:B50, and status in D2:D50, with headers above the data; select A2:D50 to color each qualifying row. With the first data row at row 2, use =OR($B2="Urgent",$D2="Blocked") as the rule formula; the column references stay fixed as Excel evaluates each row, while the row number adjusts. A row with priority Urgent and status In Progress returns TRUE, as does a Normal-priority row with status Blocked, while a Normal and Done row returns FALSE.

Create the rule through Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, then set the format and verify Applies to in Manage Rules. Choose wording and spelling that match the source cells exactly: a cell containing “Urgent ” with a trailing space may not match the text “Urgent” in the formula, and “urgent” can be affected by how values were entered even though Excel text comparisons in these formulas are generally not case-sensitive. If the source uses controlled lists, data validation can reduce inconsistent labels and make this kind of rule more dependable.

OR is not the same as AND, and swapping the function changes the result substantially: AND requires both tests to be true, whereas OR requires one or more. Avoid adding an OR condition merely because it looks simpler if the intended meaning is “both”; for example, an open task with a future due date would be highlighted by an OR rule if either status or date condition passed, defeating an overdue-open filter. If the rule needs a mixture—such as urgent tasks regardless of status, plus blocked tasks only when they are assigned—write that logic deliberately with nested AND and OR functions, and test a few example rows before relying on the color.

How to Choose

Start by translating the requirement into plain language and identifying whether the conditions are joined by “and” or “or.” “Highlight when status is Open and the due date is before today” calls for AND because both facts define the exception; “highlight if the priority is Urgent or the status is Blocked” calls for OR because either signal is sufficient. This wording check is more important than the choice of fill color, since the wrong logical operator can make a polished rule report the wrong rows.

Next, decide what the formatting should apply to. For a full-row highlight, select the whole record range, such as A2:D50, while writing references to the relevant condition columns as $C2 and $D2; for highlighting only the date cells, select D2:D50 and still use row-relative references to the other columns as needed. Dollar signs anchor columns or rows: $C2 means always inspect column C but move down through rows, while C$2 would keep checking row 2 and is usually not what a per-record rule needs.

Use clear, consistent source values and confirm the data type before troubleshooting the formula. A date that looks like a date but is stored as text may not compare as expected, and a misspelled status label will not meet an exact text test; sort or filter the source columns, inspect representative cells, and correct the underlying data where possible. For date rules, decide whether “past due” means strictly earlier than today or includes today: D2<TODAY() excludes tasks due today, whereas D2<=TODAY() includes them.

Finally, check the Applies to range, the formula’s starting row, and rule interactions. If the selected range begins in row 2, use row 2 references in the formula; if it begins in row 5, write references for row 5. In Manage Rules, review the range and rule order, especially when multiple formats target the same cells, because a different rule can make the result appear unexpected; edit or remove obsolete rules rather than layering new ones blindly.

On Windows, the ribbon path is Home > Conditional Formatting > New Rule. Excel for Mac has the equivalent Conditional Formatting and New Rule controls on the Home tab, though dialog presentation can differ slightly; Excel for the web also offers conditional-formatting controls, but some rule editing tasks may be easier in the desktop app. Formulas use commas as argument separators in the examples here, which is the standard syntax for Excel configured with a comma list separator; regional settings can use a different separator.

Frequently Asked Questions

Can I use two criteria without writing a formula?

Some built-in rules, including certain comparisons and duplicate-value options, avoid formulas, but they do not cover every combination of columns. For criteria such as “status is Open and due date is before today,” a formula-based rule is the direct way to combine tests across fields. Use New Rule and select the formula option when the built-in rule types do not express the condition you need.

Does AND require both conditions to be true?

Yes. AND returns TRUE only when all of its listed logical tests are TRUE, so a conditional-formatting rule using AND applies its format only when every requirement is met. One FALSE test makes the complete AND result FALSE.

Does OR require both conditions to be true?

No. OR returns TRUE when at least one test is TRUE, and it returns FALSE only when all its tests are FALSE. That makes it appropriate for alternate reasons to flag the same record, but not for a requirement that says both facts must apply.

Why is the wrong row being highlighted?

Check that the formula row matches the first row of the Applies to range and that the condition columns have dollar signs where needed. For example, for a range beginning at A2, $C2 checks column C on each corresponding row; using $C$2 would make every row check the single value in C2. Also confirm that the rule applies to the intended range and that another conditional-formatting rule is not affecting the visible result.

Can I use a date criterion that updates automatically?

Yes. TODAY() returns the current date, so comparisons such as D2<TODAY() recalculate as the date changes; this is useful for rolling overdue checks. If you need a fixed cutoff instead, enter a date in a worksheet cell and reference that cell, so the threshold can be changed without editing the rule formula.

Final Thoughts

For two-criteria conditional formatting, choose AND when both facts must be true and OR when either fact should trigger the format. Anchor the condition columns, keep the row reference aligned with the first row of the Applies to range, and test a true and false example before using the rule on a live tracker. These checks make the formatting easier to maintain and reduce the chance that highlighted rows mislead the people using the workbook.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.