Excel – Conditional Formatting

Conditional Formatting

• • Definition

     

    • • Purpose: A feature that changes the appearance of cells based on specified conditions.
    • • Usage: Highlights data to make it easier to analyze and understand.

• • Accessing Conditional Formatting

     

    • • Location: Found under the “Home” tab in the Excel ribbon.
    • • Menu: Conditional Formatting drop-down menu with various options.

• • Common Conditional Formatting Rules

     

    • • Highlight Cell Rules

         

        • • Greater Than / Less Than: Highlights cells with values greater or less than a specified value.
        • • Between: Highlights cells with values between two specified numbers.
        • • Equal To: Highlights cells with values equal to a specified value.
        • • Text That Contains: Highlights cells containing specified text.
        • • A Date Occurring: Highlights cells with dates falling in a specified time frame.
        • • Duplicate Values: Highlights cells with duplicate or unique values within a range.

• • Top/Bottom Rules

     

    • • Top 10 Items / Bottom 10 Items: Highlights the top or bottom N items in a range.
    • • Top 10% / Bottom 10%: Highlights the top or bottom N percent of items in a range.
    • • Above Average / Below Average: Highlights cells with values above or below the average.

• • Data Bars

     

    • • Definition: Visual bars within cells that represent the value in relation to other values in the range.
    • • Usage: Creates an in-cell bar chart to compare values.

• • Color Scales

     

    • • Definition: Shades cells with different colors based on the value in each cell.
    • • Usage: Uses gradient colors to show value ranges, making it easy to see high, medium, and low values.

• • Icon Sets

     

    • • Definition: Adds icons to cells based on their values.
    • • Types: Includes arrows, traffic lights, stars, and other symbols.
    • • Usage: Provides a quick visual representation of data trends.

• • Custom Conditional Formatting Rules

     

    • • New Rule: Create custom rules using formulas or specific criteria.
    • • Formula-Based Rules: Apply formatting based on the result of a formula (e.g., =A1>10).

• • Managing Conditional Formatting Rules

     

    • • Rule Manager: Accessed via “Manage Rules” to edit, delete, or prioritize multiple rules.
    • • Editing Rules: Modify the conditions or formatting applied by a rule.
    • • Deleting Rules: Remove rules that are no longer needed.
    • • Order of Rules: Change the order in which rules are applied.

• • Practical Examples : Try below activities for practice.

• • Highlighting Sales Performance: Use color scales to show sales performance across different regions.

    Region	Sales Q1	Sales Q2	Sales Q3	Sales Q4	Total Sales
    North	15000	18000	17000	16000	66000
    South	22000	21000	25000	24000	92000
    East	14000	13000	15000	12000	54000
    West	20000	19000	23000	21000	83000
    Central	18000	17000	16000	15000	66000

• • • Deadline Tracking: Highlight past due dates and upcoming deadlines with different colors.

      Task	Assigned To	Due Date	Status
      Task A	John	10-06-2024	Completed
      Task B	Mary	18-06-2024	In Progress
      Task C	Steve	20-06-2024	Not Started
      Task D	Anna	25-06-2024	In Progress
      Task E	Mark	22-06-2024	Not Started
      Task F	Lucy	01-07-2024	Not Started
      Task G	Paul	15-06-2024	Completed
      Task H	Karen	21-06-2024	In Progress

• • • Grade Analysis: Use icon sets to represent different grade ranges (e.g., A, B, C).

      Student Name	Subject	Grade
      Alice	Mathematics	85
      Bob	Science	78
      Charlie	English	92
      David	History	88
      Eva	Geography	74
      Frank	Physics	95
      Grace	Chemistry	81
      Henry	Biology	67
      Irene	Literature	89
      Jack	Economics	73

• • Best Practices

     

    • • Simplicity: Avoid overloading with too many rules; keep it simple and focused.
    • • Clarity: Choose colors and icons that are easily distinguishable.
    • • Relevance: Ensure the formatting adds value and enhances data analysis.