### Creating Custom Conditional Formatting Rules in Excel

### Conditional Formatting Formulas Overview

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.

Since Excel 2007, 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.

### Applying Multiple 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.

### Example: Finding Data that Exceeds 25% and 50% Increases with Conditional Formatting

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 blue
- if the difference is greater than 50% the cell background color will change to red

The rules used to accomplished this task,

= (A2-B2)/A2 > 25%

= (A2-B2)/A2 > 50%

will be entered using the conditional formatting *New Formatting Rule* dialog box.

### Entering the Tutorial Data

- Enter the data into cells A1 to C5 as seen in the image above

**Note:** Step 3 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.

### Setting the Condtional Formatting Rules

As mentioned, the conditional formatting rules that check for the two conditions will be entered using the conditional formatting *New Formatting Rule* dialog box.

### Setting conditional formatting to find greater than 25% increase

- Highlight cells B2 to B5 in the worksheet
- Click on the
*Home*tab of the ribbon - Click on the
*Conditional Formatting*icon in the ribbon to open the drop down menu - Choose
*New Rule*to open the New Formatting Rule dialog box as seen in the image above - In the top half of the dialog box, click on the last option:
*Use a formula to determine which cells to format* - In the bottom half of the dialog box, click in the
*Format values where this formula is true:*line - Type the formula: = (A2-B2)/A2 > 25% in the space provided
- Click on the
*Format...*button to open the*Format Cells*dialog box - In this dialog box, click on the
*Fill*tab and choose a blue fill color - Click
*OK*twice to close the dialog boxes and return to the worksheet - At this point, the background color of cells B3 and B5 should be blue

### Setting conditional formatting to find greater than 50% increase

- With cells B2 to B5 still selected, repeat steps 1 to 6 above
- Type the formula: = (A2-B2)/A2 > 50% in the space provided
- Click on the
*Format...*button to open the*Format Cells*dialog box - Click on the
*Fill*tab and choose a red fill color - Click
*OK*twice to close the dialog boxes and return to the worksheet - The background color of cell B3 should still be blue indicating that the percent difference between the numbers in cells A3 and B3 is greater than 25% but less than or equal to 50%
- 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%

### Checking Conditional Formatting Rules

### Calculating % Difference

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

- Click on cell C2 to make it the active cell
- Type in the formula =(A2-B2)/A2 and press the Enter key on the keyboard
- The answer 10% should appear in cell C2, indicating that the number in cell A2 is 10% larger than the number in cell B2
- It may be necessary to change the formatting on cell C2 in order to display the answer as a percent
- Use the fill handle to copy the formula from cell C2 to cells C3 to C5
- 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% and the difference between cells A5 and B5 is greater than 50%
- Cell B4 did not change color because the difference between cells A4 and B4 equals 25%, and our conditional formatting rule specified that a percentage greater than 25% was required for the background color to change to blue

### Order of Precedence for Conditional Formatting Rules

### Applying Conflicting Conditional Formatting Rules

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% between two cells - the first rule - the difference in value being greater than 25% is also true.

### Excel's Order of Precedence

Since a cell cannot have both a red and blue 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 (= (A2-B2)/A2 > 50%) 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.

### 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 (= (A2-B2)/A2 > 25%) formatted the range of cells B2:B5 with a blue border instead of a blue 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 blue 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 blue and red respectively.