Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Learn How to Use the AND Function of Conditional Formatting

Conditional formatting is used to highlight the cells where a given condition is met. However, if we want to highlight the cells that meet multiple conditions, then conditional formatting with AND function is used.

Figure 1. Final result

AND Function

The AND function is used to evaluate multiple conditions and it returns TRUE when all the conditions are met, otherwise returns FALSE. Therefore, the custom rule of conditional formatting with AND function triggers to highlight selected cells or rows when all the provided conditions are met. We have a dataset of salespersons accounts and their sales amounts. We want to evaluate the following conditions to highlight the rows where salespersons exceed both sales and account goals

  • Sales Amounts in column B are greater than or equal (>=) to the Sales Goal of $10,000
  • AND Accounts in column  C are greater than or equal (>=) to the Accounts Goal of 8

Using conditional formatting with AND function the custom formula rule is created to evaluate both given conditions in the following formula syntax:

=AND(Sales Amount >=Sales Goal, Accounts >=Accounts Goal)

OR

=AND($B2>=$G$2,$C2>=$G$3)

Figure 2. Salespersons’ dataset with conditions to evaluate

Applying Conditional Formatting

We use conditional formatting with AND function to highlight rows where above two conditions are met. To add conditional formatting in this case, follow the following steps:

  • Select the range of cells where salespersons data is entered, such as A2: D10
  • On the Home tab, click Conditional Formatting and then click New Rule
  • Click the option “Use a Formula to Determine Which Cells to Format”
  • In the formula box, enter =AND($B2>=$G$2,$C2>=$G$3)
  • Click the Format button and select the Formatting option, such as Fill > Red color > OK
  • Click OK

Figure 3. Applying Conditional Formatting with AND function

Figure 4. Highlighting rows that meet both conditions

Instant Connection to an Expert through our Excelchat Service:

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have a list or people who are ranked in numbers from 3 to 6 I need to recognize the contents of each multiple cells and create an equivalent letter. for that value into another column. e.g. Cell E1 = 3 to show in new cell that row (H1) the letter "C" I have multiple rows with different values in column "E" Can this be done?
Solved by I. J. in 30 mins
I need a formula where s36 = 1 if a10=s7 and c10=any value?
Solved by G. J. in 13 mins
Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?
Solved by Z. U. in 20 mins
Hello good afternoon, I have a problem with excel. I need a formula to compare data between 3 columns and give me the result in a fourth column
Solved by X. B. in 58 mins
I want to display a range of cells if they meet a specific criteria. I want to display a name IF it does not equal one of three options AND there are any numbers contained in a range of cells
Solved by G. F. in 60 mins

1
Leave a Comment

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Thang Tran Recent comment authors
newest oldest
Thang Tran
Admin
Thang Tran

Hello

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc