How to avoid a conditional formatting rule in Excel that doesn’t work as expected

Recent articles

Formulaic conditional formatting guidelines in Microsoft Excel can be difficult, so discover how to avoid a typical error.

” data-credit=”Image: milindri, Getty Images/iStock Photo” rel=”noopener noreferrer nofollow”>

Image: milindri, Getty Images/iStock Image.

Microsoft Excel’s conditional formatting is a versatile tool that uses formatting on the fly; as you upgrade the information, the rule updates the formatting appropriately. It’s simple to use however doesn’t constantly work the method you anticipate. Often you do not even see that it isn’t working asexpected

In this post, we’ll take a look at a formulaic rule in Excel that works in one circumstances, however not another. We’ll start with a basic expression that highlights particular worths in a four-column information set. We’ll attempt to use the very same rule to a set of labels, where it will stop working.

Disclosure: TechRepublic might make a commission from a few of the items included on this page. TechRepublic and the author were not made up for this independent review.

I’m utilizing (desktop) Office 365 however you can work with earlier variations. To follow along, download the demonstration.xlsx file. The internet browser will support existing conditional formatting guidelines, however you can’t execute a formulaic rule in the internet browser.

FIND OUT MORE: Office 365 Consumer pricing and features

The conditional formatting rule that works

You have actually most likely dealt with formulaic guidelines in Excel in the past– they’re basic to execute however can be difficult. By using a rule to a information set (essentially a range), you can utilize one rule to format the picked worths. To show, we’ll highlight worths less than or equivalent to the worth 21 in the basic information set revealed in Figure A. (Number 21 and the equality operator are approximate to the example.)

Figure A

  We’ll use a conditional formatting rule to highlight values equal to or less than 21.

” data-credit=”” rel=”noopener noreferrer nofollow”>

We’ll utilize a conditional formatting rule to emphasize worths equivalent to or less than 21.

To begin, pick the information you desire to format in Excel, in this case, that’s C3: F6 and do the following:

  • On the Home tab, click Conditional Formatting (in the Designs group) and pick New Rule.
  • Select the last rule in the leading pane (Usage a Formula).
  • In the second pane, go into the following expression:
    = C3: F6< =21
  • Click the Format tab and pick any format to use. I picked red from the Color dropdown (on the Typeface tab).
  • Click OKAY and see the format and the formula (Figure A) prior to clicking OKAY to see the results revealed in Figure B.
  • SEE: Cheat sheet: Windows 10 (totally free PDF) ( TechRepublic)

    Figure B

      All values in the selected range (C3:F6) equal to or less than the value 21 are red.

    ” data-credit=”” rel=”noopener noreferrer nofollow”>

    All worths in the picked variety (C3: F6) equivalent to or less than the worth 21 are red.

    This is a basic rule in Excel, and you have actually most likely used something comparable to your ownwork It works fantastic as is, however what if you desire to emphasize just the matching names and not the worths? You may presume that the rule would work the very same for the names, however it doesn’t since the conditional referral includes more than one column. Let’s see what occurs.

    SEE: 50 Excel suggestions every user ought to master ( TechRepublic)

    Including a conditional formatting monkey wrench

    When using a conditional format, you pick the variety you desire formatted. Let’s expect rather of the worths, we desire to use the format to the matching names in column B. If we use the very same rule to B3: B6, we see the results revealed in Figure C. A you can see, it doesn’t work as you may anticipate. (I went into the very same expression, C3: F6< =21)

    Figure C

      The expression doesn’t return the results you expected.

    ” data-credit=”” rel=”noopener noreferrer nofollow”>

    The expression doesn’t return the results you expected.

    The most significant problem with this rule is that it is working– however not the method youexpected If you had not currently seen the results in the last area, would you have observed the problem? You may in such a little information set, however in a big one, you may miss it. When a screenshot is more efficient than a description,

    This is one of those times. Figure D shows the very same expression participated in G3 and copied to G6. As you can see, you get an “selection” result– 4 FALSE or true worths for each row. The TRUE worths in column G, match the formatted worths in column B. The rule can’t crunch all those results into one worth for a single-column– it’s a column-to- columnrule Excel uses the TRUE/FALSE results of the first column in the rule variety, column C, to the worths in column B.

    Figure D

      The expression returns a four-column array result.

    ” data-credit=”” rel=”noopener noreferrer nofollow”>

    The expression returns a four-column selection result.

    You might have observed that another problem has been exposed: Why does the blank cell, G3, return TRUE? Look no more than the equality operator,

    I am the Editor for Gaming Ideology. I love to play DOTA and many other games. I love to write about games and make others love gaming as much as I do.

    Leave a Reply