Excel Conditional Formatting Examples

image This week, there were a couple of Excel conditional formatting questions in the blog comments. Ron asked about changing the font colour for the highest, second highest and lowest values. Guido wants to highlight values that aren’t multiples of another cell’s value.

I’ll answer the questions here, so they’re easier to find. Maybe you’ve encountered similar conditional formatting problems, and this will help.

Change Font Colour for Top 3 Values

The first question is from Ron, who wants to change the font colour, instead of the fill colour:

I am trying conditional format a range of three cells where the FONT of the highest value in the range will be in Red follow by Blue and Green. I know it can be done using cell fill but, with a large spread sheet, it will look like dog’s breakfast. Any help would be appreciated. Also can this be done across worksheets (i.e. three different worksheet in the same file)

Ron didn’t say that the three cells were adjacent, in the same row, but I made that assumption in my example, shown below.

CondFormatFont01

To find the highest, second highest, and lowest values for cells C2:E2 with a worksheet formula, you could do the following:

  • To find the highest value, use MAX  =MAX($C2:$E2)
  • To find the second highest value, use LARGE =LARGE($C2:$E2,2)
  • To find the lowest value, use MIN =MIN($C2:$E2)

The column references are absolute, so that all 3 cells in the row will refer to the same range.

You can use the same formulas in conditional formatting, compared to the active cell, and add the font formatting for each formula.

Format the Highest Value

To set up the conditional formatting for the highest value:

  • Select cells C2:E6, with C2 as the active cell
  • On the Excel Ribbon’s Home tab, click Conditional Formatting, and click New Rule
  • In the New Formatting Rule dialog box, under ‘Select a Rule Type’, click ‘Use a formula to determine which cells to format’
  • In the formula box, type the MAX formula:   =C2 = MAX($C2:$E2) The first reference to C2 is relative, so each cell will check its value compared to the MAX in the $C2:$E2 range.
  • Click Format, and on the Font tab, select Red as the font colour, then click OK, twice, to close the dialog boxes.

CondFormatFont02

Now the highest values are highlighted in each row.

CondFormatFont03

Format the Second Highest Value

To set up the conditional formatting for the second highest value:

  • Select cells C2:E6, with C2 as the active cell
  • On the Excel Ribbon’s Home tab, click Conditional Formatting, and click New Rule
  • In the New Formatting Rule dialog box, under ‘Select a Rule Type’, click ‘Use a formula to determine which cells to format’
  • In the formula box, type the LARGE formula:   =C2 = LARGE($C2:$E2,2)
  • Click Format, and on the Font tab, select Blue as the font colour, then click OK, twice, to close the dialog boxes.

Format the Lowest Value

To set up the conditional formatting for the second highest value:

  • Select cells C2:E6, with C2 as the active cell
  • On the Excel Ribbon’s Home tab, click Conditional Formatting, and click New Rule
  • In the New Formatting Rule dialog box, under ‘Select a Rule Type’, click ‘Use a formula to determine which cells to format’
  • In the formula box, type the MIN formula:   =C2 = MIN($C2:$E2)
  • Click Format, and on the Font tab, select Green as the font colour, then click OK, twice, to close the dialog boxes.

Now the highest value in each row is highlighted in red, the second highest is blue, and the lowest is green.

CondFormatFont04

Conditional Formatting From Another Worksheet

To answer the second part of Ron’s question — conditional formatting won’t let you refer to cells on a different worksheet, or in a different workbook. However, you can refer to a workbook level named range that’s on a different worksheet, but that wouldn’t help in the example shown above.

For example, you could highlight all the cells on Sheet1 that are higher than the maximum allowed, if the maximum is on Sheet2, in a range named MaxAmt.

=C2>MaxAmt

And here’s another example of referring to a named range in conditional formatting.

Highlight Cells That Are Not a Multiple of Another Cell

The next conditional formatting question came from Guido:

need conditional formatting based on the multiple of the number oin another cell. How to do that?
For ex: in the formatted cell can only be the multiple of the number in the other cell, otherwise it colours for ex red

To check this on the worksheet, you could use the MOD function: =MOD(D2,C2)

The MOD function returns the remainder after a number is divided by divisor. If the result is zero, then D2 is a multiple of C2.

Highlight the Non-Multiples

Shown below is the sample data for this conditional formatting example.

CondFormatMult01

To add the conditional formatting, and highlight the non-multiples in column D:

  • Select cells D2:D6, with D2 as the active cell
  • On the Excel Ribbon’s Home tab, click Conditional Formatting, and click New Rule
  • In the New Formatting Rule dialog box, under ‘Select a Rule Type’, click ‘Use a formula to determine which cells to format’
  • In the formula box, type the MOD formula:   =MOD(D2,C2) <> 0
  • Click Format, and on the Fill tab, select Red, then click OK, twice, to close the dialog boxes.

Now the non-multiples in column D highlighted in red.

CondFormatMult02

More Excel Conditional Formatting Examples

There are more Excel conditional formatting examples on the Contextures website.

______________

You may also like...

5 Responses

  1. Vickie Wade says:

    Use conditional formatting in column M so that values of “YES” automatically highlight Green, and values of “NO” automatically hightlight Red.

  2. gym.hodgson says:

    Hi,
    I’m pullin’ my hair out… I need to highlight date cells, which just happen to be Mondays, from Monday to sunday ie what we consider to be “a week!” with a green background only while they are part of the current week.
    Using “thisweek” in the cond formatting doesn’t work as the ‘excel’ week starts on a Sunday (yep, that old chestnut)
    Can you please help?!

  3. Dana says:

    I need to use conditional formatting to change the font of text in one cell based on the contents of another cell. Example: If cell C2 has the letters BB in it, then D2 contents will be red.

  4. noraini says:

    u are a savior!! tq!!

  5. john says:

    How do you produce a list of months from today for a given number of months ‘x’ in sequential order.
    ie a calc throws up a number eg 14 so the spreadsheet then automatically prints the current month and the next 14 in date order.
    is this conditional formatting??

Leave a Reply to Dana Cancel reply

Your email address will not be published. Required fields are marked *