Can you have multiple conditions in conditional formatting?

@Paula2325 So I assume you are applying this format to a range in column D.  You have a couple problems.  1st, to refer to an entire column you need a ":" in between so it would look like $D:$D.  BUT 2nd and more important you don't want to refer to the entire column (at least I don't think that is what you are trying to do).  conditional formatting looks at the upper left cell of the range you are applying it to and evaluates the formula and then moves cell to cell and adjusts the formula accordingly.  What that means is you only want to write the formula as if you are ONLY interested in the upper left cell of the range.

So if your range is $D:$D (the entire column of D) then D1 is the upper left and the formula you want is:

=AND($D1="",$I1="Community")

Please NOTE that I did NOT use "$" before the row #s, because you want Excel to increment D1 to D2 to D3, etc... as it goes down the column.  

Lastly, to check if the cell is blank you could use ISBLANK($D1) but the problem with that or the above ="" is that sometimes people like to "clear"/"erase" a cell by hitting which really doesn't make it blank but rather =" " (have a space character in it).  If you want to ignore spaces and accept blank looking cells as blank, then you can use TRIM($D1)=""

Hi - we have different types of milk that we test for acidity. High acidity is a problem, so I want conditional formatting to highlight these results. However, sheep milk naturally has higher acidity that other milks so has a different limit. Sheep milk limit is max 0.24, all other milks are max 0.18.

Furthermore we get sheep milk from different customers. I have created a column that checks for "sheep" in the text of the product description and returns "Yes" if true. This future-proofs the system so if we get another sheep milk customer the formatting will still work.

I have created a series of 4 conditional formatting rules that I want to apply in order:
=J6>0.18,  cell fill is orange
=P6="No", no format set and stop if true
=P6="Yes", change cell fill back to white (sheep results from 0.19-0.24 show as normal)

=J6>0.24, change font to red bold.

However the series of formulas seems to be only doing the first rule and not progressing. What am I doing wrong?

Learn how to use multiple conditions and formulas in conditional formatting to apply multiple criteria (AND, OR, LEFT, RIGHT) to a single cell or a range.

Now let’s talk about the logical functions. In this section, we’ll show you how to create effective formulas to build a complex rule. First, we will create a new rule to highlight any cell in the Description column that starts AND ends with the given characters.

How to create multiple conditions in Excel

To highlight cells in line with multiple conditions, follow the steps below:

1. Select the range to apply formatting rules

2. Click Home > Conditional Formatting > New Rule.

3. Choose ‘Use a formula to determine which cells to format‘, and type the formula: =AND(LEFT(F2,1)=” A”, RIGHT(F2,1)=” X”). We want to search cells in the Description column for an ‘F’ and highlight that cell when multiple conditions are true. To do this, we’ll use Excel’s LEFT and RIGHT formulas, with the AND formula, to lookup the A and X values.

4. Click on the Format button and add your preferred style

5. Click OK twice to return to the Conditional Formatting Rules Manager window.

6. Use the formatting to the selected range by clicking ‘Apply‘ and then click Close.

Your spreadsheet will focus on all cells in Description that meet ALL conditions. Excel will reflect every cell in the range selected with the first character = A, AND the last character = X by changing the background color.

Explanation

We use AND at the beginning of the formula to apply multiple conditions at the same time.

Apply the LEFT function when you want to extract a given number of characters starting on the left side of the text. For example, LEFT(“A920X”,1) returns “A.”

Use the RIGHT function to extract characters starting at the right side of the text. The Excel RIGHT function extracts a given number of characters from the end of a string.

For example, RIGHT(“A920X”,” 1) returns “X.” So, this example is just one of the hundreds of different formulas you could enter with the AND function.

Tip: If you want to use multiple rules instead of multiple conditions, you can do it without trouble. Learn how to create a rule that depends on another cell.

Additional resources

• How to use conditional formatting