In Excel for Mac, select the cells you want to format, then choose Home > Conditional Formatting and select a rule type or New Rule. Set the condition and appearance, then confirm; Excel will apply the formatting automatically whenever the cell values meet that condition.
What conditional formatting does
Conditional formatting changes how cells look in response to their contents. You can use it to call attention to overdue dates, unusually high values, repeated entries, or patterns across a table. Unlike manually applying a fill color, a conditional format is a rule: if the underlying value changes, Excel can remove, change, or apply the appearance again.
The feature changes presentation, not the cell’s actual contents. A red fill does not make a number negative, and a green icon does not certify that an entry is correct. Formulas, sorting, calculations, and exported data continue to use the stored values. Treat color as a visual cue, not as a substitute for a clear status column or accessible label.
Conditional formatting is available in Excel for Mac, including Microsoft 365 editions. Ribbon labels and dialog layouts can vary slightly between releases, but the main controls are on the Home tab. If a command is not where an older set of instructions shows it, check the current Home ribbon or the Excel Help search box.
Choose a rule that fits the data
Excel’s built-in rules are the quickest option when the condition is straightforward. Select the target cells, open Home > Conditional Formatting, and choose the relevant category. Depending on the selected rule, Excel asks for a threshold, text, date range, or comparison value, followed by a format.
- Highlight Cells Rules can mark values greater than or less than a number, text that contains a phrase, dates in a chosen period, or duplicate values.
- Top/Bottom Rules identify the highest or lowest values, including a number of items or a percentage of the selection.
- Data Bars show a bar inside each cell, useful for comparing magnitudes while retaining the numbers.
- Color Scales shade values along a gradient, helping reveal relative highs and lows in a range.
- Icon Sets add symbols according to thresholds. Check the threshold settings so the symbols communicate the intended categories.
These visualizations are most useful when the selected range contains comparable data. A color scale across sales amounts can show relative differences; using the same scale on a range mixing dates, text, and amounts is unlikely to communicate anything meaningful. Built-in rules are also easier for another workbook user to understand than a complicated custom formula.
Create a custom rule with a formula
A formula-based rule lets you format a cell based on another cell, combine several tests, or highlight an entire row. Select the range to format, choose Home > Conditional Formatting > New Rule, and select the formula-based rule option in the dialog. Enter a formula that evaluates to TRUE or FALSE, then choose the format. The exact option wording can differ by Excel release.
For a row-based example, assume A2:D100 contains a task table and column C contains each task’s status. Select A2:D100 before creating the rule. Use this formula, then choose a fill color:
With statuses in C2:C100 and the intended formatting range A2:D100, use =$C2="Overdue". If C3 contains Overdue, Excel formats A3:D3; rows with another status do not meet the condition.
The dollar signs control how references move as Excel checks each cell in the selected range. $C locks the status column, while the row number 2 is left relative so the next row is checked against C3, then C4, and so on. The formula is written for the top-left cell of the selected range. If the range instead begins at A5, adjust the row reference to 5.
As another example, you can highlight duplicate product codes. With codes in A2:A100 and that same range selected, use =COUNTIF($A$2:$A$100,A2)>1. Each code occurring more than once receives the chosen format. The fixed range stays the same for every check, while A2 adjusts as the rule evaluates each row. For a simple duplicate check, Excel’s built-in Duplicate Values rule is less work; the formula is useful when you need to combine duplicate detection with other conditions.
Use date and status conditions carefully
Date rules are useful for schedules, invoices, and task lists, but the cells must contain real Excel dates rather than date-looking text. A real date is stored as a number and can be compared with another date. If a rule behaves unexpectedly, check the cell’s value and number format; changing a text cell’s appearance to a date format does not necessarily convert its contents into a date.
Suppose column B contains due dates, column C contains task statuses, and A2:C100 is the selected range. To highlight rows whose due date has passed while the task is not complete, use:
With due dates in B2:B100 and statuses in C2:C100, and A2:C100 selected, use =AND($B2<TODAY(),$C2<>"Complete"). For example, a past date in B3 and a status other than Complete in C3 make Excel format row 3. A future date or a Complete status does not satisfy both tests.
If blank due-date cells should never be highlighted, add a nonblank test. With the same layout and selected range, use =AND($B2<>"",$B2<TODAY(),$C2<>"Complete"). This avoids treating an empty date as an overdue task. TODAY() uses the current date when Excel recalculates, so the result can change from one day to the next without editing the rule. If a workbook’s calculation setting is manual, recalculation may be needed for date-dependent results to refresh.
Edit rules, order, and formatting
To inspect or revise existing rules, select a relevant cell and choose Home > Conditional Formatting > Manage Rules. The Rules Manager lets you review the rules that apply to the selection, change their conditions or formats, and remove rules. If you cannot see the rule you expected, check the manager’s scope or “show formatting rules for” selection: a rule may apply to the current selection, the worksheet, or another range.
When more than one rule applies to a cell, rule order can affect the result. In the manager, move rules into the intended priority order where available. A rule with Stop If True can prevent lower-priority rules from being applied after it evaluates as true. This is useful for deliberate tiers, but it can also make another color seem to disappear. Inspect the order and stop setting before rebuilding rules that already exist.
Choose formats that remain legible. A dark fill may need a light font; a light fill may need dark text. Avoid relying on red and green alone to distinguish important states, since some readers have difficulty differentiating those colors. Add meaningful text such as “Overdue” or “Complete” and use color as a supporting signal. For an icon set or gradient, make sure the thresholds match the meaning of the underlying data.
Common problems and practical fixes
- The wrong rows are highlighted: Check the selected range and the formula’s starting row. A rule applied to A2:D100 should generally refer to row 2 in its formula, not row 1. Confirm that the column reference is locked when every cell in a row should check the same status column.
- Some cells never change appearance: Confirm that the cells are included in the rule’s applied range and that their values have the expected type. Text that looks like a number may not satisfy a numeric comparison.
- Two colors seem to conflict: Open Manage Rules and check whether multiple rules apply, their order, and any Stop If True setting. Simplify overlapping rules if their combined behavior is hard to explain.
- The formatting continues below the table: Review the rule’s applied range. A rule may cover more rows than the data needs, or a copied cell may have extended its range.
- Clearing a format does not remove the rule: Use Home > Conditional Formatting > Clear Rules and choose the option for the selected cells or the whole sheet. Clearing rules removes conditional formatting; it is different from changing a cell’s ordinary number format or manually applied fill.
Copying and pasting cells can bring conditional formats along, extend their ranges, or create overlapping rules. If a workbook has accumulated many similar rules, inspect Manage Rules and consolidate them rather than repeatedly adding new rules. Before making broad changes to an important workbook, save a separate copy so you can compare the result or restore the original.
Excel for Mac compared with Windows and the web
The central idea and most rule types are shared across Excel for Mac, Excel for Windows, and Excel for the web. This means a workbook with ordinary conditional formatting can generally be opened on another platform with its rules intact. However, the dialogs and ribbon placement can differ, and some advanced editing controls may not appear identically in every version.
For Mac, the steps in this guide use the Home ribbon. Windows users also commonly use Home > Conditional Formatting, though keyboard access and dialog details differ. The Windows ribbon shortcut sequence often cited for the Conditional Formatting menu is Alt, H, L; it is not a Mac shortcut. On Mac, use the ribbon or Excel’s menu search rather than assuming that Windows Alt-key sequences will work. In Excel for the web, rule creation is available through its browser ribbon, but a desktop Mac dialog should not be assumed to match the web interface exactly.
If a workbook is shared with people using different platforms, avoid depending on a subtle visual effect as the only way to interpret a value. Keep the actual status or category in the worksheet, and check the workbook in the editions your collaborators use when a rule is business-critical.
Frequently Asked Questions
Where is conditional formatting in Excel for Mac?
Select the cells, then open the Home tab and choose Conditional Formatting. From that menu, select a built-in rule or choose New Rule to create a custom condition.
Can Excel for Mac highlight a whole row based on one cell?
Yes. Select the full range of rows you want to format and create a formula-based rule that fixes the condition column while leaving the row relative. For example, if status is in column C and the selection begins in row 2, =$C2="Overdue" checks the status for each row.
Why does my conditional-formatting formula use dollar signs?
Dollar signs lock a column or row reference as Excel applies a rule across the selected range. In =$C2="Overdue", the column stays C but the row changes, allowing every row to check its own status cell.
Does conditional formatting change the cell value?
No. It changes the cell’s appearance when a rule is met, but it does not rewrite the value or replace the need for a formula. The value remains available for calculations and other workbook operations.
How do I remove conditional formatting in Excel for Mac?
Select the affected cells and use Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from the selected cells or the sheet. If you only want to change one condition, use Manage Rules instead of clearing every rule.
Choosing a reliable setup
Start with the smallest rule that clearly expresses what readers need to notice. Use a built-in comparison for a simple threshold, a color scale for comparable numbers, and a formula when the condition depends on other cells or several tests. Test the rule against a few known examples, including a value that should match and one that should not. Finally, review its range and appearance in the worksheet. These checks make conditional formatting easier to maintain and less likely to mislead someone reading the workbook later.
Related
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting Based on a Checkbox in ExcelYes. In Excel for Microsoft 365, conditional formatting can respond to a checked or unchecked box because a…
- Track Changes in Word in Red: Step-by-Step GuideTo track edits in red in Microsoft Word, turn on Review > Track Changes, then open the advanced…
- How to Use Mail Merge in Word to Send Emails Step by StepTo send a mail merge from Word, prepare a recipient list with an email-address column, choose Mailings >…
- Excel New Sheet: How to Add and Open OneTo add a new worksheet in Excel for Microsoft 365 on Windows, click the plus sign beside the…
- Add a Shared Mailbox in Legacy Outlook for MacTo add a shared mailbox in legacy Outlook for Mac, open Tools > Accounts, select your Exchange account,…
- How to Unhide Rows in Excel That Won’t UnhideIf Excel’s Unhide Rows command does nothing, first select the rows on both sides of the missing section…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.