Conditional Formatting Greyed Out? Excel Fixes

If Conditional Formatting is greyed out in Excel for Microsoft 365 on Windows, first check whether you are editing a cell, have multiple worksheets grouped, or are working in a protected or read-only workbook. Exit cell editing, ungroup the sheets, or get permission to edit the file; then select a worksheet cell and open Home > Conditional Formatting.

Quick checks before changing settings

Start by selecting an ordinary cell in the worksheet, rather than a chart, shape, text box, or other object. Excel changes the ribbon according to what is selected, and commands intended for cells may not be available while an object is active. If a cell is selected but the ribbon command is still dimmed, check the workbook and sheet state before assuming the feature is missing.

Press Esc to leave cell-edit mode, or press Enter to commit the text or formula being edited. On Mac, Esc cancels the edit and Return commits it.
Look for a Protected View or read-only notice near the top of the window. If the file is trusted and you are authorized to edit it, use the available editing option or ask the file owner for access.
Check whether worksheet tabs are grouped. A group label may appear in the Excel title bar, and several tabs may look selected.
Check whether the worksheet is protected. The Review tab can show whether sheet protection is active.
Close menus or dialogs that are waiting for an action, then click a cell again and check the Home tab.

These checks address the usual causes without altering the formatting rules already in the workbook. If the command becomes available after one change, make the desired adjustment and then restore any protection or view settings that are needed.

Grouped worksheets can disable commands

When more than one worksheet is selected as a group, Excel applies many edits across the selected sheets at once. Some commands are unavailable in this state, including commands that depend on managing a sheet’s individual conditional-formatting rules. Grouping can happen accidentally when you Ctrl-click worksheet tabs, Shift-click a range of tabs, or right-click a tab and choose an option that selects multiple sheets.

To check, look at the sheet tabs at the bottom of the window. If multiple tabs are highlighted, or the title bar indicates that sheets are grouped, right-click one of the selected tabs and choose Ungroup Sheets. You can also click a worksheet tab that is not part of the group, if one is available. Select a cell on the sheet you want to change and look at Home > Conditional Formatting again.

Ungrouping does not delete rules. It simply returns Excel to working with one active sheet at a time. If you intentionally grouped sheets to enter the same values across them, finish that task first, then ungroup before editing conditional formatting. Also verify the rules on each sheet afterward: changing one sheet’s rule should not be assumed to update other worksheets that were previously grouped.

Sheet protection and workbook permissions

Worksheet protection is designed to limit changes to cells and worksheet features. If protection is on, Excel may prevent you from adding, editing, or deleting conditional-formatting rules. On Windows, open Review and look for Unprotect Sheet. If that command is present, the sheet is protected; selecting it may require a password. On Mac, the protection controls are also on the Review tab, although their placement can vary with the ribbon layout.

If you are authorized to make the change, ask the workbook owner for the password or have the owner unprotect the sheet, update the rule, and protect it again. Do not try to work around a password when you do not have permission. The Protect Sheet options can allow certain actions, but permission to format cells should not be treated as a guarantee that a user can manage conditional-formatting rules. The owner should check the specific protection behavior in the workbook.

Workbook structure protection is different from worksheet protection. It can restrict actions such as adding, deleting, moving, or renaming tabs, but it is not the same control as protecting cell contents on a sheet. If the problem is specifically that the Conditional Formatting command is disabled, check sheet protection and editing permissions rather than assuming that a locked workbook structure is the cause.

Protected View, read-only files, and editing mode

Excel can open a file in a state where changes are restricted. For example, a downloaded file may open in Protected View, or a file shared with you may be marked read-only because your account has viewing rather than editing access. In either situation, a command may be unavailable because Excel is preventing changes to the document, not because the workbook lacks conditional formatting.

For a trusted file in Protected View, use Enable Editing in the message bar if that option appears. Do not enable editing for an unexpected or untrusted attachment just to make a ribbon command active. For a file stored in OneDrive, SharePoint, or another shared location, check that you are signed in with the account that has edit permission. If the file is read-only, request edit access or create an authorized working copy.

Cell-edit mode is a separate, temporary cause. It begins when you type into a cell or edit its contents in the formula bar. Press Esc to cancel or Enter to finish, then select the target cell. If an operation is still in progress, Excel may disable other ribbon actions until you finish or cancel it.

Choose the right conditional-formatting command

Sometimes the feature is available, but a particular option appears unavailable because of the current selection or the menu you opened. Select the cells that the rule should affect, then use Home > Conditional Formatting. From there, choose a rule type such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets. For more control, choose Manage Rules.

