HAPPY FRIDAY EXCELLERS. More Formula Fun time….

Today let’s look at a small but mighty powerful formula. AND. We can use it to test multiple conditions ( up to 255 logical ones!).

The Syntax of the formuls is

=AND (logical1, [logical2], …)

Where

logical1 – is The first condition or logical value to evaluate

logical2 – is [optional] The second condition or logical value to evaluate

So let’s use it with conditional formatting to highlight a whole row in a data set, meeting the following conditions

Status of Refund AND over a threshold of $40,000

So, here is an extract of sales data. Including refund data. If we hightlight a row of data that contains a Refund woth over $40,000, it will make the data pop out so we can easily identify those really high value refunds issued.

• We start by selecting the data we want to conditionally format.
• Select to create a new formatting rule.

• In the new formatting rule dialog box select Use a formula to determine which cells to format
• We can then use the AND function to look for the two criteria of Column B containing REFUND and Column E containing a value greater than $40,000
• Let’s build the formula

= AND($B1=”REFUND”,$E1>40000)

Note the $ sign in front of the cell references – that tells Excel to keep the column the same as it explands the formula cross the row. That is how the formatting is applied to the whole row nased on the cell values.

• All that’s left is to choose the formatting if both of the criteria are met
• Hit Ok to apply the formatting

Here is the result. Excellent. My refunds over $40,000 are easily spotted now in the data.

The options for using a formula to conditionally format are huge, so many options!.

If you want to download the Example workbook to try out the formulas then you can do so below

[wpdm_package id=’9815′]

Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

• Create A Micro Chart In Excel
• How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
• Create A Timestamp In Excel With Formulas
• Use TRIM for a speedy data clean up