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.
- 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...
No comments: