Home > Format > Conditional > Examples Excel Conditional Formatting Examples These Excel conditional formatting formula examples can give you inspiration for your own workbooks. Highlight expiry dates, colour weekend dates, highlight formula cells, and much more!

Conditional Formatting IF Formula

Question: In conditional formatting rules, do you need to use the Excel IF function to compare cells to other cells?

Answer: No, you do NOT need to use IF formulas in the Conditional Formatting rules.

Test Formula - TRUE or FALSE

In the Conditional Formatting, above the formula box, you'll see this label:

• Format values where this formula is true

In the formula box, enter a formula to test something, like the Highlight Expired Dates example, shown below. It checks if a date is less than today's date:

• =B2<TODAY()

Formatting Applied if TRUE

The conditional formatting is only applied to the cells where that test result is TRUE.

• In the screen shot below, the current date is August 23rd.
• Dates earlier than August 23rd are coloured red with conditional formatting.
• Other dates are not formatted

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.

Ex A) Format the Date Font

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()<=30)
5. Click the Format button.
6. Select formatting options (Bold, Blue font, in this example), click OK
7. Click OK to close the dialog box

Ex B) Expiry Date Warning Row

If you use Excel to keep track of licences, or other things with an expiry date, how do you notice which ones are expiring soon?

To make them stand out, use conditional formatting, and highlight the entire table row, for any expiry dates that are within 30 days of the current date.

• Formula in column F calculates how many days until expiry: =D3-TODAY()
• Conditional formatting rule checks the result, and highlights rows if 30 days or less. =$F3<=30
• Summary sheet shows the total count of licences, and the number expiring soon.

2) Highlight Expired Dates

You can use Excel conditional formatting to highlight policies with dates that have expired. In this example, due dates are entered in cells B2:B7.

• If the date is before the current date, the date cell is formatted with red fill colour.
• The conditional formatting formula is shown below the screen shot.

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

1. Select cells B2:B7
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, use the Today function to find expired dates:
      =B2<TODAY()
5. Click the Format button.
6. Select formatting options (Red fill color, in this example), click OK
7. Click OK

3) Highlight Weekend Dates

To highlight the weekend dates in a list, you can use conditional formatting. The WEEKDAY function returns a number for each day of the week, so you could adjust this formula to highlight other days of the week.

You can see the steps in this video, and there are written steps below the video

Highlight Weekend Dates

In this example, the Excel file has a list of product sales for the first half of July. We’d like to highlight the dates and sales numbers that fall on a weekend.

Conditional formatting will colour the cells in columns A, B and C light green, for rows with a weekend date (Saturday or Sunday).

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

• On the Ribbon, click the Home tab, then click Conditional Formatting.
• Click New Rule, to open the New Formatting Rule dialog box
• In the Select a Rule Type list, click Use a formula to determine which cells to format.
• In the Formula box, enter a WEEKDAY formula to check the weekday of the date in row 2, which is the active row on the worksheet:
  • =WEEKDAY($H2,2)>5
  • For details on using WEEKDAY function, go to the Date Functions page
• Click the Format button, and select a Fill colour, or other formatting options, then click OK.

• Click OK to close the New Formatting Rule dialog box

The weekend rows are highlighted in light green fill colour

4) Separate Dates With Lines

If you’re working with a list of tasks or orders, sorted by date, use conditional formatting to separate the dates with a border line.

This makes it easy to see where each date starts, in a long list.

You can see the formatting steps in this video.

Highlight for Cell Content

The following examples show how to highlight cells based on their content.

1 -- Colour Cells Based on 2 Conditions

2 -- Highlight Cells With Formulas

3 -- Highlight Items in a List

4 -- Cross Off Completed Items

1) Colour Cells Based on 2 Conditions

Use Excel conditional formatting to colour cells if 2 conditions are met. In this example, a country code is entered in cell B2. If the code "US" is entered, cells that contain "United States" are coloured red.

Enter the Conditions

You could enter the conditions in the conditional formatting formula, but if you enter them in worksheet cells it's easier to see the conditions, and change them, if necessary.

In this example, the conditions are on the same sheet as the data entry cells, but you can store them on a different sheet. You could also name the cells, and use those names in the conditional formatting formula

To set up the conditions:

• In cell E1, type Cond01
• In cell B2, type US.
• In cell D1, type Cond02
• In cell D2, type United States

Add the Country Code Cell

Next, set up the cell where a country code can be entered:

