Excel Conditional Formatting Formulas

Blue and yellow spreadsheet
jasantiso / Getty Images

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. The formatting options are only applied when the selected cells meet these set conditions.

The formatting options that can be applied include font and background color changes, font styles, cell borders, and adding number formatting to data.

Excel has had a number of built-in options for commonly used conditions such as finding numbers that are greater than or less than a certain value or finding numbers that are above or below the average value.

In addition to these pre-set options, it is also possible to create custom conditional formatting rules using Excel formulas to test for user-specified conditions.

01
of 04

Applying Multiple Rules in Excel

Screenshot of Excel showing multiple formatting rules

More than one rule can be applied to the same data to test for different conditions. For example, budget data might have conditions set that apply formatting changes when certain levels, such as 50%, 75%, and 100%, of the total budget is spent.

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 is applied 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 to A5 is greater than the corresponding value in B2 to 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 greater 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 conditional formatting New Formatting Rule dialog box. Begin by entering the sample data into cells A1 to C5 as seen in the image above

In the third step of the tutorial will add formulas to cells C2:C4 that show the exact percentage difference between the values in cells A2:A5 and B2:B5 in order to check the accuracy of the conditional formatting rules.

02
of 04

Setting Conditional Formatting Rules

Screenshot of Excel showing how to create a new rule

As mentioned, the conditional formatting rules that check for the two conditions will be entered using the conditional formatting New Formatting Rule dialog box. Let's begin by setting our custom conditional formatting:

Conditional Formatting to find greater than 25 Percent increase

= (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. From the Style: box, select the Classic option.
  6. In the second drop-down, click on the last option Use a formula to determine which cells to format.
  7. Type the formula provided above into the space provided.
  8. Click on the Format with: drop-down then select the Light Red Fill with Dark Red Text option.
  9. Click OK to close the dialog boxes and return to the worksheet.
  10. At this point, the background color of cells B3 and B5 should be green.
Screenshot of Excel showing rule creation

Conditional Formatting to Find Greater Than 50 Percent Increase

 = (A2-B2)/A2 > 50%

Highlight cells B2 to B5 in the worksheet.

  1. Click on the Home tab of the ribbon.
  2. Click on the Conditional Formatting icon in the ribbon to open the drop-down.
  3. Choose New Rule to open the New Formatting Rule dialog box.
  4. From the Style: box, select the Classic option.
  5. In the second drop-down, click on the last option Use a formula to determine which cells to format.
  6. Type the formula provided above into the space provided.
  7. Click on the Format with: drop-down then select the Light Green Fill with Dark Green Text option.
  8. Click OK to close the dialog boxes and return to the worksheet.

The background color of cell B3 should still be green 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 red indicating that the percent difference between the numbers in cells A5 and B5 is greater than 50 percent.

03
of 04

Checking Conditional Formatting Rules

Screenshot of Excel showing formatting rules

To check 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.

= (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 in order 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 created are correct 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 to green.

04
of 04

Order of Precedence for Conditional Formatting

Screenshot of Excel showing conditional formatting

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

In the example used in this tutorial, the rules conflict since both rules use the same formatting option – that of changing the background cell color.

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

Excel's Order of Precedence

Since a cell cannot have both a red and green background at the same time, Excel needs to know which conditional formatting rule it should apply.

Which rule gets applied is determined by Excel's order of precedence, which states that the rule that is higher in the list in the Conditional Formatting Rules Manager dialog box has precedence.

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 changed to red.

By default, new rules are added to the top of the list and, therefore, have a higher precedence. To change the order of precedence use the Up and Down arrow buttons in the dialog box as identified in the image above.

Senior businesswoman entrepreneur working at laptop in studio office
 Getty Images/Hero Images

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 a green border instead of a green background color, the two conditional formatting rules would not conflict since both formats can be applied without interfering with the other.

As a result, cell B5 would have both a green border and a red background color, since the difference between the numbers in cells A5 and B5 is greater than both 25 and 50 percent.

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.

If a yellow background color was initially applied to cells B2 to B5 in the example, once the conditional formatting rules were added, only cells B2 and B4 would stay yellow. Because the conditional formatting rules entered apply to cells B3 and B5, their background colors would change from yellow to green and red respectively.