How to Use Conditional Formatting in Excel

Adding conditional formatting in Excel allows you to apply different formatting options to a cell, or range of cells, that meet specific conditions that you set. Setting such conditions can help organize your spreadsheet and make it easier to scan. The formatting options you can use include font and background color changes, font styles, cell borders, and adding number formatting to data.

Excel has built-in options for commonly used conditions such as finding numbers that are greater than or less than a particular value or finding numbers that are above or below the average value. In addition to these pre-set options, you can also create custom conditional formatting rules using Excel formulas.

Applying Multiple Conditions in Excel

You can apply more than one rule to the same data to test for different conditions. For example, budget data might have conditions set that apply formatting changes when certain levels of spending are reached, such as 50%, 75%, and 100%, of the total budget.

In such circumstances, Excel first determines if the various rules conflict, and, if so, the program follows a set order of precedence to determine which conditional formatting rule to apply to the data.

Finding Data that Exceeds 25% and 50% Increases

In the following example, two custom conditional formatting rules will be applied to the range of cells B2 to B5.

• The first rule checks to see if the data in cells A2:A5 is greater than the corresponding value in B2:B5 by more than 25%.
• The second rule checks to see if the same data in A2:A5 exceeds the corresponding value in B2:B5 by more than 50%.

As can be seen in the image above, if either of the above conditions is true, the background color of the cell or cells in the range B1:B4 will change.

• For data where the difference is more than 25%, the cell background color will change to green.
• If the difference is greater than 50%, the cell background color will change to red.

The rules used to accomplished this task will be entered using the New Formatting Rule dialog box. Begin by inputting the sample data into cells A1 to C5 as seen in the image above.

Setting Conditional Formatting Rules

First, we'll apply conditional formatting to find a 25 percent or more significant increase.

The function will look like this:

=(A2-B2)/A2>25%

1. Highlight cells B2 to B5 in the worksheet.

2. Click on the Home tab of the ribbon.

3. Click on the Conditional Formatting icon in the ribbon to open the drop-down.

4. Choose New Rule to open the New Formatting Rule dialog box.

5. Under Select a Rule Type, click the last option: Use a formula to determine which cells to format.

6. Type the formula noted above into the space below Format values where this formula is true:

7. Click the Format button to open the dialog box. Click the Fill tab and choose a color.

8. Click OK to close the dialog boxes and return to the worksheet.

9. The background color of cells B3 and B5 should change to the color you selected.

Now, we'll apply conditional formatting to find a 50 percent or greater increase. The formula will look like this:

1. Repeat the first five steps above.

2. Type the formula provided above in the space below Format values where this formula is true:

3. Click the Format button to open the dialog box. Click the Fill tab and choose a different color than you did in the previous set of steps.

4. Click OK to close the dialog boxes and return to the worksheet.

The background color of cell B3 should stay the same indicating that the percent difference between the numbers in cells A3 and B3 is greater than 25 percent but less than or equal to 50 percent. The background color of cell B5 should change to the new color you selected indicating that the percent difference between the numbers in cells A5 and B5 is greater than 50 percent.

Checking Conditional Formatting Rules

To verify that the conditional formatting rules entered are correct, we can enter formulas into cells C2:C5 that will calculate the exact percent difference between the numbers in the ranges A2:A5 and B2:B5.

The formula in cell C2 looks like this:

=(A2-B2)/A2

1. Click on cell C2 to make it the active cell.

2. Type the above formula and press the Enter key on the keyboard.

3. The answer 10% should appear in cell C2, indicating that the number in cell A2 is 10% larger than the number in cell B2.

4. It may be necessary to change the formatting on cell C2 to display the answer as a percent.

5. Use the fill handle to copy the formula from cell C2 to cells C3 to C5.

6. The answers for cells C3 to C5 should be 30%, 25%, and 60%.

The answers in these cells show that the conditional formatting rules are accurate since the difference between cells A3 and B3 is greater than 25 percent, and the difference between cells A5 and B5 is greater than 50 percent.

Cell B4 did not change color because the difference between cells A4 and B4 equals 25 percent, and our conditional formatting rule specified that a percentage greater than 25 percent was required for the background color to change.

Order of Precedence for Conditional Formatting

When you apply multiple rules to the same range of data, Excel first determines if the rules conflict. Conflicting rules are those where the formatting options cannot both be applied to the same data.

In our example, the rules conflict since both use the same formatting option — changing the background cell color.

In the situation where the second rule is true (the difference in value is more than 50 percent between two cells) then the first rule (the difference in value being greater than 25 percent) is also true.

Since a cell cannot have both two different color backgrounds at the same time, Excel needs to know which conditional formatting rule it should apply.

Excel's order of precedence states that the rule that is higher in the list in the Conditional Formatting Rules Manager dialog box is applied first.

As shown in the image above, the second rule used in this tutorial is higher in the list and, therefore, has precedence over the first rule. As a result, the background color of cell B5 is green.

By default, new rules go to the top of the list; to change the order, use the Up and Down arrow buttons in the dialog box.

Applying Non-Conflicting Rules

If two or more conditional formatting rules do not conflict, both are applied when the condition each rule is testing becomes true.

If the first conditional formatting rule in our example formatted the range of cells B2:B5 with an orange border instead of an orange background color, the two conditional formatting rules would not conflict since both formats can be applied without interfering with the other.

Conditional Formatting vs. Regular Formatting

In the case of conflicts between conditional formatting rules and manually applied formatting options, conditional formatting rule always takes precedence and will be applied instead of any manually added formatting options.