How to Use IF in Excel (Formulas and Conditional Formatting)

To color cells with an if-then condition in Excel, create a conditional formatting rule, enter a formula that returns TRUE or FALSE, and choose a fill color. A worksheet IF formula returns a value; the formatting rule supplies the color.

For example, select B2:B7 and use =IF(B2>=80,TRUE,FALSE) to highlight scores of 80 or more. The shorter =B2>=80 works too. Put either formula in the conditional formatting dialog, rather than replacing the scores in your worksheet.

The main steps below apply to desktop Excel for Windows, including Microsoft 365 and Excel 2024. Separate Mac and web directions follow. You need permission to edit the workbook. Try the example in an empty sheet first if your workbook already has formatting rules.

Use an IF Formula to Return a Value

An ordinary worksheet formula uses =IF(logical_test,value_if_true,value_if_false). In an empty sheet, enter 91 in B2, select C2, type =IF(B2>=80,"Pass","Review"), and press Enter. C2 should display Pass. Change B2 to 79 and check that C2 becomes Review; a score of exactly 80 should return Pass. Keep the formula in C2 so it does not overwrite the score in B2. Microsoft’s IF function guide documents the comparison and two return values.

Put text results in quotation marks; numeric results can be numbers or calculations. For example, =IF(B2>=80,1,0) returns 1 or 0. Close the parenthesis, and use the argument separator expected by your Excel locale if a comma causes an error. There is no separate ELSE keyword: the third argument supplies the otherwise result. Returning the word Green does not color a cell. Use the conditional-formatting instructions below when the desired result is a fill color.

Compare Two Excel Columns Row by Row or Highlight Repeats

To compare corresponding rows, leave an empty result column beside your data. For values in A2 and B2, enter =A2=B2 in C2 and press Enter for TRUE or FALSE, or use =IF(A2=B2,"Match","No Match") for labels. For case-sensitive text, use =EXACT(A2,B2); Microsoft’s EXACT reference shows that capitalization and spaces matter, while formatting differences do not. Drag C2’s fill handle down through the intended rows, then inspect the first and last formulas and known matching/different examples. These compare the same row, not whether a value occurs somewhere else in the other column. Check blank cells and number-versus-text inputs before interpreting the results.

For a visual repeat check, select the intended data range, such as A2:B20, and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose Duplicate or Unique and a format, then OK. Microsoft’s conditional-formatting guide covers these controls. A repeated value may occur twice within one column: this rule does not prove a match in both columns or in corresponding rows. It highlights values without deleting them. Check the range and results; to reverse the example, use Manage Rules to remove only its rule, preserving other formatting.

Find Values in Another Column or Calculate Their Difference

To check whether each value in A occurs anywhere in B, use an empty result column. With your first value in A2 and the other list in B2:B20, enter =VLOOKUP(A2,$B$2:$B$20,1,FALSE) in C2 and press Enter. A found value is returned; #N/A indicates no exact match. The column number is 1 because the lookup range has just one column. Keep that range fixed with dollar signs and fill C2 down through your actual records, checking the first and last formulas and a known match and nonmatch. Adjust the range to your data. Our full VLOOKUP guide explains exact matches, data types and lookup errors. This checks membership, rather than comparing only corresponding rows or returning every duplicate.

For a numeric/value result instead, =IF(COUNTIF($B$2:$B$20,A2)>0,A2,0) returns A2 when a match exists and 0 otherwise. Use =IF(COUNTIF($B$2:$B$20,A2)>0,"Match","No Match") if a genuine zero would be ambiguous. Microsoft’s COUNTIF instructions explain that text matching is case-insensitive and recognizes wildcards; review blanks, spaces and mixed number/text values before trusting the result. Enter the chosen formula in an empty cell, press Enter and fill down, keeping the B range locked. Separately, to calculate the difference between numeric values in corresponding rows, enter =A2-B2 in C2, press Enter and fill down; for 10 and 7 the result should be 3. Changing the operator to +, * or / performs addition, multiplication or division instead. These calculations do not test whether a value appears elsewhere in the other column.

Color Cells With an IF Formula in Excel for Windows

Use a small score list to make the result easy to check: enter 79, 80, and 91 in B2, B3, and B4. Leave the header in B1 outside the rule.

