Conditional Formatting with two criteria using AND function in Excel

Posted on:  04/03/2017
Conditional Formatting with two criteria using AND function in Excel

Conditional formatting is one of my favorite features in Excel. I like it because I’m a visual person. Excel has a lot of predefined rules in Conditional Formatting, but it is very easy to write a formula if the predefined rules don’t have what you need. An Executive MBA student from the University of Georga wanted to know how to highlight the entire row in Excel based on two conditions instead of one condition. Now is the time to use the AND function in Excel.

The exercise I used was to find people in California, abbreviated as CA in our data, that found use through Google Ads.

Steps to use the AND Function in Conditional Formatting:

Select the row below the header row to the end of your data. In the screenshot, I started selecting from A2.

  1. On the Home tab, click Conditional Formatting in the Styles group.
  2. Click New Rule.
  3. Click Use a formula to determine which cells to format.
  4. Click in the formula area and type =AND($E2=”CA”,$F2=”Google Ads”)
  5. Click Format and click the Fill tab.
  6. Pick Yellow or Orange and click OK twice.

Excel file used in this example and video Subtotals – Chris Menard

YouTube video of Conditional Formatting with two criteria

More information about Conditional Formatting

Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and Microsoft Most Valuable Professional (MVP). Chris works as a Senior Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel featuring over 900 technology videos that cover various apps, including Excel, Word, PowerPoint, Zoom, Teams, Coilot, and Outlook. To date, the channel has had over 25 million views. Menard also participates in 2 to 3 public speaking events annually, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories