Conditional Formatting in MS-Excel

The Conditional Formatting in Excel | Advanced | 2022’s New

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 Less than any number, Greater than any number, Equal to any number, Between two numbers, Text in similar numbers, Different between two dates, Duplicate values, Top items whether they are in top percentage or in a general format, Above or below average numbers, and 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 contain 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 the 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 color that you want to format with by clicking the drop-down arrow as shown in the picture below:
  • Lastly, click on
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 contain less number 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 the 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 color that you want to format with by clicking the drop-down arrow as shown in the picture below:
  • Lastly, click on OK.
See also  Ways to Apply Accounting Number Format in Excel - 2022

3. Between

Between in Conditional Formatting Highlights the cells in a range in 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 the value.
  • On the Home tab, in the Styles group, click on the 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 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

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 the 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 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/highlight the unique name.

  • Select the range of cells that you want to highlight or show the text that contain unique name.
  • On the Home tab, in the Styles group, click on the Conditional Formatting to expand its drop-down menu.
  • Now select the Highlight Cells Rules, and then click on the Text That Contain… 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 color that you want to format with by clicking the drop-down arrow as shown in the picture below:
  • Lastly, click on OK.
See also  How to Add Zeros Before a Number in Excel - 2022's Best

6. Date That Occurring

Date That Occurring in Conditional Formatting highlights the cells in a range that contain a list of dates.

  • Select the list of dates in range of cells that you want to highlight or show the list of dates.
  • On the Home tab, in the Styles group, click on the Conditional Formatting to expand its drop-down menu.
  • Now select the Highlight Cells Rules, and then click on the Date That Occurring… 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 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 highlights 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 the Conditional Formatting to expand its drop-down menu.Now 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 color 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 | Conditional Formatting in Excel

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.

  1. 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.
  2. Bottom 10 Items: Formats/Highlights the cells in a range that rank in the bottom. But you can increase or decrease numbers of the number of percentages of the items you want to highlight.
  3. 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.
  4. Above Average: Formats/Highlights the cells in a range that contain numbers which are above average that you gave.
  5. Below Average: Formats/Highlights the cells in a range that contain numbers which are below average that you gave. 
See also  How to Split Text to Columns in Excel - 2022

Data Bars

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

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

Related Posts:

FAQ:

What is conditional formatting in MS Excel?

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 Less than any number, Greater than any number, Equal to any number, Between two numbers, Text in similar numbers, Different between two dates, Duplicate values, Top items whether they are in top percentage or in a general format, Above or below average numbers, and Highlight the values based on higher or lower values using data bars, colour scales, icon sets, or applying new rules.