How to Use Conditional Formatting in Excel

Here’s, a comprehensive walk-through about conditional formatting on data

Image by Author using ideogram

Conditional formatting in Excel allows us to apply formatting rules to cells
or ranges based on specific conditions.

It helps us highlight important

information, visualize data trends, and make our worksheets more visually
appealing.

In this article, we will explore the different types of conditional

formatting, like formatting based on cell values or formulas, working
with icon sets and applying conditional formatting to real-world scenarios
using a database case study.

Table of Content

• Conditional formatting
• Conditional formatting using cell values
• Conditional formatting using formula
• Icon Set
• Formula with multiple conditions

Conditional Formatting

When data needs to be formatted based on certain conditions, we may use
conditional formatting.

It is easy to highlight cells or range of cells,
emphasize unusual values, and visualize data by using data bars, color
scales or icon sets.

Conditional Formatting changes the appearance of a cell
range, based on a condition or criterion.

If the condition is true, the cell

range is formatted based on that condition.

If the conditional is false, the

cell range is not formatted.

Conditional formatting

You can select any of these types of formatting, as per requirement.

• Format all cells by using a two-color scale.
• Format all cells by using a three-color scale.
• Format all cells by using data bars.
• Format all cells by using an icon set.
• Format only cells that contain text, number, or date or time values.
• Format only top or bottom ranked values.
• Format only values that are above or below average.
• Format only unique or duplicate values.
• Compare table columns to determine which cells to format.
• Use a formula to determine which cells to format.
• Clear conditional formats

Conditional Formatting using Cell Values

To use conditional formatting to apply shading based on cell values.

Follow
these given steps.

1. Select the cells to which you want to apply the Conditional Shading.
2. Activate the Sheet ribbon tab.
3. In the Style group, click Conditional Formatting.
4. From the gallery that appears, select Highlight Cell Rules.
5. From the menu, select a condition for the value in the cell or select
   More Rules.
6. Specify a condition and cell shading.
7. Click OK to apply the format.

Conditional Formatting using Formula

To apply conditional formatting based on formulas.

Follow the given steps.

1. Select the cells to which you want to apply the formatting.
2. In the Style ribbon group, click Conditional Formatting.
3. From the menu, select Conditional Formatting Rules Legend to
   open the Formatting Rules Legend dialog box.
4. Click New Rule to open the Add Formatting Rule dialog box.
5. In the Select a Rule Type box, select Use a formula to determine
   which cells to format.
6. In the Format values where this formula is true box, enter a formula
   that evaluates a value to be True or False.
7. Click Format to open the Format Cells dialog box.
8. In the Format Cells dialog box, specify the format in which the cells
   satisfying the condition should appear and click OK.
9. Click OK to close the Add Formatting Rule dialog box.
10. Click OK to close the Formatting Rules Legend dialog box and
    apply the format.

To highlight the records of the person who earns the highest salary, we need
to use the following formula if we have selected the database without the

header.

=$h2=max($H$2:$H$101)

New Formatting Rules

IconSet

In Excel, you have access to more icon sets, including triangles, stars and
boxes.

Get Code Titan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

You can also mix and match icons from different sets and more

easily hide icons from view.

For example, you might choose to show icons
only for high profit values and omit them for middle and lower values.

Formula with Multiple Condition

If we want to highlight only the Sales Department from the East region.

We can
change the formula by following these given steps.

1. Select the range without heading.
2. Click on Home tab.
3. Select Conditional Formatting.
4. Click on New Rule.
5. Type the formula =AND($D2="Sales",$E2="East")
6. Choose the desired format.
7. Click OK to apply and close.

Apply conditional formula based on different sheet’s cell reference

Suppose we want to highlight the records by using the cell reference of
another sheet.

In that case, follow the given steps.

1. Select the entire data without heading.
2. Click on Home Tab > Conditional Formatting > Manage Rule.
3. Click on New Rule.
4. Select the Rule types as Use a formula to determine which cell to
   format.
5. In the Edit Rule description, type the formula as: =$D2=’cross sheet’!$B$
6. Choose the desired format.
7. Click OK to apply and close the dialog box.

Thanks For Reading ☺️

A Message from AI Mind

Thanks for being a part of our community! Before you go:

• 👏 Clap for the story and follow the author 👉
• 📰 View more content in the AI Mind Publication
• 🧠 Improve your AI prompts effortlessly and FREE
• 🧰 Discover Intuitive AI Tools