Introduction to Conditional Formatting (Part 2)

Welcome to part two of this two-part series of blog posts introducing Excel Conditional Formatting. If you haven’t read part one in this series, then I suggest you read it first by clicking here.

In this second blog post, we’ll look at data bars, color scales, custom rules, and how to manage rules.

Data Bars

Let’s start with data bars. Data bars allow you to represent data in a graphical manner that looks similar to a bar chart. For instance, imagine you have some monthly sales figures and want to represent them graphically. Doing so might allow you to spot sales trends throughout the year.

In the example below, we have sales figures covering a full year. In this example, it’s easy to see which are the best and worst-performing months.

Conditional Formatting Data Bars

To implement this type of conditional formatting, select the cells with the data to be formatted, in this case, B2:B13. As shown in the image below, click the Conditional Formatting button on the Home tab on the ribbon and then select Data Bars. Once you’ve clicked Data Bars, you will have a few different formatting choices to choose from. Choose one, and you’re done.

Conditional Formatting Data Bars

The image above is from Excel for Mac 2016. However, the Windows version is very similar. You’ll notice from the image above that you can select between different colors and whether the data bars are gradient or solid fill. If you click the “More Rules” button, you will be presented with more fine-grained formatting options.

Color Scale

The color scale conditional formatting option formats the cell’s background color based on its value. Using the data used for the data bars example above, the background color of the cells in this example gradually changes, turning darker shades of green the higher the value.

Conditional Formatting Color Scale

As before, select the cells with the data you want to format, B2:B13. Again, click the “Conditional Formatting” button on the Home Tab on the Ribbon. However, this time, select “Color Scale”. As you did with the data bars, once you’ve clicked “Color Scale”, you will have a few different formatting choices from which to choose. Choose one, and you’ll be done.

Selecting Color Bars

Managing Rules

Once you get used to using conditional formatting, you might start to find that you have several rules on the same worksheet, even several rules acting on the same cells. That’s OK; Excel provides a menu for managing conditional formatting rules. Click the Conditional Formatting button on the Home tab on the ribbon and then click Manage Rules, and you’ll see the pop-up menu in the image below.

From here, you can create new rules, edit and delete existing rules, and change the order of precedence.

Conditional Formatting Manage Rules Menu - Mac

On the Mac, click the ‘+’ sign to create a new rule, the ‘-‘ sign to delete a rule, and the Edit Rule button to edit a rule. To change the order of precedence, highlight a rule and then click the up and down arrows in the top right-hand corner to change the rule order of precedence.

Conditional Formatting Manage Rules Menu - Windows

Conditional Formatting Manage Rules Menu – Windows

On Windows, click the New Rule, Edit Rule, and Delete Rule buttons near the top of the Manage Rules pop-up menu. To change the order of precedence, highlight a rule and then click the up and down arrows next to the Delete button to change the rule order of precedence.

Custom Rules

Should you find that the built-in conditional formatting rules don’t meet your needs, then you can create custom rules.

Start by clicking the New Rule button. The New Rule button can either be accessed by opening the Manage Rules menu as described above or by clicking the Conditional Formatting button on the ribbon and then clicking the New Rule option as highlighted in the image below.

Conditional Formatting - New Rule

Once you’ve done this, you’ll be presented with the ‘New Formatting Rule’ menu. The New Formatting Rule menu that pops up differs slightly between the 2016 editions of Excel for Windows and Mac. However, the functionality you are trying to access works the same. For Windows, the New Formatting Rule menu looks like the image below.

New Formatting Rule - Windows

In the Windows version of the New Formatting Rule menu, you can select many of the built-in rules we have already discussed in this post by selecting ‘Format all cells based on their value’ as highlighted in the image above. However, the New Formatting Rule Menu on the Mac looks like the image below.

New Formatting Rule - Mac

In the Mac version of the New Formatting Rule menu, you can select many of the built-in rules we have already discussed in this post by selecting the drop-down menu highlighted in the image above. However, what we want to do is select ‘Classic’ from the drop-down menu to create some custom rules.

Example 1 – Format Only Cells That Contain – Specific Text

