Conditional Formatting in Excel

 

 

Conditional formatting in Excel allows you to automatically format cells based on specific criteria. It provides ways to show your data visually appealing and easier to interpret. Using conditional formatting can enhance your workflow when data representing.

This tutorial will cover the basics and some advanced concepts in Conditional Formatting. List below are the sub topics that cover throughout this post.

• What is Conditional Formatting?
• How to Apply Conditional Formatting
• Basic Examples of Conditional Formatting
• Advanced Examples of Conditional Formatting

What is Conditional Formatting?

In Conditional formatting cells will be automatically formatted based on the cell value or a formula. Some formatting that can be applied are,

• Changing the color of cells.
• Adding data bars.
• Highlighting specific rules.

For example, you can highlight sales that exceed a target or mark overdue tasks with red.

How to Apply Conditional Formatting

Let's see how to apply conditional formatting to a worksheet.

• Select the cells you want to format. It can be a single cell, a range of cells, or an entire column.
• Go to the “Home” tab on the Excel ribbon.
• In the Styles group, click on Conditional Formatting.
• Choose the type of rule or create a custom rule.
• Excel offers several built-in options like:
• Highlight Cell Rules (greater than, less than, etc.)
• Top/Bottom Rules (top 10 items, bottom 10%, etc.)
• Data Bars, Color Scales, and Icon Sets

Above mentioned are the common steps to perform a conditional formatting. Now let's see some specific examples to understand how to perform conditional formatting.

Basic Examples of Conditional Formatting

Highlight Cell Rules: "Greater Than"

Example: Highlight cells that contain values greater than 1000.

1. Select the cells you want to format (e.g., A1:A10).

2. Go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than.

3. In the dialog box, enter 1000 and choose a color for the highlight (e.g., Green Fill with Dark Green Text).

4. Click OK.

Result: Any cell with a value greater than 1000 will now have a green background and dark green text.

Highlight Cell Rules: "Text that Contains"

Example: Highlight cells that contain the word “Urgent”.

1. Select the range (e.g., B1:B10).

2. Go to Home > Conditional Formatting > Highlight Cell Rules > Text that Contains.

3. Type “Urgent” in the box and choose a color (e.g., Red Text).

4. Click OK.

Result: Any cell containing the word "Urgent" will have red text.

Top/Bottom Rules: "Top 10%"

 

Example: Highlight the top 10% of values in a dataset.

 

1. Select the data (e.g., C1:C20).

2. Go to Home > Conditional Formatting > Top/Bottom Rules > Top 10%.

3. Choose a color (e.g., Yellow Fill with Dark Yellow Text) and click OK.

Result: The top 10% of values in the selected range will be highlighted in yellow.

Advanced Examples of Conditional Formatting

Color Scales: Creating Heat Maps

Visualize sales performance using a color scale, where lower values are shaded red and higher values are shaded green.

1. Select the range of data (e.g., D1:D20).

2. Go to Home > Conditional Formatting > Color Scales.

3. Choose the green-yellow-red scale.

Result: Cells with lower values will appear in shades of red, and higher values will be green, creating a visual heat map of your data.

Data Bars: Visualizing Data with Bars

Example: Add data bars to show the magnitude of values in a list.

1. Select the range of data (e.g., E1:E20).

2. Go to Home > Conditional Formatting > Data Bars.

3. Choose a color for the bars (e.g., Blue).

Result: The cells will now have horizontal bars, where the length of the bar corresponds to the value in the cell.

Icon Sets: Using Symbols to Classify Data

Example: Categorize exam scores into three groups (pass, warning, fail) using icons.

1. Select the data (e.g., F1:F20).

2. Go to Home > Conditional Formatting > Icon Sets.

3. Choose a three-arrow icon set.

4. Excel will automatically classify the data:

A green up arrow for high values.

A yellow sideways arrow for middle values.

A red down arrow for low values.

 

Result: Cells will display an icon based on their value, making it easier to quickly see which students passed or failed.

Creating Custom Conditional Formatting Rules

You can create custom rules if the built-in options don’t meet your needs. This is done by using formulas.

Custom Rule Example: Highlight Even Numbers

Example: Highlight even numbers in a list.

1. Select the cells (e.g., A1:A20).

2. Go to Home > Conditional Formatting > New Rule > Use a Formula to Determine Which Cells to Format.

3. Enter the formula =MOD(A1:A20,2)=0.

4. Choose a fill color (e.g., Yellow) and click OK.

Result: All even numbers will be highlighted in Yellow.