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.

  1. Select the cells you want to format.
  2. Click Home > Conditional Formatting > New Rule.
  3. Click Use a formula to determine which cells to format.
  4. Under Format values where this formula is true, enter your formula.
  5. Click Format.
  6. 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.

  1. Click OK to close all open dialog boxes.

Here's an example:

  1. Select all the cells in a worksheet. To do that, click the selector above row 1 and to the left of column A.
  2. Repeat steps 1 through 3 in the steps above.
  3. When you get to step 4, enter =MOD(ROW(),2)=1. You can copy and paste the formula if you want to.
  4. Click Format, then click the Fill tab and select a shade of blue from the color palette.
  5. 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)

  • Use 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.

  • Add 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"))))))

  • Replace 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.

 
 
Applies to:
Excel 2013