This article will show you some examples of how to use the excel conditional formatting tool to format cells by conditions in excel. That means it will only format the excel cells that match the condition. It will also tell you how to use a formula to implement excel conditional formatting.
1. How To Use The Excel Conditional Formatting Tool To Format Excel Cells By Condition Rules.
- In this example, there is a column of numbers in excel column A like below.
A 1 10 2 3 3 6 4 5 5 2 6 6
- And if you want to format the cells that are less than number 6 in column A, you can follow the below steps.
- Select the cell range A1:A6, then click the Home tab —> Conditional Formatting —> Highlight Cell Rules menu item in the Styles group.
- There are several operator items you can select, such as Greater Than, Less Than, Between .etc.
- In this example, we select the Less Than operator item, then it will open the Less Than dialog.
- If you use Excel on macOS, the popup dialog name is New Formatting Rule.
- Input the number 6 in the input text box, and click the OK button.
- Then it will select and highlight the cells which contain numbers less than 6 like below.
A 1 10 2 3 3 6 4 5 5 2 6 6
- If you change the number in cell A2 from 3 to 6, then cell A2 will not be formatted, which means the cell rule will change the cell format automatically.
- You can find other existing rules from the Home tab —> Conditional Formatting drop-down menu list such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets, you can try to use them as you need.
2. How To Remove Excel Conditional Formatting Rules.
- If you do not need to format some cells, or you want to apply other formats to those cells, you can remove the conditional formatting rules from those cells.
- Select the cells that you want to remove the conditional formatting rules.
- Click the excel Home tab —> Conditional Formatting —> Clear Rules —> Clear Rules from Selected Cells.
3. How To Use Formula To Implement Excel Conditional Formatting.
- Select the cell range A1:A6 in this example.
- Click the Home tab —> Conditional Formatting —> New Rule… menu item to open the New Formatting Rule window.
- On macOS, you should select the item Classic from the Style drop-down list, then you can select the item Use a formula to determine which cells to format from the drop-down list.
- On Windows, you can select the item Use a formula to determine which cells to format from the Select a Rule Type list.
- Input the formula =A1 < 6 in the formula input text box, you should be noticed that the formula must return a boolean value True or False.
- Click the OK button to close the dialog window.
- On Windows, there will pop up a floating icon on the bottom right corner of the cell range, click the floating icon, then click the Color… icon on the expanded icon list to change the cell’s color format by the condition, you can select other format styles icon to see the different format styles.
- On macOS, the default cell format style is to use the color style, if you want to use other format styles, you should select them from the Home tab —> Conditional Formatting menu items.
4. How To Manage Excel Conditional Formatting Rules.
- Click the excel Home tab —> Conditional Formatting —> Manage Rules… menu item will open the Conditional Formatting Rules Manager window ( on Windows) or the Manage Rules window ( on macOS ).
- Then you can see all the existing conditional formatting rules in the rules manager window.
- You can add new, edit, or delete existing conditional formatting rules in this window as you need.