The Rules Manager is useful when the worksheet already contains rules and you need to inspect their ranges, order, or formulas. In Manage Rules, check the Show formatting rules for list: it may be displaying rules for the current selection rather than the entire worksheet. Choose the worksheet view when you need to review all rules on that sheet. This can explain why a rule seems to have disappeared, but it does not itself fix a disabled ribbon button.

There is also a difference between a rule being unavailable and a rule not producing a visible result. A rule can be present and active while no cells currently meet its condition. For example, a rule that highlights values below zero will show no colored cells if every value in the selected range is zero or positive. In that case, the command is not actually greyed out; inspect Manage Rules, the Applies to range, the condition, and any higher-priority rules.

Other causes and a careful troubleshooting sequence

If none of the common causes fits, use a controlled sequence to narrow down the problem. Avoid deleting rules or changing workbook structure as an initial test, because those actions can affect other users or remove intentional formatting.

Save a permitted working copy if appropriate, then select a normal cell on the affected worksheet.
Press Esc or Enter if a cell or formula bar is being edited.
Check for grouped tabs and choose Ungroup Sheets if multiple sheets are selected.
Check for Protected View, read-only status, or missing edit permission.
Check the Review tab for sheet protection. Ask the owner to unlock the sheet if you are not responsible for its protection.
Reopen Home > Conditional Formatting. If the command works, open Manage Rules and inspect the relevant rule and its Applies to range.

If the command is still disabled in a workbook you can edit, test whether it is limited to that file: open a new blank workbook, select a cell, and check Home > Conditional Formatting. If it works there, the original workbook’s protection, selection state, or file permissions are the stronger leads. If it is disabled in a blank workbook too, close and restart Excel, confirm you are using the desktop Excel app rather than a restricted viewer, and check whether an organization-managed installation or policy limits editing. Ask your Microsoft 365 administrator or support team if the restriction persists.

Compatibility Mode is not a reason to assume conditional formatting is entirely unavailable. Older file formats can have feature limits, but the command may still be present. If Excel displays a compatibility warning, make a copy and use File > Save As to save in a modern Excel workbook format only when that is appropriate for everyone who needs the file. Saving in a new format can affect compatibility with older software, so it is not the first fix for a greyed-out button.

Excel for the web and Mac differences

In Excel for the web, conditional formatting is available through Home > Conditional Formatting, but editing capabilities can differ from desktop Excel. File permissions still matter: a viewer cannot necessarily make changes, and some workbook features may require opening the file in the desktop app. If the web ribbon is disabled, check that the workbook is in editing mode and that your account has edit access. For a rule or workbook feature the web version cannot edit, use Open in Desktop App if you are authorized and have Excel installed.

On Mac, the broad troubleshooting logic is the same: finish cell editing, ungroup tabs, check sheet protection, and verify that the file is editable. The ribbon arrangement and wording may differ slightly by version, but conditional formatting is on the Home tab. Use Esc to cancel an edit; Return generally commits it. Windows-specific shortcuts such as Ctrl-clicking a tab to select nonadjacent sheets differ on Mac, where Command-click is commonly used. The sheet-tab context menu remains a straightforward way to locate Ungroup Sheets.

Frequently Asked Questions

Why is Conditional Formatting greyed out in Excel?

The most common reasons are grouped worksheets, sheet protection, read-only access or Protected View, and being in the middle of editing a cell. Select a worksheet cell, finish or cancel editing, ungroup tabs, and confirm that you have permission to change the workbook.

How do I know if my Excel sheets are grouped?

Look for several highlighted worksheet tabs or a group indication in the Excel title bar. Right-click a selected tab and choose Ungroup Sheets, or click a tab outside the group. Then select a cell on the sheet you intend to edit.

Can I add conditional formatting to a protected sheet?

Protection can prevent changes to conditional-formatting rules. If you are authorized, use Review > Unprotect Sheet and provide the required password, or ask the workbook owner to make the change. Do not assume that permission to format cells also grants permission to edit rules.

Why does the rule exist but nothing is highlighted?

The cells may not meet the rule’s condition, the Applies to range may not include them, or another rule may affect the result. Open Home > Conditional Formatting > Manage Rules and review the condition, range, and rule order before creating a duplicate.

Is Conditional Formatting unavailable in Excel for the web?

No. Excel for the web includes conditional-formatting controls, but available editing options depend on the workbook, account permissions, and feature support. If a specific rule cannot be changed there, open the workbook in desktop Excel if you have access.

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.