OK, so now that we know which menu to select, let’s have a look at a couple of examples. In this first example, we are going to highlight cells that contain the name ‘Richard’ from a range of cells that contain various names. Firstly, select the range that you are going to apply the Conditional Formatting to, in this case, A1:C10 as highlighted in yellow in the image below.

Conditional Formatting Example 1 Range Selection

Then, select “Format only cells that contain” from the New Formatting Rule menu. Again, the Windows and Mac menus look a little different.

The Image below is the Windows New Formatting Rule menu when you select the “Format only cells that contain” option.

Format only Cells that Contain - Windows

On the Mac, you have to click the drop-down menu as highlighted in the image below and select the “Format only cells that contain” option.

Format only Cells that Contain - Mac

From this point forward, Windows and Mac work in a similar enough manner. When you select the leftmost drop-down menu highlighted in the image below, you are presented with several choices such as “Cell value,” “Specific text,” “Date occurring,” and so forth. This drop-down menu defines the type of rule you are going to create. Go ahead and select the option “Specific text” as highlighted in the image below.

Conditional Formatting - Specific text

Once you’ve selected “Specific text” from the first drop-down menu, you need to select the criteria you are going to apply to this new rule. To do this, select the second drop-down menu, which provides various criteria you can apply, such as “containing,” “not containing,” and so on. For our example, select the option ‘containing’ as shown in the image below.

Conditional Formatting - Specific text containing

Finally, in the text box next to the second drop-down menu, type the name ‘Richard’ as shown in the image below. This tells the rule what text to apply the formatting to. This could have just as easily been another name. When this is done, you can select the type of formatting you’d like to apply by selecting the ‘Format with’ drop-down menu.

Conditional Formatting Specific text containing Richard

However, for now, we’ll stick with the default red text on a pale red background. Once you click OK, the range of cells containing the names should look something like the image below.

Example 1 Conditional Formatting Complete

From this example, you can see that there is a multitude of different ways to format cells that contain certain values using the two drop-down menus. Exploring all these different options is beyond the scope of this blog post, but this example should give you a taste of what can be achieved.

Example 2

In this second example, we are going to use a formula to determine which cells to format. Using formulas in Conditional Formatting is extremely powerful. It allows you to create some highly customized conditional formatting rules. If you’re not familiar with formulas, then I suggest that you read my series of blog posts introducing formulas by clicking here.

For the second example, we are going to use the same data set as we did in the first example. Only, this time, we are going to format all cells that contain the names ‘Richard’ and ‘Nicky’. So, go ahead; select the range that you are going to apply the conditional formatting to; again, it is A1:C10, as highlighted in yellow in the image below.

Conditional Formatting Example 1 Range Selection

We’ve already seen how to create a new rule by using the New Formatting Rule menu. This time, select the option ‘Use a formula to determine which cells to format’. Windows and Mac screen shots are below.

Windows Screen Shot

Use a formula to determine which cells to format - Windows

Mac Screen Shot

Use a formula to determine which cells to format - Mac

From this point forward, Windows and Mac work in a similar enough manner.

To achieve the result we want, we are going to use the ‘OR’ function. The OR function allows you to test two or more criteria. If either of the criteria is correct, then the OR function will return ‘true’. What does that mean for our example? Well, if a cell contains either the name ‘Richard’ or ‘Nicky’, the function will return ‘true’. If the function returns true, then the formatting is applied.

If you would like to know more about the OR function, please click here.

Okay, back to Example Two. Enter the formula ‘=OR(A1=”Richard”,A1=”Nicky”)’ into the text box on the New Formatting Rule menu highlighted in the image below.

Enter Formula in Conditional Formatting

When you click OK, the range of cells containing the names should look something like the image below. The cells containing either the names Richard or Nicky should be formatted with red text on a pale red background.

Conditional Formatting Formula text containing Richard or Nicky

Hopefully, you will have found this blog post useful. We have only just touched on the level of customization you can apply to conditional formatting, especially when using formulas. The best thing to do now is to have a go. If you have any questions or comments, please leave them in the comments section below.

If you liked this post, please share it with your friends on Facebook.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit