Conditional formatting quickly highlights important information in a spreadsheet. But sometimes the built-in formatting rules don’t go quite far enough. Adding your own formula to a conditional formatting rule gives it a power boost to help you do things the built-in rules can’t do.
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:
To create the second rule:
You can copy the following table to a worksheet in Excel – be sure to paste it into cell A1. Then, select cells D2:D11, and create a new conditional formatting rule that uses the formula below.
=COUNTIF($D$2:$D$11,D2)>1
When you create the rule, make sure it applies to cells D2:D11. Set a color format to be applied to cells that match the criteria (that is, there is more than one instance of a city in the D column – Seattle and Spokane).
First | Last | Phone | Text to voice software for mac. City |
---|---|---|---|
Annik | Stahl | 555-1213 | Seattle |
Josh | Barnhill | 555-1214 | Portland |
Colin | Wilcox | 555-1215 | Spokane |
Harry | Miller | 555-1216 | Edmonds |
Jonathan | Foster | 555-1217 | Atlanta |
Erin | Hagens | 555-1218 | Spokane |
Jeff | Phillips | 555-1219 | Charleston |
Gordon | Hee | 555-1220 | Youngstown |
Yossi | Ran | 555-1221 | Seattle |
Anna | Bedecs | 555-1222 | San Francisco |
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.