Why is Conditional Formatting Grayed Out in Excel

Sometimes you may see that the Conditional Formatting button in Excel is grayed out. This means it is unavailable.

This tutorial explains why the Conditional Formatting button may be grayed out in Excel and outlines solutions to the issue.

Why Conditional Formatting Button is Grayed Out in Excel

Three reasons can make the Conditional Formatting button grayed out. Excel may be in Edit mode, the worksheet may be protected, or some or all the worksheets in the workbook may be grouped.

Reason 1: Excel is in Edit mode

When you are editing the contents of a cell, Excel is operating in Edit Mode. When Excel is in Edit Mode the word Enter appears in the bottom left corner of the Excel window as shown below.

When you are in Edit mode, the Conditional Formatting option is grayed out and you cannot use it. In the following illustration, the Conditional Formatting option is grayed out because we are editing the contents of cell I2.

Solution

The Conditional Formatting button becomes active when Excel exits the Edit mode. You can exit Edit mode by doing any of the following:

• Press the Enter key on the keyboard. Excel moves out of Edit mode and selects the cell directly below the active cell.
• Click the Enter button that is on the left side of the formula bar. Excel goes out of Edit mode and leaves the cursor where it is.

• Press the Tab key. Excel leaves the Edit mode and selects the cell to the right of the current cell.
• Press the Esc key. Excel exits Edit mode and leaves the cursor where it is.
• Click a different cell. Excel goes out of Edit mode and selects the cell you clicked.

Reason 2: Your Worksheet is protected

We protect a worksheet to prevent accidental or deliberate deletion, moving, or changing of data. This has the effect of graying out the Conditional Formatting option.

If the Conditional Formatting button is not grayed out in other worksheets in the same workbook, it could be a sign that your worksheet is protected.

Solution

To unprotect the worksheet, do the following:

1. Click Review >> Protect >> Unprotect Sheet.

1. In the Unprotect Sheet dialog box that pops up, enter the password in the Password box and press OK.

The Conditional Formatting option becomes available.

Reason 3: You have grouped worksheets

Worksheets are grouped so that any changes made in one worksheet, are replicated in the same location in the other worksheets. This however makes the Conditional Formatting button to become unavailable.

You can tell that worksheets are grouped if the worksheet tabs have a white background and the word Group appears in the name of the workbook.

In the following illustration, three worksheets in the workbook are grouped. The Conditional Formatting button is grayed out.

Solution

Ungrouping the worksheets will cause the Conditional Formatting button to become active. To ungroup the worksheets, do any of the following:

• Right-click any worksheet tab in the group and choose Ungroup Sheets from the shortcut menu.

• Click any worksheet tab in the group if all the sheets in the workbook are grouped.
• Click any worksheet tab outside the group.

Conclusion

This tutorial has looked at three reasons why the Conditional Formatting button in Excel may be grayed out. Excel may be in Edit mode, the worksheet may be protected, or some or all the worksheets in the workbook may be grouped.

The tutorial has also outlined solutions to the reasons.