You may see cells in your Excel worksheet change color when you click on them. This unexpected behavior is not a default setting. It is almost always caused by conditional formatting rules applied to the cells. These rules can be hidden or inherited from a template, making them hard to find. This article explains how to locate and delete the specific conditional formatting rule causing the color change on selection.
Key Takeaways: Removing Conditional Formatting That Activates on Selection
- Home > Conditional Formatting > Manage Rules: Opens a dialog to view, edit, and delete all formatting rules affecting the selected cells or sheet.
- Condition based on formula =CELL(“row”)=ROW(): A common rule that highlights the active cell’s row; you must delete this rule to stop the color change.
- Clear Rules from Selected Cells or Entire Sheet: The quickest method to remove all conditional formatting when you do not need to review individual rules.
Why Conditional Formatting Causes Color Changes on Click
Conditional formatting applies colors, icons, or data bars based on specific conditions. A cell changing color only when selected is triggered by a rule that checks the active cell’s position. The most frequent cause is a formula using the CELL function. For example, a rule with the formula =CELL(“row”)=ROW() will highlight the entire row of the cell you click. This is because the CELL(“row”) function returns the row number of the last changed cell. When you select a new cell, the formula recalculates and applies the format to that new row.
These rules are often copied from online templates or shared workbooks. They can be applied to a single cell, a range, or an entire column. The formatting may also be set on a table or a pivot table. Since the rule only activates upon selection, it can be missed during a casual review of the sheet’s formatting.
Steps to Find and Delete the Conditional Formatting Rule
Follow these steps to identify and remove the rule that makes cells change color when selected.
- Select the affected cell or range
Click on a cell that changes color when selected. If the entire row or a large area highlights, select any cell within that formatted range. - Open the Conditional Formatting Rules Manager
Go to the Home tab on the ribbon. In the Styles group, click Conditional Formatting. From the dropdown menu, select Manage Rules. - Review the rules list
The Conditional Formatting Rules Manager dialog will open. From the “Show formatting rules for” dropdown at the top, select “This Worksheet” to see every rule. Look for a rule where the “Format” column shows a fill color and the “Formula” column contains a formula like =CELL(“row”)=ROW() or =CELL(“col”)=COLUMN(). - Delete the specific rule
Click on the unwanted rule in the list to select it. Then, click the Delete Rule button. Click Apply to see the change, then click OK to close the dialog. The cell should no longer change color when you click it.
Quick Method: Clear All Rules
If you do not need to review the rules and want to remove all conditional formatting, use this faster method.
- Select the target area
To clear rules from specific cells, select that range. To clear rules from the entire worksheet, click the triangle at the intersection of the row and column headers. - Use the Clear Rules command
Go to Home > Conditional Formatting. Hover over Clear Rules. Choose either “Clear Rules from Selected Cells” or “Clear Rules from Entire Sheet” based on your selection.
If the Color Change Persists After Removing Rules
Excel Still Highlights Cells on Click
If cells still get a background shade when selected, the issue might be Table Style highlighting. Click inside the range and check if the Table Design tab appears on the ribbon. If it does, go to Table Design > Table Styles and click the down arrow. Select Clear at the bottom of the gallery to remove all table formatting, including banded rows that may appear on selection.
Multiple Conflicting Conditional Formatting Rules
A cell can have several conditional formatting rules. Excel applies them in the order listed in the Rules Manager. A rule lower in the list might override the one you deleted. Open the Rules Manager again and ensure no other rules with a formula reference remain. Use the up and down arrows to change the order if needed, or delete all rules for certainty.
Formatting Applied via a Cell Style
Cell Styles are predefined formats in the Home tab. Some styles include a “Followed Hyperlink” effect that can change color. Select the cell, go to Home > Cell Styles, and choose Normal. This will revert the cell to the default style without special formatting.
Manual Clear vs. Rules Manager: Key Differences
| Item | Clear Rules from Selected Cells/Sheet | Conditional Formatting Rules Manager |
|---|---|---|
| Speed | Fast, one-click operation | Slower, requires dialog navigation |
| Control | Removes all rules indiscriminately | Allows selective deletion of specific rules |
| Best for | Sheets where you want to remove all conditional formatting | Troubleshooting or when you need to keep some rules |
| Visibility | Does not show what rules exist | Displays all rules, their formulas, and applied ranges |
You can now stop Excel cells from automatically changing color when you select them. Use the Conditional Formatting Rules Manager to find and delete the specific formula-based rule causing the behavior. For a clean slate, the Clear Rules command removes all formatting instantly. Next, try using the Format Painter tool to copy only the desired static formatting to other cells without carrying over conditional rules.