Conditional Formatting

Conditional Formatting in Excel

What is Conditional Formatting?

Conditional formatting in Excel is a powerful tool that allows you to automatically apply formatting to the specified cells if a condition is met. This facilitates to visually represent the data within cells.

The formatting can be Colours, Icons, and Data Bars, that suits the data being displayed. For e.g., 

    • You can highlight the cells in a column with the colors you choose for the numbers meeting specified criteria.
    • You can highlight the cells in a column with the colors you choose for the text meeting specified criteria.
    • You can use Data Bars to display trends in a column.
    • You can use icons to show trends, variations, or thresholds in your data.

Conditional formatting can really make your data stand out and be easier to analyze.

How to Apply Conditional Formatting

Select the Cells

      • Click and drag to highlight the cells you want to format.

Go to Conditional Formatting

      • Go to the Home tab on the Ribbon.

      • In the Styles group, click on Conditional Formatting.

      • You will find various options
        • Highlight Cells Rules.
        • Top/Bottom Rules.
        • Data Bars.
        • Color Scales.
        • Icon Sets.

Select the Rule Type

      • Choose the rule type that best fits your needs.
      • You can try out various options applying them to your data if you are not sure.
      • Example: To highlight values above 50, 
        • Choose Highlight Cells Rules
        • Choose Greater Than…

Set Your Criteria

      • A dialog box will appear where you can enter the criteria.

Format cells that are GREATER THAN:

      • Type a number (e.g. 50) and choose the formatting (fill color, text color).

      • Click OK to apply.
The data before applying Conditional Formatting and after applying looks as follows:

Managing Conditional Formatting

You can

      • Remove the applied Conditional Formatting, or
      • Change the applied Conditional Formatting Rules.

Removing the Applied Conditional Formatting

If you want to remove the applied Conditional Formatting, you need to clear the rules.

Go back to the Conditional Formatting dropdown, and select Clear Rules.

You can clear rules from the selected cells, or from the entire worksheet, or from the selected table, or from the selected PivotTable.

You can remove the applied Conditional Formatting from Rules Manager dialog also as shown below.

Changing the Applied Conditional Formatting Rules

You can make changes to the applied Conditional Formatting, may be in the criteria, or formatting.

Go back to the Conditional Formatting dropdown, and select Manage Rules.

Conditional Formatting Rules Manager dialog opens.

You can choose to show formatting rules by selection. In the above picture Worksheet is chosen.

You can see all the rules applied to the worksheet and make adjustments as needed.

You can Edit a Rule, Duplicate a Rule and then make changes to it, create a New Rule, or Delete a Rule, by selecting the Rule and selecting the option.

Example:

Imagine you have sales data in column B and you want to highlight any cells where sales are above $500:

    • Select column B.

    • Click on “Conditional Formatting” > “Highlight Cells Rules” > “Greater Than…”.

    • Enter 500 and choose a fill color (e.g., green).

    • Click “OK”.

Your sales data above $500 will now be highlighted in green!

Video

Video on applying Conditional Formatting on Excel Table Columns using Color Scales and Data Bars.