1. Select B2:B7, starting at B2.
2. Choose Home > Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter =IF(B2>=80,TRUE,FALSE).
5. Click Format, select the Fill tab, and choose a green background.
6. Click OK in the format dialog, then OK to create the rule.

In this example, 79 does not qualify; 80 and 91 do. Change 79 to 85 and check that its cell becomes green. Change it back to 79 and check that this rule no longer supplies green. Other rules or an existing manual fill may still color that cell.

Microsoft explains the Windows and web rule controls in its conditional formatting guide. For a color you apply by hand, see how to color cells in Excel.

Why IF Does Not Set the Fill Color

The IF function evaluates a condition and returns one of two values. For example, this worksheet formula displays a label:

=IF(B2>=80,"Pass","Review")

With a score of 91, it returns Pass; with 79, it returns Review. Those labels can help readers understand a score without depending on color alone. Likewise, =IF(B2>=80,"Green","Red") returns the words Green or Red. It does not paint the background.

For a formatting rule, use a logical result such as =B2>=80. TRUE activates the format you chose; FALSE leaves that rule inactive. If you want a second color for a different condition, create another rule with its own formula and fill.

Color a Whole Row Based on One Cell

Suppose columns A through D contain a record, and column B holds its score. Select A2:D7 and create a formula rule using:

=$B2>=80

The dollar sign locks the test to column B while the row number changes. Each row is evaluated using its own score, so a qualifying B3 colors A3:D3. Without the dollar sign, the tested column can shift across the row.

Match the formula to the first row of the selected range: a range starting at row 2 needs a row-2 reference. Using =$B3>=80 for A2:D7 tests the following row and can make the colors appear offset.

To keep a threshold in F1 instead of typing 80 into each rule, enter a number in F1 and use =$B2>=$F$1. Both dollar signs keep the threshold fixed. Microsoft describes these distinctions in its guide to relative, absolute, and mixed references.

Use Text or More Than One Condition

To color A2:D7 when column C contains the status Ready, use:

=$C2="Ready"

Put text in quotation marks; do not put quotation marks around the entire formula. For a stricter rule requiring both a numeric score of at least 80 and that status, use:

=AND(ISNUMBER($B2),$B2>=80,$C2="Ready")

Our example requires all three checks. A blank or text score fails the numeric check, while a score of 91 with status Review fails the status check. AND requires every condition to be true; OR can be used when any condition should qualify. Microsoft documents AND and OR in conditional formatting, including why an outer IF is optional.

Excel for Mac and Excel for the Web

On a Mac, select the range and choose Home > Conditional Formatting > New Rule. Set Style to Classic, then choose Use a formula to determine which cells to format. Enter the formula. Under Format with, choose the custom format option, select the fill color, and confirm with OK. These controls are covered in Microsoft’s Mac formula-formatting instructions.

In Excel for the web, select the range, then choose Home > Styles > Conditional Formatting > New Rule. Check Apply to range, choose Formula as the rule type, enter your formula, choose the format, and select Done.

Copy Conditional Formatting With Format Painter

In desktop Excel, select a cell with the intended conditional formatting, choose Home > Format Painter, then drag the paintbrush across the destination cells. Press Esc to end painting. Double-click Format Painter if you deliberately want to apply it to several ranges. Microsoft’s conditional-formatting guide documents this sequence. It copies formatting rather than replacing the destination values, but can also copy other cell formatting.

Select the destination and open Home > Conditional Formatting > Manage Rules. Inspect the copied formula, its Applies to range, rule order and any Stop If True choice. Relative references can shift when copied; adjust them to the intended first destination cell and retain dollar signs where a row or column must stay fixed. Test known qualifying and nonqualifying values without overwriting needed records, then save. Do not assume the rule is correct solely because a color appeared. If undoing the change, remove only the copied rule or use Undo immediately, preserving other rules.

If the Color Is Missing or Applied to the Wrong Cells

Open Home > Conditional Formatting > Manage Rules. Check the range and formula. For the first example, the range should be B2:B7 and the formula should begin with B2. For the whole-row example, use A2:D7 and $B2.

Start the formula with =. Fix errors in the referenced cells: a rule returning an error will not apply its format. If rules conflict, inspect their order and any Stop If True setting. A higher-priority rule can explain an unexpected color.

To undo your example, remove its rule in Manage Rules. Clearing rules from selected cells also removes other rules affecting those cells, so check before using that option. Choosing No Fill alone does not remove a conditional formatting rule.