Conditional formatting rules in Excel

Create conditional formatting rules with formula

For example, let’s say a doctors' office wants to track their patients’ birthdays to see whose birthday is coming up and then mark them as having received a Happy Birthday greeting from the office.

In this worksheet, we see the information we want by using conditional formatting, driven by two rules that each contain a formula. The first rule, in column A, formats future birthdays, and the rule in column C formats cells as soon as “Y” is entered, indicating that the birthday greeting has been sent.

To create the first rule:

• Select cells A2 through A7. Do this by dragging from A2 to A7.
• Then, click Home > Conditional Formatting > New Rule.
• In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
• Under Format values where this formula is true, type the formula: =A2>TODAY()
• The formula uses the TODAY function to see if the dates in column A are greater than today (in the future). If so, the cells are formatted.
• Click Format.
• In the Color box, select Red. In the Font Style box, select Bold.
• Click OK until the dialog boxes are closed.
• The formatting is applied to column A.

To create the second rule:

• Select cells C2 through C7.
• Repeat steps 2 through 4 above, and enter this formula: =C2="Y"
• The formula tests to see if the cells in column C contain “Y” (the quotation marks around the Y tell Excel that this is text). If so, the cells are formatted.
• In the Color box, select White. In the Font Style box, select Bold.
• Click the Fill tab and select Green.
• The formatting is applied to column C.

loading...