The Advanced Conditional Formatting in Excel – 2022

Conditional Formatting in Excel is one of the best features to format or highlight 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.

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, colour 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/ 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/highlight.

Then select the colour 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 Formats/ highlights the cells in a range that contains less value than you entered.

Select the range of cells that you want to highlight or show the lesser 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 Less Than… 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.

3. Between

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

Select the range of cells that you want to highlight or show in between the higher and lower 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 Between… to open a dialogue box.

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

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.

4. Equal To

Equal to in Conditional Formatting Formats the cells in a range equal to A number you enter.

Select the range of cells that you want to highlight or show the equal 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 Equal To… 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.

5. Text That Contains

Text That Contains in Conditional Formatting Formats the cells in a range that contain text that you want to show/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/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.

6. Date That Occurring

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 the list of dates.

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/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.

7. Duplicate Values:

Duplicate Values in Conditional Formatting Finds and highlight 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.

Share