Yes. In Excel for Microsoft 365 on Windows, you can use the IFS function in a conditional-formatting formula, provided the formula ultimately returns TRUE or FALSE. Select the cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter an IFS formula, and choose the format to apply.
What IFS Does in a Conditional-Formatting Rule
IFS checks conditions in order and returns the result associated with the first condition that is TRUE. That makes it useful when a rule needs to test several alternatives. Conditional formatting, however, does not ask IFS to choose a color or font. The rule’s formula decides whether the formatting should be applied; you choose the appearance separately in the rule settings.
For a formula-based rule, Excel evaluates the formula for the cells in the rule’s Applies to range. A TRUE result applies the chosen format to a cell, and a FALSE result does not. So an IFS formula needs to produce a logical result, or a result Excel can evaluate as logical. Returning the Boolean values TRUE and FALSE explicitly is the clearest approach.
For example, a status rule could return TRUE for either “Late” or “Due soon,” and FALSE for all other statuses. The final TRUE condition acts as a default, so IFS always has a result even when none of the earlier tests matches. That fallback is important: without a matching condition, IFS returns the #N/A error, which is not a dependable way to control conditional formatting.
How to Add an IFS Rule in Excel
First select the range that should receive the formatting. If the rule should color an entire row based on a status column, select the full row range, not just the status cells. Then create a formula rule and set the format.
- Select the target cells, such as A2:F100.
- On the ribbon, choose Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter a formula that returns TRUE for the cells you want to format.
- Select Format, choose a fill, font, border, or other available appearance, and confirm the dialogs.
With statuses in D2:D100 and the intended formatting range A2:F100, use this formula. It highlights a row when its status is either “Late” or “Due soon”; every other status returns FALSE.
Assumed layout: statuses are in D2:D100, the selected range is A2:F100, and the active cell when creating the rule is A2. Formula:
=IFS($D2="Late",TRUE,$D2="Due soon",TRUE,TRUE,FALSE)
The dollar sign fixes the status column at D while leaving the row number relative. As Excel evaluates the rule down the range, it checks D2 for the first row, D3 for the next, and so on. If the selected range starts somewhere other than row 2, adjust the formula’s row reference to match its top row.
Examples for Multiple Conditions
IFS is most useful when you want to express several distinct tests in one formula. You can combine IFS with functions such as AND to require more than one condition in a branch. For instance, you might flag a record only when its score is high and its review status is complete, or when a lower score is marked for review.
Assumed layout: scores are in B2:B100, review statuses are in C2:C100, the selected range is A2:C100, and the first selected row is row 2. This rule highlights a row if a score of at least 90 is complete, or a score of at least 70 is marked for review:
=IFS(AND($B2>=90,$C2="Complete"),TRUE,AND($B2>=70,$C2="Review"),TRUE,TRUE,FALSE)
For row 2, a score of 92 with status Complete makes the first test TRUE, so the rule applies. A score of 75 with status Review makes the second test TRUE, so it also applies. A score of 92 with status Review does not meet either pair of conditions and returns FALSE. The order matters if conditions overlap: IFS uses the result for the first matching condition and does not continue to a later match.
A date check is another common case, but blank cells need attention. Excel can treat an empty date cell like a zero in date comparisons. If the goal is to format past-due dates but leave blanks unformatted, test for a blank first.
Assumed layout: due dates are in E2:E100, the selected range is A2:E100, and the first selected row is row 2. The formula highlights rows with a nonblank date earlier than today:
=IFS($E2="",FALSE,$E2<TODAY(),TRUE,TRUE,FALSE)
TODAY updates with the current date when Excel recalculates. If you need a fixed cutoff date rather than a date that changes, use a cell containing that cutoff and refer to it in the rule.
Order, Defaults, and Rule Priority
There are two different kinds of order to consider. Inside IFS, Excel checks the logical tests from left to right and returns the value paired with the first TRUE test. Put more specific tests before broader ones. If a broad condition comes first, it may match and prevent a more specific branch from being reached.
Outside the formula, Excel also maintains an order for conditional-formatting rules. When two rules apply to the same cells, their priority can affect the final appearance. In Home > Conditional Formatting > Manage Rules, inspect the order and the Applies to ranges. The Stop If True option can prevent lower-priority rules from being evaluated for a cell. That setting is separate from IFS’s own first-match behavior.
A default branch such as a final TRUE,FALSE pair prevents an unmatched value from producing #N/A. You can also use a meaningful default result when the formula is part of a larger decision, but in a conditional-formatting test the default is commonly FALSE. Avoid leaving out the fallback unless you deliberately want an error when no test matches.
Relative References and the Applies To Range
Reference style is a frequent source of rules that seem to work on one cell but not across a table. In conditional formatting, Excel adjusts relative references as it evaluates cells throughout the Applies to range. A reference like D2 can shift both its column and row; $D2 keeps the column fixed but lets the row change; $D$2 keeps both fixed.
For a row-based rule, the usual pattern is to lock the column that contains the test value and leave the row relative. For example, when status is in column D and formatting covers A2:F100, $D2 checks the correct status for each row. If the intention is to use one control cell for every row, lock both parts of that cell reference instead. Check the top-left cell of the Applies to range: the formula should be written for that cell, not for an unrelated row.
If you edit the rule later, select a cell inside the affected range and use Manage Rules to review its formula and range. A valid formula can still produce unexpected results if the range starts on a different row, includes extra columns, or was applied to only part of the intended table.
When to Use IFS—and When to Choose Another Formula
IFS is not required just because a rule has several conditions. If the rule is simply “match this status or that status,” OR is usually shorter and easier to scan. If it is one condition that must be met together with another, AND expresses that directly. These functions can be more readable when they describe the logic plainly.
IFS can be a good choice when conditions are mutually exclusive or when you want a clearly ordered set of tests. It is less helpful when every branch returns the same TRUE result, because an OR formula may express the same idea with less repetition. It also cannot make one IFS branch use a red fill and another branch use a yellow fill inside a single conditional-formatting rule. Use separate rules with separate formats for that.
For example, to give “Late” and “Due soon” different colors, create one rule for each status and assign each rule its own format. Alternatively, a formula rule can target one status at a time. A single IFS rule can answer whether a cell qualifies for one chosen format, but it cannot dynamically select the format itself.
Common Problems and How to Fix Them
- #N/A appears: Add a final fallback test and result, commonly TRUE,FALSE, so the formula has an answer when no earlier condition matches.
- The wrong rows are formatted: Verify that the formula’s row number matches the first row of the Applies to range. Lock the condition column with a dollar sign if the rule spans multiple columns.
- Every row gets the same format: Check whether the formula accidentally uses an absolute row reference, such as $D$2, when it should check each row’s status.
- No cells change: Confirm that the formula returns TRUE for a sample row and that the text matches exactly. Extra spaces or different wording can make a text comparison FALSE.
- IFS is not recognized: IFS is available in Microsoft 365 and Excel 2019 or later. In older Excel releases, use nested IF functions or an equivalent combination of AND and OR.
- The right format does not show: Review competing rules in Manage Rules. A higher-priority rule or Stop If True setting may be affecting the result.
Also confirm that the formula begins with an equals sign and that its parentheses and argument pairs are complete. Excel’s argument separator depends on regional settings; some installations use semicolons instead of commas. If a comma-based formula is rejected, check the separators Excel expects for that installation rather than changing the logic.
Version and Platform Notes
IFS is supported in Microsoft 365 and Excel 2019 and later. If someone opens the workbook in an older version that does not recognize IFS, the function may appear as an unrecognized-name error. If a workbook must work across older installations, consider a nested IF formula or simpler AND/OR logic, and confirm compatibility with the intended recipients.
On Windows, the formula-rule path is Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. On Excel for Mac, use Home > Conditional Formatting > New Rule; the rule dialog and labels can differ slightly by release, but choose the formula-based rule and enter the same logic. Excel for the web has its own rule interface, so do not assume every dialog label or formatting option matches the Windows desktop version.
Frequently Asked Questions
Can conditional formatting use IFS directly?
Yes. Enter IFS in a formula-based conditional-formatting rule. Make sure its result evaluates to TRUE for cells to format and FALSE for cells to leave unchanged.
Does IFS choose the color for each condition?
No. A conditional-formatting rule has a format chosen in its settings. To use different colors for different categories, create separate rules and assign a format to each one.
Why does my IFS conditional-formatting rule show #N/A?
IFS returns #N/A when none of its tests is TRUE and no fallback condition is included. Add a final default branch, often TRUE,FALSE, and check that the earlier tests match the cell values you expect.
Should I use IFS or OR in conditional formatting?
Use OR when any of several simple conditions should trigger the same format. IFS is useful when you want ordered tests with corresponding results, but it may be unnecessarily long if every branch returns the same Boolean value.
Can I use IFS in Excel 2016?
IFS is not available in Excel 2016. For a workbook that must support that version, replace it with nested IF logic or an equivalent formula built from functions available in that installation.
Practical Takeaway
Use IFS in conditional formatting when ordered, multi-branch logic makes the rule clearer, and make its result explicitly TRUE or FALSE. Include a fallback, set the Applies to range carefully, and remember that the format itself is chosen in the rule—not returned by IFS. For simple alternatives, OR or AND may be easier to maintain; for distinct colors, use separate conditional-formatting rules.
Related
- Python Pandas Excel to Dictionary: Practical Guide
- Python pandas Excel Formatting: A Practical Guide to Styles
- Correlation Table in Excel: Create and Read One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTA and COUNTIF in Excel: How to Use Them TogetherTo find what share of filled status cells say Complete, with statuses in B2:B11, use =COUNTIF(B2:B11,"Complete")/COUNTA(B2:B11). If five…
- Outlook Address Examples and How to Choose the Right OneCommon Outlook address examples include first.last@outlook.com, initials.number@outlook.com, and a work address such as first.last@company.com. The first two show…
- Excel MAX Function: Find the Highest Value in a RangeTo find the largest number in Excel, enter a formula such as =MAX(B2:B10) in an empty cell, replacing…
- Copy Formulas in Excel Without Creating External LinksTo copy a formula without linking it to another workbook, copy the formula text from the formula bar…
- How to Compare Two Columns in Excel With a FormulaTo compare two Excel columns row by row, enter =IF(A2=B2,"Match","Different") in C2, then fill the formula down. This…
- Excel Day Name Formula: Show the Weekday from a DateTo show the weekday name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.