Conditional Formatting
Highlight Cells Rules | Clear Rules | Top/Bottom Rules | Conditional Formatting with Formulas
Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.
Highlight Cells Rules
To highlight cells that are greater than a value, execute the following steps.
1. Select the range A1:A10.
![Conditional Formatting Example Conditional Formatting Example](https://www.excel-easy.com/data-analysis/images/conditional-formatting/conditional-formatting-example.png)
2. On the Home tab, in the Styles group, click Conditional Formatting.
![Click Conditional Formatting Click Conditional Formatting](https://www.excel-easy.com/data-analysis/images/conditional-formatting/click-conditional-formatting.png)
3. Click Highlight Cells Rules, Greater Than.
![Highlight Cells Rules Highlight Cells Rules](https://www.excel-easy.com/data-analysis/images/conditional-formatting/highlight-cells-rules.png)
4. Enter the value 80 and select a formatting style.
![Greater Than Greater Than](https://www.excel-easy.com/data-analysis/images/conditional-formatting/greater-than.png)
5. Click OK.
Result. Excel highlights the cells that are greater than 80.
![Greater Than Result Greater Than Result](https://www.excel-easy.com/data-analysis/images/conditional-formatting/greater-than-result.png)
6. Change the value of cell A1 to 81.
Result. Excel changes the format of cell A1 automatically.
![Conditional Formatting in Excel Conditional Formatting in Excel](https://www.excel-easy.com/data-analysis/images/conditional-formatting/conditional-formatting-result.png)
Note: you can also use this category (see step 3) to highlight cells that are less than a value, between two values, equal to a value, cells that contain specific text, dates (today, last week, next month, etc.), duplicates or unique values.
Clear Rules
To clear a conditional formatting rule, execute the following steps.
1. Select the range A1:A10.
![Select Cells Select Cells](https://www.excel-easy.com/data-analysis/images/conditional-formatting/select-cells.png)
2. On the Home tab, in the Styles group, click Conditional Formatting.
![Click Conditional Formatting Click Conditional Formatting](https://www.excel-easy.com/data-analysis/images/conditional-formatting/click-conditional-formatting.png)
3. Click Clear Rules, Clear Rules from Selected Cells.
![Clear Rules Clear Rules](https://www.excel-easy.com/data-analysis/images/conditional-formatting/clear-rules.png)
Top/Bottom Rules
To highlight cells that are above average, execute the following steps.
1. Select the range A1:A10.
![Top/Bottom Rules Example Top/Bottom Rules Example](https://www.excel-easy.com/data-analysis/images/conditional-formatting/top-bottom-rules-example.png)
2. On the Home tab, in the Styles group, click Conditional Formatting.
![Click Conditional Formatting Click Conditional Formatting](https://www.excel-easy.com/data-analysis/images/conditional-formatting/click-conditional-formatting.png)
3. Click Top/Bottom Rules, Above Average.
![Top/Bottom Rules Top/Bottom Rules](https://www.excel-easy.com/data-analysis/images/conditional-formatting/top-bottom-rules.png)
4. Select a formatting style.
![Above Average Above Average](https://www.excel-easy.com/data-analysis/images/conditional-formatting/above-average.png)
5. Click OK.
Result. Excel calculates the average (42.5) and formats the cells that are above this average.
![Above Average Result Above Average Result](https://www.excel-easy.com/data-analysis/images/conditional-formatting/above-average-result.png)
Note: you can also use this category (see step 3) to highlight the top n items, the top n percent, the bottom n items, the bottom n percent or cells that are below average.
Conditional Formatting with Formulas
Take your Excel skills to the next level and use a formula to determine which cells to format. Formulas that apply conditional formatting must evaluate to TRUE or FALSE.
1. Select the range A1:E5.
![Cells to Format Cells to Format](https://www.excel-easy.com/data-analysis/images/conditional-formatting/cells-to-format.png)
2. On the Home tab, in the Styles group, click Conditional Formatting.
![Click Conditional Formatting Click Conditional Formatting](https://www.excel-easy.com/data-analysis/images/conditional-formatting/click-conditional-formatting.png)
3. Click New Rule.
![Click New Rule Click New Rule](https://www.excel-easy.com/data-analysis/images/conditional-formatting/click-new-rule.png)
4. Select 'Use a formula to determine which cells to format'.
5. Enter the formula =ISODD(A1)
6. Select a formatting style and click OK.
![Use a formula to determine which cells to format Use a formula to determine which cells to format](https://www.excel-easy.com/data-analysis/images/conditional-formatting/use-a-formula-to-determine-which-cells-to-format.png)
Result. Excel highlights all odd numbers.
![Odd Numbers Odd Numbers](https://www.excel-easy.com/data-analysis/images/conditional-formatting/odd-numbers.png)
Explanation: always write the formula for the upper-left cell in the selected range. Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =ISODD(A2), cell A3 contains the formula =ISODD(A3), etc.
Here's another example.
7. Select the range A2:D7.
![Select Range Select Range](https://www.excel-easy.com/data-analysis/images/conditional-formatting/select-range.png)
8. Repeat steps 2-4 above.
9. Enter the formula =$C2="USA"
10. Select a formatting style and click OK.
![Format values where this formula is true Format values where this formula is true](https://www.excel-easy.com/data-analysis/images/conditional-formatting/format-values-where-this-formula-is-true.png)
Result. Excel highlights all USA orders.
![Conditional Formatting with a Formula Conditional Formatting with a Formula](https://www.excel-easy.com/data-analysis/images/conditional-formatting/conditional-formatting-with-a-formula.png)
Explanation: we fixed the reference to column C by placing a $ symbol in front of the column letter ($C2). As a result, cell B2, C2 and cell D2 also contain the formula =$C2="USA", cell A3, B3, C3 and D3 contain the formula =$C3="USA", etc.
No comments:
Post a Comment