Conditional Formatting Access in Excel: Where to Find It

In Excel for Microsoft 365 on Windows, access conditional formatting from Home > Conditional Formatting. Select the cells you want to format first, then choose a rule; if the command is missing or unavailable, check whether you are editing the workbook, whether the sheet is protected, and whether the selected range is appropriate.

Where to find conditional formatting

Conditional formatting is a command on Excel’s Home tab, not a separate application or a setting that must be installed. In the desktop version for Windows, select a cell or range, open Home > Conditional Formatting, and choose an option from the menu. The menu includes ready-made rules for highlighting cells, comparing values, displaying data bars, color scales, and icon sets. The exact choices shown can depend on the kind of rule you select.

To try a basic rule, select a range containing numbers, such as B2:B20, and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter a comparison value and choose a format. Excel applies the visual style to cells meeting the condition. The underlying values do not change, and the rule can update the appearance when those values change.

If you do not see the ribbon, expand it or select the Home tab. In a narrow Excel window, some controls may be compressed into smaller buttons or grouped menus. The command remains associated with Home. If you are using a different spreadsheet program, the menu path will not necessarily be the same.

What conditional formatting controls

Conditional formatting changes how cells look when they meet a condition. It can draw attention to overdue dates, low inventory, unusually high results, duplicates, or values that fall within a range. It does not, by itself, prevent someone from changing a cell, validate an entry, hide data securely, or change the value used in calculations. Treat it as a visual signal rather than an access-control or data-protection feature.

For example, a sales table might use a green fill for values above a target and a red fill for values below it. A due-date column might use a warning color for dates approaching today. A color scale can show relative highs and lows across a range, while data bars provide a quick visual comparison between numeric values. These formats are useful when a reader needs to scan a table, but the legend or rule should be clear enough that color is not the only way to understand the data.

Conditional formatting is rule-based. A rule identifies which cells it applies to, tests a condition, and specifies a format for cells where the condition is met. A cell can be covered by more than one rule. When rules overlap, their order and settings can affect the final appearance, so a surprising color may come from another rule rather than from the cell’s value alone.

Apply a built-in rule

Built-in rules are the quickest way to highlight common conditions without writing a formula. A typical setup is:

Select the cells to evaluate. Include the intended data cells, and avoid headings unless you want them formatted too.
Choose Home > Conditional Formatting and pick the relevant category, such as Highlight Cells Rules or Top/Bottom Rules.
Enter the threshold or other requested value, then choose a format from the available choices.
Inspect several cells, including one that should qualify and one that should not, to confirm the rule behaves as expected.

Suppose a stock list has quantities in column C, beginning at C2. Select the quantity cells and choose a less-than rule with a threshold of 10 to flag low stock. If the range includes a heading or totals, the result may be misleading; choosing the data cells carefully is part of setting up the rule correctly. You can adjust the threshold later by editing the rule rather than recoloring each cell manually.

For dates, consider whether the stored values are genuine Excel dates rather than text that merely looks like a date. A rule cannot reliably compare text in the same way it compares date values. If a rule appears not to recognize a date, check the cell’s underlying value and number format before rebuilding the formatting.

Use a formula for a custom condition

Formula-based rules can express conditions that are not covered by the built-in menu, including checks that depend on another column. In Excel for Windows, select the cells to format and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, then choose the desired format.

With values in B2:B20, and with the intended formatting range also set to B2:B20, use =B2>100 to highlight values greater than 100. The reference B2 is relative, so Excel evaluates each cell in the selected range against its own value. For instance, 125 meets the condition and 95 does not. The formula does not add or subtract anything from the cell; it only determines whether the chosen format appears.

When a rule refers to a different column, the starting reference must line up with the first row of the range to which the rule applies. For example, to format A2:A20 when the corresponding status is in B2:B20, a formula can test the status in the same row. If the status text is the word Late, the formula is =$B2="Late". The dollar sign fixes the column at B while allowing the row number to adjust. This can highlight each matching item in column A without formatting the status column itself.

Formula rules are sensitive to reference type and range alignment. A reference such as $B$2 remains fixed on one cell; B2 can shift across both rows and columns; and $B2 fixes only the column. If every row is responding to the same status or the wrong row, inspect the Applies to range and the dollar signs in the formula.

Review, edit, and remove rules

