Learn how to use multiple conditions in conditional formatting to apply multiple criteria (AND, OR, LEFT, RIGHT) to a single cell or a range.
How to apply multiple conditions in Excel?
Now, let’s talk about the logical functions of conditional formatting. This section will show you how to create effective formulas to build a complex rule. First, we will create a new rule highlighting any cell in the Description column that starts AND ends with the given characters.
To highlight cells in line with multiple conditions, follow the steps below:
#1. Select the range
The first step is to select the range to apply formatting rules:
#2. Create a new rule
Click Home > Conditional Formatting > New Rule.
#3. Use a formula
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. We’ll use Excel’s LEFT and RIGHT formulas, with the AND formula, to lookup the A and X values.
#4. Choose the formatting style
Click on the Format button and add your preferred style.
Click OK twice to return to the Conditional Formatting Rules Manager window.
#5. Apply formatting for the selected range
Use formatting for the selected range by clicking ‘Apply‘ and then Close.
Your spreadsheet will focus on all cells in the Description that meet ALL conditions. By changing the background color, Excel will reflect every cell in the range selected with the first character = A and the last character = X.
Explanation
We use AND at the beginning of the formula to apply multiple conditions simultaneously.
- Apply the LEFT function when you want to extract a 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.