Yes. In Excel, conditional formatting can use a formula that evaluates to TRUE or FALSE to decide which cells receive a chosen format. Select the cells, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter the formula, and choose the formatting.
How to create a formula-based rule
Start by selecting the cells the rule should affect. The formula is evaluated for each cell in that selected range, so the selection’s top-left cell matters when you write relative references. For example, if you select A2:D20, Excel treats A2 as the starting point for the rule.
- On Windows, select the target cells and choose Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter a formula in the formula box. It should begin with an equals sign and produce a logical result, such as TRUE or FALSE.
- Select Format, choose options such as a fill color, font color, or border, and confirm the formatting choices.
- Choose OK to save the rule, then check several cells to confirm it behaves as intended.
For example, with status values in C2:C20 and a rule applied to A2:D20, enter =$C2="Late". The dollar sign fixes the status column, while the row number can change as Excel checks each row. The rule highlights all four cells in a row when that row’s status is Late.
In current Excel for Mac, use Home > Conditional Formatting > New Rule and choose the formula-based option in the rule dialog; the layout and wording can vary by release. In Excel for the web, available rule-creation and editing options can vary, so use the desktop app if the formula option you need is not shown.
How Excel evaluates the formula
A formula-based rule is not a formula that writes a result into a cell. Instead, Excel checks the formula against the cells covered by the rule and applies the selected formatting when the result is true. If the result is false, the rule does not apply its format to that cell. A number can also act as a logical result: zero is treated as FALSE, and a nonzero number as TRUE.
Excel adjusts relative references as it evaluates the rule across the applied range. Suppose the rule applies to A2:D20 and checks column C. The first evaluation uses row 2; when Excel moves to the next row, the row reference adjusts to 3. That is why the example uses a fixed column reference but a changing row reference. A formula that refers to the wrong starting row can appear to work in one place and misclassify the rest of the range.
Conditional formatting changes how a cell looks, not its stored value. A red fill does not make a number negative, and a displayed date is still a date serial value that Excel can use in calculations. Sorting, filtering, and formulas continue to work on the underlying values rather than the color itself.
Relative and absolute references
Dollar signs control which parts of a reference move as Excel evaluates a rule. A reference such as A2 is relative in both its column and row. The reference $A2 fixes the column but allows the row to change; A$2 fixes the row but allows the column to change; and $A$2 fixes both. Choosing the right pattern is essential when a rule applies to more than one cell.
Use a relative reference when the condition should inspect each cell independently. With scores in B2:B30 and the rule applied to B2:B30, use =B2<60. Each cell is compared with its own value. To shade an entire row according to the value in column B, apply the rule to A2:D30 and use =$B2<60. The column is locked so every cell in a row checks column B, while the row adjusts for the next record.
When a formula uses a fixed threshold stored in a cell, lock that reference if the rule spans multiple rows or columns. With values in B2:B50, the minimum threshold in F1, and the rule applied to B2:B50, use =B2>=$F$1. Changing F1 then changes the cutoff without editing the rule. For a range that starts on a different row or column, adjust the formula’s relative reference to match the range’s top-left cell.
Useful formula examples
These examples use the stated ranges as assumptions. Apply each rule to the range named in its description, then select a format that makes the condition easy to spot.
- With status labels in C2:C100 and the rule applied to A2:D100, use
=$C2="Late"to highlight complete rows with a Late status. This is useful for task lists and order trackers. - With sales values in D2:D50 and the rule applied to A2:D50, use
=$D2>=1000to highlight rows where sales are at least 1,000. The comparison includes exactly 1,000 because it uses greater-than-or-equal-to. - With possible duplicate IDs in A2:A100 and the rule applied to A2:A100, use
=COUNTIF($A$2:$A$100,A2)>1. This highlights every occurrence of an ID that appears more than once, including its first occurrence. - With names in A2:A100 and the rule applied to A2:A100, use
=A2=""to highlight blank cells. A formula returning an empty string can also appear blank to this test, so consider whether that is desirable for your data. - With amounts in B2:B100 and the rule applied to B2:B100, use
=B2<0to flag negative values, such as credits or losses. Select a format that remains readable for both positive and negative numbers.
These rules can be adapted, but keep the reference pattern aligned with the selected range. If you select whole rows or a range that begins in row 5, the formula must be written with the correct starting row, not copied blindly from an example that begins in row 2.
Dates, overdue items, and blank cells
Date rules often combine a date comparison with a check for whether the record is complete. TODAY() returns the current date and updates when Excel recalculates the workbook. With due dates in A2:A100, completion statuses in B2:B100, and a rule applied to A2:B100, use =AND($A2<>"",$A2<TODAY(),$B2<>"Complete"). It highlights rows with a nonblank due date before today and a status other than Complete. The explicit blank check prevents empty date cells from being treated as old dates.
For a different requirement, such as highlighting dates due within the next seven days, make the boundaries explicit. With due dates in A2:A100 and the rule applied to A2:A100, use =AND(A2>=TODAY(),A2<=TODAY()+7). This includes today and the date seven days from today. If completed items should be excluded, add a status condition using the appropriate column and lock that column in the same way as the overdue example.
Excel stores real dates as numeric values. If a date comparison does not work, check that the cells contain actual Excel dates rather than text that merely looks like a date. Changing a cell’s number format cannot convert text into a valid date by itself.
Common problems and how to fix them
- The wrong rows are highlighted: Check the formula’s row number against the top row of the applied range. A rule applied to A2:D20 generally needs references that begin with row 2.
- Every row checks the same cell: A fully absolute reference, such as $C$2, keeps both the column and row fixed. For a row-by-row test, lock the column but leave the row relative, as in $C2.
- The rule works in one column but not others: Confirm the rule’s Applies to range and make sure the reference fixes only the parts that should stay fixed. A relative column reference may shift as Excel checks across columns.
- A blank looks like a match: Comparisons involving blank cells can produce unexpected results, especially with dates and numbers. Add an explicit nonblank test where needed, such as a check that the key cell is not empty.
- The formula is rejected: Check for the leading equals sign, balanced parentheses, straight quotation marks around text, and valid function names. Excel formulas normally use commas between function arguments in standard installations; regional settings can affect separators.
- A correct rule seems hidden: Another conditional-formatting rule may also apply to the same cells. Review the rule order, formats, and any Stop If True setting in the rules manager.
To inspect or edit rules in Excel for Microsoft 365 on Windows, choose Home > Conditional Formatting > Manage Rules. Check the Show formatting rules for list so you are viewing the rules for the current selection or the whole worksheet as appropriate. The Applies to field is especially useful when a rule was copied, pasted, or extended and now covers a different range than expected.
Manage rule order and workbook performance
More than one conditional-formatting rule can apply to the same cell. Excel may combine compatible formats, while conflicting formats are affected by rule order and the Stop If True option. In Manage Rules, move a more important rule higher when needed, and use Stop If True only when lower rules should not be evaluated after that rule is true. For example, a high-priority error format might need to take precedence over a general highlight.
Rules can also be copied with cells, which may expand or duplicate their Applies to ranges. After pasting formatted cells, inspect Manage Rules if formatting appears inconsistent. Keeping a small number of clear rules, each scoped to the data area rather than an entire worksheet, makes a workbook easier to understand and can avoid unnecessary work when Excel recalculates formatting.
When a condition is reused in many places, consider whether a helper column would make the logic easier to audit. A helper column can display a label such as Overdue or Review, while conditional formatting simply formats cells based on that label. This is not required, but it can be easier to troubleshoot than a long rule formula containing several nested conditions.
Formula rules versus built-in rules
Built-in conditional-formatting options are often quicker for simple tasks. Home > Conditional Formatting includes options such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. Use them when the condition is a straightforward comparison or visual scale that the dialog already supports.
Choose a formula rule when the condition depends on another cell, combines multiple tests, or needs to format a whole row based on one column. Formula rules offer flexibility, but they require careful reference choices and are less self-explanatory to someone opening the workbook later. A short, clearly structured formula and a sensible Applies to range make the logic easier to maintain.
Frequently Asked Questions
Does a conditional-formatting formula have to return TRUE or FALSE?
That is the clearest approach, but Excel can also interpret numeric results as logical values: zero is false and a nonzero number is true. A comparison or a function such as AND commonly returns TRUE or FALSE directly.
Why does my formula start with a different row number than the first highlighted row?
The formula should normally be written relative to the top-left cell in the rule’s Applies to range. If the range begins in row 2, a row-by-row formula typically starts with row 2. If the range begins in row 5, adjust the reference to row 5.
Can I use a formula to highlight an entire row?
Yes. Apply the rule to all columns in the rows you want formatted, then use a mixed reference to lock the condition’s column and leave its row relative. That makes every cell in a row evaluate the same row’s status or value.
Can conditional formatting change a cell’s value?
No. It changes appearance only. Use a worksheet formula, data validation, or another appropriate Excel feature if you need to calculate or restrict the underlying value.
Why is a formula-based rule not updating immediately?
Check that calculation is enabled and that the formula depends on values Excel has recalculated. Rules using TODAY() change as the current date changes and the workbook recalculates; the displayed format does not alter the data itself.
Related
- Edit Data Validation in Excel: Lists, Rules, and Fixes
- Duplicate Conditional Formatting in Excel: Find Values
- Dropdown Slicer in Excel: What It Is and How to Use One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Date Plus 3 Months: Formula, Rules, and ExamplesTo add three calendar months to a date in Excel, use =EDATE(A2,3), assuming the starting date is in…
- Excel Date Math: Formulas, Serial Numbers, and ExamplesExcel date math works by treating dates as numbers: subtract one date from another to count elapsed days,…
- SUMIF and SUMIFS Examples in ExcelIn Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet…
- Month Excel Formula From Date: Number, Name, or DateTo get the month number from a date in Excel, use =MONTH(A2) when the date is in cell…
- Excel Formula for Numbering Rows: Static and DynamicTo number rows in Excel, enter =ROW()-1 in cell A2 when row 1 contains a header, then fill…
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.