Skip to content

The Advanced Conditional Formatting in Excel – 2022

Conditional Formatting in MS-Excel

Conditional Formatting in Excel is one of the best features to format or highlight cells (with different colors) that contain data. This feature will help us easily spot certain cells with data based on the condition we have given.

The data in the cells may include the following:

  1. Less than any number
  2. Greater than any number
  3. Equal to any number
  4. Between two numbers
  5. Text in similar numbers
  6. Difference between the two dates
  7. Duplicate values
  8. Top items, whether they are in top percentage or a general format
  9. Above or below average numbers, and
  10. Highlight the values based on higher or lower values using data bars, color scales, icon sets, or applying new rules.

Highlight Cells Rules

Under the Highlight Cells Rules, we have Greater Than, Less Than, Between, Equal To, Text That Contains, A Date Occurring, and Duplicate Values. These are discussed below:

1. Greater Than:

It formats or highlights the cells in a range that contains a greater number value than you enter.

Select the range of cells that you want to highlight or show the higher value.

On the Home tab, in the Styles group, click on Conditional Formatting to expand its drop-down menu.

Now select the Highlight Cells Rules, and then click on the Greater Than… to open a dialogue box.

On the dialogue box, in the Text box, type the greater value that you want to format or highlight.

Then select the color that you want to format with by clicking the drop-down arrow as shown in the picture below:

Conditional Formatting in MS-Excel
Conditional Formatting in Excel
Format greater value
Conditional Formatting in Excel

2. Less Than

Less Than in Conditional Formatting highlights the cells in a range that contain less value than you entered.

Select the range of cells that you want to highlight or show the lesser value of.

On the Home tab, in the Styles group, click on Conditional Formatting to expand its drop-down menu.

Now select the Highlight Cells Rules, and then click on Less Than… to open a dialogue box.

On the dialogue box, in the Text box, type the least value that you want to format or highlight less than that value.

Then select the color that you want to format with by clicking the drop-down arrow as shown in the picture below:

Lastly, click on OK.

3. Between

“Between” in Conditional Formatting Highlights, the cells are in a range between the higher and lower number values that you enter.

Select the range of cells that you want to highlight or show in between the higher and lower values.

On the Home tab, in the Styles group, click on Conditional Formatting to expand its drop-down menu.

Now select the Highlight Cells Rules, and then click on the Between… to open a dialogue box.

On the dialogue box, in the Text box, type the least value that you want to format or highlight between two values.

Then select the color that you want to format with by clicking the drop-down arrow as shown in the picture below:

Lastly, click on OK.

4. Equal To

In conditional formatting, the cells in a range equal to the number you enter are formatted.

Select the range of cells that you want to highlight or show as equal.

On the Home tab, in the Styles group, click on Conditional Formatting to expand its drop-down menu.

Now select the Highlight Cells Rules, and then click on the Equal To… to open a dialogue box.

On the dialogue box, in the Text box, type the least value that you want to format or highlight less than that value.

Then select the color that you want to format with by clicking the drop-down arrow as shown in the picture below:

Lastly, click on OK.

5. Text That Contains

Text That Contains in Conditional Formatting Formats the cells in a range that contain text that you want to show or highlight the unique name.

Select the range of cells that you want to highlight or show the text that contains a unique name.

On the Home tab, in the Styles group, click on Conditional Formatting to expand its drop-down menu.

Select the Highlight Cells Rules and then click on the Text That Contains… to open a dialogue box.

On the dialogue box, in the Text box, type the least value that you want to format or highlight less than that value.

Then select the color that you want to format with by clicking the drop-down arrow as shown in the picture below:

Lastly, click on OK.

6. Date That Occurred

Date That Occurs in Conditional Formatting highlights the cells in a range containing a date list.

Select the list of dates in the range of cells that you want to highlight or show.

On the Home tab, in the Styles group, click on Conditional Formatting to expand its drop-down menu.

Select the Highlight Cells Rules and then click on the Date That Occurs… to open a dialogue box.

On the dialogue box, in the Text box, type the least value that you want to format or highlight less than that value.

Then select the color` that you want to format with by clicking the drop-down arrow as shown in the picture below:

Lastly, click on OK.

7. Duplicate Values:

Duplicate Values in Conditional Formatting Finds and Highlightst the duplicate data that you enter.

Select the range of cells that you want to highlight or show the duplicate values.

On the Home tab, in the Styles group, click on Conditional Formatting to expand its drop-down menu. Select the Highlight Cells Rules, and then click on the Duplicate Values… to open a dialogue box.

On the dialogue box, in the Text box, type the least value that you want to format/highlight less than that value.

Then select the colour that you want to format with by clicking the drop-down arrow as shown in the picture below:

Lastly, click on OK.

Top/Bottom Items

Top 10 Items: By default, it formats/highlights the top 10 cells that contain numbers. But you can increase or decrease numbers of the number of items you want to highlight.

Top 10%: Formats/Highlights the cells in a range that rank in the top 10%. But you can increase or decrease numbers of the number of percentages of the items you want to highlight.

Bottom 10 Items: Formats/Highlights the cells in a range that ranks at the bottom. But you can increase or decrease numbers of the number of percentages of the items you want to highlight.

Bottom 10%: Formats/Highlights the cells in a range that rank in the bottom percentages. But you can increase or decrease numbers of the number of percentages of the items you want to highlight.

Above Average: Formats/Highlights the cells in a range that contain numbers which are above average that you gave.

Below Average: Formats/Highlights the cells in a range that contain numbers which are below average that you gave. 

Data Bars

Fills with either gradient colour or solid colour in a range of cells that contain numbers. The higher value fills with the longer bar similarly, the lower value fills with the shorter bar.

Colour Scales

Gradient colour scales indicate where each cell value falls within that range. For example, the gradient colour gradually increases or decreases for higher or lower values. In some other cases. The gradient colour gradually changes from a higher value to a lower one.

Icon Scales

Select any set of icons that best fit your values in the cells to represent the Higher, lower, or equal number value with arrows, shapes, traffic lights, flags, etc.

What is conditional formatting in MS Excel?

Conditional is one of the best features to format cells with different colours that contain data. This feature will help us easily spot certain cells with data based on the condition we have given.

How can I create advanced conditional formatting rules?

To create advanced rules, select the cells, go to the “Home” tab, click “Conditional Formatting,” and choose “New Rule.” Then, define your custom conditions and formatting options.

What are some examples of advanced conditional formatting?

Examples include color scales, data bars, icon sets, and custom formula-based rules for highlighting data trends or anomalies.

How can I apply advanced formatting to a range of cells at once?

You can apply advanced conditional formatting to multiple cells or a range by selecting them all and then defining your custom rules and formats.

How does advanced conditional formatting help in data analysis?

It visually highlights patterns, trends, and outliers in your data, making it easier to identify insights and draw conclusions from your spreadsheets.