• In cell B1, type Code
• In cell B2, type US. (This can be changed later)
• Format cell B2 with yellow fill colour, to show that it is a data entry cell

Add Conditional Formatting

Next, add conditional formatting to country cells in the data range. The formula is explained below.

1. Select cells D5:D14, where the country names are listed for the orders
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, enter    =AND($B$2=$E$2,D5=$F$2)
5. Click the Format button.
6. Select formatting options (red fill colour, in this example)
7. 
8. Click OK, click OK

If US is entered in cell B2, and a cell in D5:D14 contains "United States", it is coloured red.

How It Works

The conditional formatting formula is: =AND($B$2=$E$2,D5=$F$2)

The AND function checks the 2 conditions:

1. Does cell B2 match the condition entered in cell E2
2. Does the data entry cell match the condition entered in cell F2

Some notes about the cell references in the formula:

• Cell D5 is used in the formula, because that was the active cell when the conditional formatting was applied.
• A relative reference is used for the data entry cell (D5), because it should adjust to match each cell where the conditional formatting is applied.
• Absolute references are used for $B$2, $E$2 and $F$2 because no matter where the conditional formatting is applied, it should always check those cells.

2) Highlight Cells With Formulas

Use Excel conditional formatting to highlight cells that contain a formula. In this example, there are values in cells A2:B8, and totals in cells C2:C8 and in A9:C9

1. Select all the cells where you want the formatting -- range A2:C9
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, enter: =ISFORMULA(A2)
5. Click the Format button.
6. Select a font colour for highlighting.
7. Click OK, click OK

3) Highlight Items From Criteria List

Use Excel conditional formatting to highlight cells that contain values from a different list on the worksheet, such as a criteria list with valid two-digit codes..

In this screen shot, a list in column C has 3 code: AA, BB and CC. In column A, cells with those codes are changed to green color, thanks to a conditional formatting rule.

1. Create a list of items you want to highlight. If the items are on a different sheet than the conditional formatting, name the list.
2. Select range A2:A7
3. On the Ribbon's Home tab, click Conditional Formatting, then click New Rule
4. 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
5. For the formula, enter
   =COUNTIF($C$2:$C$4,A2)
   or, if the list is named, use the name in the formula:
   =COUNTIF(CodeList,A2)
   
6. Click the Format button.
7. Select a font colour for highlighting.
8. Click OK, click OK

4) Cross Off Completed Items

If you have a list of the tasks that you have to work on, use conditional formatting to cross off completed items. In this example, completed tasks are marked with an X in the "Done" column.

There is a conditional formatting on the list, to cross off completed items, and change the font to light grey. That makes it easier to focus on the tasks that still need to be finished.

To set up this conditional formatting, follow these steps:

1. Select cells A2:C4
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, enter   =$C2<>""
• In each cell, the formula checks if column C in that row is empty
5. Click the Format button, to open the Format Cells dialog box.
6. On the Font tab, choose Strikethrough, and select light grey as the colour



7. Click OK, click OK

After you set up the conditional formatting rule, the item will be crossed off, if you type anything in the "Done" column.

This example is on the Strikethrough sheet in sample file #1.

Get the Excel Files ✅

1 -- Conditional Formatting Examples: Download the sample Excel Conditional Formatting file , with most of the examples from this page. The zipped Excel file is in xlsx format, and does not contain any macros.

2) Expiry Date: This sample file highlights expiry dates that are in 30 days or less. Summary sheet shows total item count, and count of items expiring soon. The zipped Excel file is in xlsx format, and does not contain any macros.

2 --Hidden Data Warning: This sample file shows warnings, if rows or columns are hidden. Formula checks for hidden rows, and conditional formatting marks hidden column. Zipped file does not contain any macros.

3 -- Conditional Formatting for Weather Data: This sample file uses color scale for temperatures, and 4 formula rules for weather conditions - Sun, Cloud, Rain and Snow. The zipped file is in xlsx format, and does not contain macros

4 -- Hidden Questions: Main questions are visible, and conditional formatting hides the follow-up questions. The zipped file is in xlsx format, and does not contain macros

Note: For the Show Selected Color in Cell sample files, go to the Show List and Colors page.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Conditional Formatting Intro

Highlight Duplicates

Alternate Row Colour

Highlight Numbers

Hide/Show Questions

Conditional Format Overview

Color Scales | Icon Sets | Data Bars

Custom Icon Sets

Fix Conditional Formatting Extra Rules

Conditional Formatting Multiple Cells

Conditional Formatting - Currency

Conditional Formatting Documentation