To inspect existing rules in Excel for Windows, choose Home > Conditional Formatting > Manage Rules. In the rules manager, use the Show formatting rules for control to view rules for the current selection or the worksheet. This is especially helpful when the color visible in a cell does not match the rule you expected: a rule may cover a larger range, or another rule may also apply.

Select a rule to edit its condition, format, or range, or remove a rule that is no longer needed. The Applies to field is important: a rule intended for B2:B20 might accidentally cover B:B, a total row, or another block. Broad ranges can make a workbook harder to understand and may slow recalculation in complex files. Keep the range focused on the data that needs the formatting.

If two rules affect the same cells, review their order and any setting that stops evaluation when a rule is true. Changing rule priority can change which format is visible. Before deleting a rule, confirm what other parts of the worksheet depend on it. Conditional formatting can be subtle, and removing it does not delete cell values, but it can remove an important warning or visual cue.

Access on Mac and in Excel for the web

In Excel for Microsoft 365 on Mac, conditional formatting is also available from the Home tab, usually through Home > Conditional Formatting. The interface and dialog layout can differ from Windows, and keyboard navigation differs between operating systems. If a Windows instruction names a particular dialog control, look for its equivalent in the Mac conditional-formatting menu rather than assuming the screens are identical.

Excel for the web also provides conditional-formatting controls on its Home tab, but the browser interface and available editing options can differ from the desktop application. If a workbook contains a complex set of rules and the browser does not expose the setting you need, open the file in the desktop Excel app and use Manage Rules there. Existing formatting may still be visible in the browser even when a particular rule is easier to edit on desktop.

Menu access is not the same as having permission to change the workbook. If you opened a file with view-only access, you may be able to see its formatting but not edit the rules. A protected worksheet can also limit changes. The workbook owner or administrator may need to grant editing access or adjust protection. Do not create a duplicate rule simply because the existing rule is difficult to edit; first check whether you are in the correct app and have permission to modify the file.

Common access and display problems

If the Conditional Formatting command appears unavailable, begin with the simplest checks: make sure you are in an editable workbook, select a cell or range, and confirm the Home tab is active. A protected sheet or view-only file may limit editing. If the command is merely hard to locate, expand the ribbon or widen the window to reveal controls hidden by a compact layout.

If a rule is available but does not appear to work, check the data and the rule rather than repeatedly applying a new one. Common causes include:

The wrong range is selected. The rule may apply to a heading, a neighboring column, or only part of the intended data.
Numbers or dates are stored as text. A value that looks numeric may not compare as a number.
A formula reference is misaligned. The formula’s first row should correspond to the first row in Applies to.
Another rule also applies. Review rule order and overlapping ranges in Manage Rules.
The format is hard to see. A subtle fill, font color, or existing cell style can make a valid rule look absent.

Use a small test range when diagnosing a complicated rule. Check one cell that should qualify and one that should fail, then inspect the rule’s condition and Applies to range. This is more reliable than judging the entire sheet by eye, especially when rules depend on formulas or other columns.

Frequently Asked Questions

Where is Conditional Formatting in Excel?
In Excel for Microsoft 365, select the relevant cells and open Home > Conditional Formatting. Use Manage Rules on that menu to inspect or change rules already applied.

Why is Conditional Formatting greyed out?
You may have view-only access, be working on a protected sheet, or be in an interface that does not expose the control in the same way. Check editing permissions and sheet protection, then try the desktop app if you need rule-management options not shown in the browser.

Can conditional formatting stop people from editing a cell?
No. It changes a cell’s appearance based on a condition; it does not enforce permissions or block edits. Use worksheet protection or file-sharing permissions when you need to control editing, and do not treat a color or hidden-looking format as security.

Can I use conditional formatting based on another cell?
Yes. Create a formula-based rule and reference the other cell or column. For a range starting in row 2, ensure the formula is written for that first row and set the Applies to range to the cells you intend to format.

Will conditional formatting change the cell’s value?
No. The rule changes visual presentation, not the stored value. A green fill, for example, does not make a number larger or affect a calculation unless a separate formula uses the underlying value.

For a dependable result, choose the data range deliberately, select a built-in rule when it fits, and use Manage Rules to check the scope whenever formatting behaves unexpectedly. Conditional formatting is most useful when its visual cues are accurate, consistent, and understandable to everyone who uses 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.