Using conditional formatting in Excel

By adding conditional formatting in Excel, you can apply different formatting options to a cell or range of cells that meet specific conditions that you specify. Setting these conditions can help organize your spreadsheet and make it easier to scan. Formatting options you can use include changes to font and background color, 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 or less than a certain value, or finding numbers that are above or below the average. In addition to these predefined options, you can also create your own conditional formatting rules using Excel formulas.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

Contents
  1. Apply multiple conditions in Excel
  2. Find data over 25% and 50% more
  3. Set conditional formatting rules
  4. Check conditional formatting rules
  5. Priority order for conditional formatting
  6. Apply non-conflicting rules
  7. Conditional Formatting vs. Regular Formatting

Apply multiple conditions in Excel

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

In such circumstances, Excel first determines whether the various rules conflict, and if so, it follows its established precedence order to determine which conditional formatting rule to apply to the data.

Find data over 25% and 50% more

The following example applies two custom conditional formatting rules to a range of cells B2 before B5

    • The first line checks if there is more data in it cells A2: A5 then the corresponding value in B2: B5 more than 25%.
    • The second line checks whether the same data is in A2: A5 corresponding value in B2: B5 more than 50%.

As seen in the image above, if any of the above conditions are met, 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 background color of the cell changes to green.
    • If the difference is greater than 50%, the background color of the cell changes to red.

The rules used to complete this task are entered using the dialog box New Formatting Rule † Start entering sample data in cells A1 in C5 as shown in the image above.

In the last part of the tutorial we will add formulas to the cells C2:C4 showing the exact percentage difference between the values ​​in the cells A2: A5 And B2:B5 † this allows us to check the accuracy of the conditional formatting rules.

Set conditional formatting rules

We first apply conditional formatting to find an increase of 25 percent or more.

The function looks like this:

 = (А2-В2)/А2> 25%
  1. highlight cells B2 on the B5 on the sheet.
  2. Press tab “Home” on the adhesive tape
  3. Click the icon Conditional Formatting on the adhesive tape to open the drop-down list.
  4. Select new rule to open the dialog New Formatting Rule
  5. In section Select rule type select the last option: Use a formula to determine which cells to format.
  6. Enter formula marked above in the box below Format values ​​where this formula is true:
  7. Press the button Format to open the dialog box. Go to the Fill tab and choose a color.
  8. Click Okay to close the dialog boxes and return to the worksheet.
  9. Background colour cells B3 And B5 should change to the color you chose.

We are now going to apply conditional formatting to find an increase of 50 percent or more. The formula looks like this:

  1. Repeat the first five steps above.
  2. Enter formula indicated above in the field below Format values ​​where this formula is true:
  3. Press the button Format to open the dialog box. Go to the “Fill” tab and choose a different color from the previous one.
  4. Click Okay to close the dialog boxes and return to the worksheet.

Background colour cells B3 should remain the same, indicating that the percentage difference between the numbers in cells A3 And B3 greater than 25 percent but less than or equal to 50 percent. Background colour cells B5 should change to the new color you chose, indicating a large percentage difference between the numbers in cells A5 And B5 † than 50 percent.

Check conditional formatting rules

To make sure the entered conditional formatting rules are correct, we can enter formulas in the cells C2:C5 which calculates the exact percentage difference between numbers in the series A2: A5 And B2: B5

The formula in cell C2 looks like this:

 = (А2-В2)/А2
  1. Press cell C2 to make it active.
  2. Enter the above formula and press the key Enter on keyboard.
  3. The answer 10% should appear in cell C2 indicating that the number in cell A2 10% more than in cell B2
  4. You may need to change the format to cell C2 to display the answer as a percentage.
  5. Use fill mark to copy the formula from cells C2 in cells C3 in C5
  6. answers for cells C3 on the C5 should be 30%, 25% and 60%.

The answers in these cells show that the conditional formatting rules are correct because the difference between: cells A3 And B3 is greater than 25 percent, and the difference between cells A5 And B5 exceed 50 percent.

cell B4 did not change color because the difference between: cells A4 And B4 is 25%, and our conditional formatting rule indicated that a percentage greater than 25 percent was needed to change the background color.

Priority order for conditional formatting

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

In our example, the rules are conflicting because both use the same formatting option – change the cell color in the background.

In case the second line is true (the difference in value is greater than 50 percent between the two cells), then the first line (the difference in value is greater than 25 percent) is also true.

Because a cell cannot have two different background colors at the same time, Excel needs to know which conditional formatting rule to apply.

Excel’s precedence order specifies 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 line used in this guide is higher in the list and therefore takes precedence over the first line. The resulting background color cells B5 will be green.

New rules are at the top of the list by default; Use the arrow keys to change the order Up and down in the dialog box.

Apply non-conflicting rules

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

If the first conditional formatting rule in our example formats a range of cells B2: B5 with an orange border instead of an orange background color, the two conditional formatting rules will not conflict because either format can be applied without interfering with the other.

Conditional Formatting vs. Regular Formatting

In the event of conflicts between conditional formatting rules and manually applied formatting options, the conditional formatting rule always takes precedence and overrides any manually added formatting options.

Leave a Reply

Your email address will not be published.