# Create conditional formulas to find data or apply formatting

When Excel's built-in conditional formatting rules don’t meet your needs, you can use a formula to get the results you want. For example, you can use a conditional formula to find blank cells and turn them red so you can see them easily.

- Select the cells you want to format.
- Click
**Home**>**Conditional Formatting**>**New Rule**. - Click
**Use a formula to determine which cells to format**. - Under
**Format values where this formula is true**, enter your formula. - Click
**Format**. - Use the controls on the
**Number**,**Font**,**Border**, and**Fill**tabs to change your data, or the cells around your data.

For example, you can change negative values to bold and red, add a light yellow fill to cell backgrounds, or add a dashed border.

- Click
**OK**to close all open dialog boxes.

Here's an example:

- Select all the cells in a worksheet. To do that, click the selector above row 1 and to the left of column A.
- Repeat steps 1 through 3 in the steps above.
- When you get to step 4, enter
**=MOD(ROW(),2)=1****.****You can copy and paste the formula if you want to.** - Click
**Format**, then click the**Fill**tab and select a shade of blue from the color palette. **Click****OK****to complete the rule, and****now every other row****in your worksheet is shaded in the color you chose.**

That's a little of what conditional formulas can do in a formatting rule.

## Examples of conditional formulas

**To****find blank cells**, first select the range of cells (a row or column) that will contain your results, then repeat the steps in the first section to create a formatting rule that uses this formula.

=B2=""

In your formula, remember you'd substitute B2 with the first cell you want to use.

**To find duplicate values in a range of cells**, try this one. It applies formatting to all values that aren't unique.

=COUNTIF($A$1:$D$11,D2)>1

**To calculate averages**, enter:

=A1>AVERAGE(A1:A14)

Once you're comfortable with conditional formulas, give these a try. They use IF, AND, and OR logic. You enter them in Excel's formula bar (you don't use them in a rule), but they can save you time and effort.

**Find values that meet two conditions****:**Excel displays TRUE if the value in cell A2 is greater than the value in A3 and it's also less than the value in A4. But if the value in A2 doesn't meet both conditions, you see FALSE instead.

**=AND(A2>A3,A2<A4)**

**Find values that meet one condition****:**In this one, Excel only displays TRUE if the value in A2 meets one of the conditions—it's greater than the value in A3 or less than the value in A4.

**=OR(A2>A3,A2<A4)**

**U****se formulas that don't display TRUE or FALSE as a result****:**This example displays "OK" if the value in A2 doesn't equal the values in A3 and A4. Otherwise, it displays "NOT OK".

**=IF(AND(A2<>A3,A2<>A4),"OK","NOT OK")** displays.

**A****dd letter grades****based on****a****numeric score****:**This formula adds letter grades base on any type of score, such as test results or product ratings.

=IF(D2>=80,"A", IF(D2>=75, "B+", IF(D2>=70, "B", IF(D2>=70,"B", IF(D2>=65,"C+", IF(D2>=60, "C","D"))))))

**R****eplace the letter grades with****"Pass" and "Fail"****:**

=IF(D2>59,"Pass","Fail")

## Give it a try

Use the sample data here to create a conditional formatting rule and apply it to a column of cells.

This Excel Online workbook shows you the formatted cells, but to see the formula that's used, open the workbook in Excel. See the text below this workbook to find out how to download it to your computer.

Download this workbook by clicking the green Excel icon in the lower-right corner. Save it to your computer, and then open it in Excel. On the **Blanks** worksheet, select B2:B13 and, on the **Home** tab, click **Conditional Formatting** > **Manage Rules** > **Edit Rule****.** Click **Use a formula to determine which cells to format** to see the formula.

## More about conditional formatting

Learn more about conditional formatting.

- Apply conditional formatting based on text in a cell
- Apply conditional formatting to find duplicate values
- Copy conditional formatting
- Remove conditional formatting
- Use a formula in a conditional formatting rule
- Sum values based on one or more conditions
- What happened to the Conditional Sum Wizard add-in?