The data in the cells may include the following:
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:
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.
title: “The Advanced Conditional Formatting In Excel 2022” ShowToc: true date: “2022-11-28” author: “Krystal Pickel”
The data in the cells may include the following:
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:
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.
