How do you use conditional formatting to highlight information?

Format > Conditional Format > Examples

Excel conditional formatting formula examples. Highlight expiry dates, colour if duplicate, COUNTIF, high or low values, hide or show values. 20+ examples

Highlight Dates 📆

The following examples use conditional formatting formulas to highlight dates in a list.

1 -- Highlight Upcoming Expiry Dates

2 -- Highlight Expired Dates

3 -- Highlight Weekend Dates

4 -- Separate Dates With Top Border Lines

1] Highlight Upcoming Expiry Dates

You can use Excel conditional formatting to highlight payments that are due in the next thirty days. In this example, Due dates are entered in column A.

• If the date is within the next 30 days, the date is formatted with bold blue font.
• In column B, a formula [shown below] calculates the number of days away each date is, based on the current date.

To set up the conditional formatting for upcoming expiry dates, follow these steps

1. Select cells A2:A9
2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
3. Click Use a Formula to Determine Which Cells to Format
4. For the formula in cell B2, use the TODAY function, with the AND function to calculate the number of days in the past or future:
•    =AND[A2-TODAY[]>=0,A2-TODAY[]1
5. Click the Format button.
6. Select a font colour for highlighting - light red fill in the screen shot below.
7. Click OK, click OK

COUNTIF Formula Notes

1] Occurrences: The COUNTIF function counts the occurrences of each row's combined text, starting from row 2, and down to the formula's row. If there is more than one occurence, the row is highlighted.

2] Character Limit: The COUNTIF function only works for 255 characters or fewer. For longer strings use the following formula:

• =COUNT[FIND[$G5,$G$5:$G$11]]>1

3] First Instance: If you only want to highlight the duplicate records, and not the first instance of a duplicated record, use the following conditional formatting formula:

• =COUNTIF[$G$2:$G2,$G2]>1

3] Hide Duplicate Values

Use Excel conditional formatting to hide duplicate headings on a worksheet, to make a list easier to read. This video shows you the steps, and the written instructions are below the video.

Hide Duplicate Values

In a table, each row should have all data entered, to enable sorting and filtering. However, you can use Excel conditional formatting to hide the duplicate values, and make the list easier to read.

In this example, when the table is sorted by Region, the second [and subsequent] occurrences of each region name will have white font colour.

You can see the text if you select the cells.

Follow these steps to hide the duplicates, and you can see the steps in the video above.

1. Select range A2:A5
2. On the Ribbon's Home tab, click the Conditional Formatting button, then click New Rule
3. Click Use a Formula to Determine Which Cells to Format
• NOTE: In Excel 2003, choose Format|Conditional Formatting
• Then, from the first drop-down, choose Formula Is
4. For the formula, enter
   =A2=A1
5. Click the Format button.
6. Select a font colour to match the cell colour.
7. Click OK, click OK

Highlight Numbers in List 🌡

The following examples show different ways to highlight numbers with conditional formatting rule.

1 -- Highlight Top or Bottom Values

2 -- Highlight Lottery Numbers

3 -- Highlight Weather Data

4 -- Show Temperatures With Color Scale

1] Highlight Top or Bottom Values

In these two examples, see how to highlight the:

• Top 3 values in a list
• Bottom X values in a list

Both examples use the same list -- the months of the year, and the quantity sold each month.

This video shows the steps, and the written instructions are below the video.

Top 3 Values

In this example, we'll highlight the 3 highest numbers in the list of monthly sales.

The conditional formatting rule will use the LARGE function, which has 2 arguments:

• the list of numbers to check -- $C$2:$C$13
• the nth largest number to return -- 3

The rule will compare each number in the selected cells [C2:C13], to see if it is greater than or equal to that nth number

• C2>=LARGE[$C$2:$C$13,3]

Follow these steps to apply the conditional formatting:

1. Select the numbers in the Sales column [C2:C13]
2. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
3. Click Use a Formula to Determine Which Cells to Format
4. In the formula box, enter the LARGE formula,
      =C2>=LARGE[$C$2:$C$13,3]
5. Click the Format button.
6. Select formatting options [light green fill], click OK
7. Click OK

Bottom X Values

In this example, we'll highlight the lowest numbers in the list of monthly sales. Instead of typing a specific number in the formula, we'll set up a cell on the worksheet, where that number can be entered. Then, the conditional formatting formula will refer to that cell.

First, set up the number cell:

• In cell E1, type the heading: How Many?
• In cell E2, type the number 2 -- that can be changed at any time later
• Put a border around the 2 cells, centre horizontally, and add yellow fill colour in E2

TIP: Later, you could type a zero, to temporarily remove any highlighting

To highlight the bottom values, the conditional formatting rule will use the SMALL function, which has 2 arguments:

• the list of numbers to check -- $C$2:$C$13
• the nth smallest number to return -- a reference to cell $E$2

The rule will compare each number in the list, to see if it is less than or equal to that nth number

• C2=3
• Click the Format button.
• Select formatting options [yellow fill, in this example], click OK
• Click OK

3] Highlight Weather Data

In this conditional formatting example, temperatures and weather descriptions are highlighted in a weather log. You can download the sample data below.

In the weather log table,

• a Red-White-Blue color scale is used on the Temperature column.
• In the weather column, there are 4 rules set up, to colour the cells based on weather type - Sun, Cloud, Rain or Snow.

In another worksheet, there are 2 named Excel tables - one for weather types, and one for weather descriptions.

There are also 3 named ranges, shown in the screen shot below

The named ranges are used as the source for data validation drop down lists, like this list with weather descriptions.

The named ranges are also used in the INDEX/MATCH formulas in the conditional formatting rules.

This screen shot shows the formula in a worksheet cells, where it was used for testing the formula, before creating the rule.

Note: A blank cell or cells with text, will not be affected by the color scale formatting.

Here are the 5 rules, listed in the Rules Manager.

You can download the Conditional Formatting for Weather Data sample data below.

4] Show Temperatures With a Color Scale

To see the steps for using a color scale on a temperature cell, please watch this short video. The written instructions are below the video.

Show Temperatures With a Color Scale

To show hot temperatures in a red cell, and cold temperatures in a blue cell, you can use Excel's conditional formatting color scale. This feature is available in Excel 2007 and later versions.

1. Enter the temperature in cell B3
2. In cells F6:F25, enter the numbers 140 to -50, in increments of 10
3. In cell G6, enter a formula that will show an empty string if the current temperature is equal to the temperature in that row, or between that temperature, and the one above:

   =IF[$B$3=F6,"",IF[AND[$B$3>F6,$B$3 Conditional Formatting > Highlight Cells Rules > Text that Contains. ... .