Conditional Formatting allows you to highlight cells based on specific conditions, making it easier to visualize data trends.
Steps to Apply Conditional Formatting
-
Select the Data Range
- Highlight the cells you want to format (e.g.,
A1:A10 or A1:D10).
-
Open Conditional Formatting
- Go to the Home tab.
- Click Conditional Formatting in the Styles group.
-
Choose a Rule Type
- Highlight Cell Rules (e.g., greater than, less than, equal to).
- Top/Bottom Rules (e.g., top 10 values, bottom 10%).
- Data Bars (creates a bar inside the cell based on values).
- Color Scales (gradient colors based on high/low values).
- Icon Sets (adds arrows, checkmarks, or other symbols).
-
Set the Condition and Formatting
- Choose a condition (e.g., "greater than 50").
- Select the formatting style (e.g., bold text, red fill).
- Click OK to apply.
Example 1: Highlight Cells Greater Than a Value
- Select A1:A10.
- Click Conditional Formatting → Highlight Cell Rules → Greater Than.
- Enter
50, choose a formatting style, and click OK.
Example 2: Apply a Color Scale for Visual Comparison
- Select A1:A10.
- Click Conditional Formatting → Color Scales.
- Choose a 3-color scale (e.g., red for low, yellow for mid, green for high).
Example 3: Use a Formula for Custom Formatting
You can use a formula to apply formatting dynamically.
Highlight Cells Where Value is Greater than Average
- Select A1:A10.
- Click Conditional Formatting → New Rule → Use a Formula to Determine Which Cells to Format.
- Enter the formula:
=A1>AVERAGE($A$1:$A$10)
- Click Format, choose a color, and click OK.
How to Remove Conditional Formatting
- Select the formatted range.
- Go to Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.
