Conditional Formatting in MS-Excel 2019 | New & Advanced

Conditional Formatting In MS-Excel

Conditional Formatting in MS-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 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 general format, Above or below average numbers, and Highlight the values based on higher or lower values using data bars, color scales, icon sets, or applying new 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 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 MS-Excel
Format greater value
Conditional Formatting in MS-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.

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

6. Date That Occurring

Date That Occurring in Conditional Formatting highlights the cells in a range that contain 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 MS-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. 

Data Bars | Conditional Formatting in MS-Excel

Fills with either Gradient color or Solid color 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 | Conditional Formatting in MS-Excel

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

Icon Scales | Conditional Formatting in MS-Excel

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

Related Posts: