Excel AND Statement for Conditional Formatting Rules

How-to basics · Updated November 18, 2026

To use an AND statement in Excel conditional formatting, create a formula-based rule with the AND function. For example, if status is in column B and amount is in column C, select the rows to format and use =AND($B2="Open",$C2>=100); the format applies only when both conditions are true.

In Microsoft Excel for Microsoft 365 on Windows, select the range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula, choose a format, and confirm. The key is to make the formula’s row references match the first row of the selected range.

What AND means in conditional formatting

AND is a logical function: it returns TRUE when every condition supplied to it is true, and FALSE when one or more conditions are false. A conditional-formatting formula is evaluated for the cells in its “Applies to” range. Excel applies the chosen appearance where the formula evaluates to TRUE.

In the opening example, a row is highlighted only if its status is Open and its amount is at least 100. A row with an Open status and an amount of 85 does not qualify; a row with an amount of 150 but a Closed status does not qualify either. This is useful when a single field is not enough to identify the records you want to call out.

“AND statement” is a common way to describe this setup, but Excel uses the AND function inside a conditional-formatting rule. You are not writing a separate programming-language statement, and you do not need VBA. The formula simply supplies a TRUE-or-FALSE test for the formatting rule.

Create an AND conditional-formatting rule

For the example below, assume row 1 contains headings, column B contains status, column C contains amount, and the data runs from rows 2 through 100. To color the entire record when both conditions are satisfied:

  1. Select the cells to format, such as A2:D100. The top-left cell of the selection is A2.
  2. Choose Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter the formula shown below, then choose Format to set a fill, font, border, or number format.
  5. Choose OK to save the rule, and confirm the rule applies to the intended range.

With status in B2:B100 and amount in C2:C100, and with A2:D100 selected, the formula is:

=AND($B2="Open",$C2>=100)

The dollar signs lock the column letters B and C, while the row number remains relative. Excel can therefore check B2 and C2 for the first record, then B3 and C3 for the next one, and so on. Because the whole row range is selected, the same test controls the formatting of every cell from columns A through D in each qualifying row.

How references and selection ranges work

Conditional-formatting formulas are sensitive to both the selected range and the way cell references are written. Excel interprets a relative reference in relation to the upper-left cell of the rule’s Applies to range. If the formula is written for row 2 but the range begins on row 3, the rule can check the wrong record.

For a range beginning at A2, $B2 means “always look in column B, using the current row.” The dollar sign before the column prevents the reference from shifting sideways when the format is applied across A:D. The missing dollar sign before the row lets the row number advance for each record. In contrast, $B$2 locks both the column and row, so every record would be tested against B2 rather than its own status.

To highlight only the amount cells in C2:C100, select that range and keep the references tied to the same record. The top-left cell is now C2, but the test can still inspect B2 and C2. For a rule intended for a range that starts at row 5, adjust the formula’s row references to 5. When troubleshooting, check the Applies to range and the first row number together.

Useful AND formula examples

AND can combine numeric comparisons, text tests, dates, and blank-cell checks. Each condition must be a valid Excel logical test. Here are practical patterns:

  • For status plus a minimum amount, assume status is in B2:B100 and amount is in C2:C100, with A2:D100 selected: =AND($B2="Open",$C2>=100). This highlights rows where the item is Open and the amount is 100 or more.

  • For a date window, assume dates are in D2:D100, category is in E2:E100, and A2:E100 is selected: =AND($D2>=DATE(2026,1,1),$D2<DATE(2027,1,1),$E2="Review"). This checks for dates in calendar year 2026 and a Review category. The upper date test is exclusive, which avoids relying on a “last day” time value and includes any time on December 31.

  • For a required value that is not blank, assume status is in B2:B100 and owner is in C2:C100, with A2:D100 selected: =AND($B2="Open",$C2<>""). This highlights Open records that have an owner entered.

In the date example, the dates in column D must be real Excel date values, not text that merely looks like a date. If the workbook uses a different year or category label, change the date boundaries or text in the formula to match the data. Text comparisons in Excel are generally not case-sensitive, so a capitalization difference alone usually does not make an exact text test fail.

Combine AND with OR and other tests

Use AND when all specified requirements must be met. Use OR when any one of several alternatives is enough. You can nest the functions when a rule requires a mix, such as an Open item with either a high amount or an urgent priority.

Assume status is in B2:B100, amount is in C2:C100, and priority is in D2:D100, with A2:D100 selected. This formula means the status must be Open and at least one of the other tests must be true:

