Introduction to Conditional Formatting (Part 1)

Hi and welcome to part one of this two-part blog post series on Conditional Formatting. In this blog post, I will introduce you to the Excel feature of Conditional Formatting. I’ll give you a brief idea of what it can be used for and provide you with some examples.  

What is Conditional Formatting?

Conditional Formatting allows you to format cells and their content based on a cell’s value. The extent of the formatting you can apply is pretty much the same as when you apply formatting normally.

Formatting is applied based on rules. Excel has some built-in rules, which we will look at in this blog post, but you can also create new rules that we will discuss in part two of this series of blog posts.

Formatting can be applied to a cell or a range of cells. The formatting doesn’t have to be applied to the cell containing the value that the rule is checking.

You can have multiple rules; different formatting can be applied depending on the value of the cell.

Accessing Conditional Formatting

To access Conditional Formatting in Excel, click on the Conditional Formatting button on the Home tab in the Ribbon (see the images below).
Mac Conditional Formatting Button

Mac Conditional Formatting Button

 

Windows Conditional Formatting Button

Windows Conditional Formatting Button

Built In Formatting Rules?

Now that we know how to access Conditional Formatting and a little bit about what it does, let’s have a closer look at some of the built-in rules.
If you click the Conditional Formatting button and then click the Highlight Cells Rules button, you’ll see a drop-down menu, something like the image below.

Conditional Formatting Rules Menu

In this menu, you will find several built-in rules that allow you to highlight cells based on certain criteria. The built-in rules in this menu are described in the table below.

RuleDescription
Greater ThanThis rule allows you to highlight cells that are greater than a certain value. For instance greater that the number 10
Less ThanThis rule allows you to highlight cells that are less than a certain value. For instance less than the number 10
BetweenThis rule allows you to highlight cells that are between two specific values. For example between 10 and 20
Equal ToThis rules allows you to highlight cells that are equal to a specific value. This value doesn't have to be numeric, it can be text or a date for example.
Text that ContainsThis rule allows you to search the contents of a cell and highlight it if it contains certain text. For example you might certain of cells and highlight those that contain the name John
A Date OccurringThis rule allows you to highlight cells and highlight them if they contain a date that is within a specific range. For instance dates that occur in the next month or the previous week.
Duplicate ValuesThis rule allows you to highlight cells that have more than one occurrence of a value. This could be used to highlight duplicate values if your wanting a unique list of values

Some Examples

Okay, let’s take a look at a couple of examples.

Highlighting Duplicates

In this first example, we will highlight duplicate names from a range of cells. First, highlight the cells with the names in.
Duplicate Values Select Range

Then click the Conditional Formatting button, then the Highlight Cells Rules button, and then the Duplicate Values button highlighted in yellow in the image below.

Duplicate Values Button

From the pop-up menu, choose how you want the cells with the duplicate values to be formatted. The pop-up menu on Windows and Mac look a little different as you can see from the images below; however, they perform the same function.
Select Formatting Mac

Select Formatting Mac

 

Select Formatting Windows

Select Formatting Windows

When this is done, Excel will highlight the cells with names that occur more than once in the range of cells selected.
Duplicate Values Highlighted Cells

Highlight Cells Containing a Specific Value

In the second example, we will highlight cells that have a value greater than 120. Like the first example, start by selecting the cells that you want to apply the Conditional Formatting too.

Greater Than Select Range

Then, click the Conditional Formatting button, and then the Highlight Cells Rules button, followed by the Greater Than button that is highlighted in yellow in the image below.

Conditional Formatting Greater Than Button

Same as before, from the pop-up menu, choose how you want the cells to be formatted. Again, the pop-up menu on Windows and Mac look a little different.

Select Formating Mac

Select Formating Mac

Select Formatting Windows

Select Formatting Windows

When this is done, Excel will highlight the cells with values greater than 120.

Conditional Formatting Greater Than Highlighted Cells

So that’s it for this post. I hope you have enjoyed it and learned a little. In the next blog post in this two-part series on Conditional Formatting, I will discuss how to manage multiple rules and how to extend the built-in rules and create your own.

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