MODIFYING DATA IN EXCEL (3)
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.
Step 1 : Select the range A1:A10.
Step 2 : On the Home tab, in the Styles group, click Conditional Formatting.
Step 3 : Click Highlight Cells Rules, Greater Than.
© 2024 Athena Global Education. All Rights Reserved
Step 4 : Enter the value 80 and select a formatting style.
Step 5 : Click OK.
Result. Excel highlights the cells that are greater than 80.
Step 6 : Change the value of cell A1 to 81.
Result. Excel changes the format of cell A1 automatically.
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.
© 2024 Athena Global Education. All Rights Reserved
Step 1 : Select the range A1:A10.
Step 2: On the Home tab, in the Styles group, click Conditional Formatting.
Step 3: Click Clear Rules, Clear Rules from Selected Cells.
Top/Bottom Rules
To highlight cells that are above average, execute the following steps.
Step 1 : Select the range A1:A10.
© 2024 Athena Global Education. All Rights Reserved
Step 2: On the Home tab, in the Styles group, click Conditional Formatting.
Step 3: Click Top/Bottom Rules, Above Average.
Step 4 : Select a formatting style.
Step 5: Click OK.
© 2024 Athena Global Education. All Rights Reserved
Result. Excel calculates the average (42.5) and formats the cells that are above this average.
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 of 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.
Step 1 : Select the range A1:E5.
Step 2 : On the Home tab, in the Styles group, click Conditional Formatting.
Step 3 : Click New Rule.
© 2024 Athena Global Education. All Rights Reserved
Step 4 :Select 'Use a formula to determine which cells to format'.
Step 5 : Enter the formula =ISODD(A1)
Step 6 : Select a formatting style and click OK.
Result. Excel highlights all odd numbers.
© 2024 Athena Global Education. All Rights Reserved
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.
Step : 7 Select the range A2:D7.
Step 8 : Repeat steps 2-4 above.
Step 9 : Enter the formula =$C2="USA"
Step 10 : Select a formatting style and click OK.
Result. Excel highlights all USA orders.
© 2024 Athena Global Education. All Rights Reserved
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.
© 2024 Athena Global Education. All Rights Reserved