=AND($B2="Open",OR($C2>=500,$D2="Urgent"))

Parentheses make the intended grouping clear: the outer AND requires Open status, while the inner OR accepts either an amount of 500 or more or an Urgent priority. If the requirement is instead “Open and high amount, or Urgent regardless of status,” the grouping must change. Writing down the rule in ordinary language before building the formula helps prevent accidentally changing its meaning.

You can also create separate conditional-formatting rules rather than nesting functions. Separate rules can be easier to inspect when each condition needs a different color, but they may overlap. If multiple rules apply to one cell, review their order and any Stop If True setting in Conditional Formatting > Manage Rules.

Common problems and how to fix them

If the expected cells remain unformatted, first test the logic against actual values and inspect the rule’s Applies to range. Common causes include:

  • Wrong starting row: The formula uses row 2, but the selected range begins on row 3. Align the formula’s first row with the range’s top row.
  • Incorrect absolute references: Locking the row as well as the column, such as $B$2, makes every record refer to the same status cell. Use a relative row when each row should be checked independently.
  • Text or number mismatch: A value that looks numeric may be stored as text, or a status may contain an extra space. Check the source cells, not just their displayed appearance.
  • Wrong comparison boundary: >100 excludes 100; >=100 includes it. Choose the operator that matches the requirement.
  • Unexpected blank behavior: A blank cell can behave unexpectedly in comparisons, especially when it is being treated like zero. Add an explicit blank check if blanks should be excluded.
  • Wrong rule scope: A rule may apply to only one column when you intended to format entire rows. Select the full row segment or edit Applies to in Manage Rules.

To diagnose a complex rule, temporarily enter a simpler version, then add one condition at a time. You can also place a similar test in a worksheet cell to see whether it returns TRUE or FALSE for a particular record. The formula in a normal cell should use the same row references as the corresponding record; remember to remove any temporary test when finished.

Review, edit, and copy rules

Choose Home > Conditional Formatting > Manage Rules to inspect existing rules, edit their formulas, adjust their Applies to ranges, and change their order. In the Show formatting rules for list, choose the relevant worksheet or selection if the rule you need is not visible. This is particularly helpful in workbooks where several rules have accumulated over time.

When copying formatted cells, Excel may copy conditional-formatting rules as well as the visible formatting. That can create duplicate rules or expand a rule’s range. After copying, review Manage Rules and remove duplicates if necessary. If you want to reuse the logic on a different range, confirm that the formula’s relative row references and the new Applies to range still line up.

On Excel for Mac, the Home tab also provides Conditional Formatting and New Rule; dialog wording or layout can vary slightly by release. Excel for the web has conditional-formatting controls too, though some editing options may differ from desktop Excel. If a workbook must work across desktop and web versions, keep the rule simple and confirm it displays as expected in the versions your team uses.

When to use another approach

An AND rule is appropriate when one formatting result depends on several conditions being true. It is not always the clearest choice. If you want to highlight a single threshold, such as values below zero, a built-in Conditional Formatting option may be quicker. For “either this or that,” use OR or separate rules instead of requiring every test to pass.

If a condition is complicated, consider adding a helper column that calculates a status such as “Review” or “OK,” then base formatting on that result. Helper columns make the logic visible to coworkers and are often easier to audit than a long nested formula. For straightforward row highlighting, however, an AND formula keeps the worksheet compact and updates automatically as cell values change.

Frequently Asked Questions

Can conditional formatting use AND in Excel?
Yes. Create a formula-based rule and use the AND function. The rule applies the chosen format when every condition in the function is true.

How do I highlight a whole row when two conditions are met?
Select the full data area, such as A2:D100, and write the formula using fixed column references and a relative row. For status in B and amount in C, use =AND($B2="Open",$C2>=100).

Why does my AND conditional-formatting formula highlight the wrong rows?
Usually the formula’s row reference does not match the first row of the Applies to range, or the references lock a row that should move. Check both the selected range and the dollar signs.

Can I use more than two conditions?
Yes. Add further logical tests inside AND, separated by commas. Every test must be true for the rule to apply. Keep long formulas readable, and consider a helper column if the logic becomes difficult to maintain.

What is the difference between AND and OR in conditional formatting?
AND requires every condition to be true. OR requires at least one condition to be true. You can nest them when a rule combines mandatory requirements with alternatives.

Related

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.