top of page


Conditional Formatting provides users with the capability to apply formatting to individual cells or a range of cells based on specific criteria.

There are numerous conditions that can be evaluated to determine the application of formatting, including:

  • Formatting cells based on their values

  • Formatting cells containing specific text

  • Formatting top or bottom-ranked cells within a range

  • Formatting cells based on a formula

Basic use of the Conditional Formatting:

conditional formatting 1.PNG

Formatting cells based on their values:

With Conditional Formatting, we have the ability to format cells based on their values.

For instance, we can specify to Excel that all values exceeding 179 should have their background color changed to green. There are different steps:

  1. First, let’s select the range we’d like to format

  2. Next, click on Conditional Formatting – Highlight Cells Rules – Greater Than

  3. Now, in the value type “179”, and select “Green Fill with Dark Green Text”

Format cells if their text contains a certain text:

Now, let's explore how we can format only the cities that contain the letter "e".

This can be accomplished by navigating to Conditional Formatting, selecting Highlight Cells Rules, and choosing the option Text That Contains.

Formatting top/bottom cells in a range:

Conditional Formatting allows for the efficient identification of top/bottom cells.

To format the top and bottom 10% of students' test scores, follow these steps:

  • select Conditional Formatting

  • Top/Bottom Rules

  • Top 10%

  • Bottom 10%:

Format Cells Based on a Formula:

Undoubtedly, one of the most potent capabilities of Conditional Formatting is the ability to apply any desired formula as a condition.

Now, let's explore how we can highlight all cells that contain more than 7 characters.

To achieve this, we can utilize the LEN function, which calculates the number of characters present in each cell.

THere will be different steps:

  1. Conditional Formatting

  2. Highlight Cells Rules

  3. More Rules

  4. Use a formula to determine which cells to format:

bottom of page