Hi. In this video we are going to walk through how to use the Highlight Cells Rules of your Excel
spreadsheet's Conditional Formatting options.
The Conditional Formatting options can be found in the Styles section of your Home tab and, clicking on this, you see that we have a
number of options to choose from. In this
particular video we're going to be focusing on the Highlight Cells Rules.
So, under Highlight Cells Rules, the first option we have is to highlight cells that are Greater Than. This means that, using this formatting,
you can highlight any cells with a value higher than the value you chose.
So, for example, we have this dataset here and let's say we want to highlight any number that is greater than 8. Well, we can either highlight
this column, column D, where our data is or we can select just the area with the cells that we want to apply the conditional formatting to.
For ease, I'm going to highlight the entire column. So once we do that, we can go back to Conditional Formatting,
click on Greater Than,
and this defaulted to 7, but you can either choose the cell with the value that you would like
to find values greater than or you can simply type the number 8.
And when, we do this, you see that now, the cell with the value of 10
is highlighted for us.
We can use Conditional Formatting to highlight cells that are less than another number or less than the value of another cell.
So, going back to our example, we'll highlight column D again and this time we want to
highlight anything that is less than the number 8.
So, we go back to Conditional Formatting, click on Less Than, and we'll change this to 8. And we need to change the formatting, because
while you can have multiple conditional formats applied to the same cells, you want them to look differently so you can tell them apart.
So we click OK.
And right away we see that we have a problem.
All of our cells are highlighted except the ones that contain the number 8 and the number 10.
The reason for this is that Excel sees the blank cells as being equal to zero
and zero, of course, is less than 8.
So we'll actually highlight this again, go into our Conditional Format / Manage Rules and I'm just going to delete this rule.
Click OK. And, so, this rule is best used when all of your data is together, or when you have a small set of data like
this where it's just very easy and quick to just choose your data points, or, of course, if you would like to
include the blank cells in your formatting for less than. So we go back to Conditional Format,
click on Less Than, change this to 8,
yellow fill. And now that we've applied that formatting just to those cells that we want to look at, we see that the numbers 3
and 5 have been highlighted as being less than 8, which is what
we wanted the formatting to do.
So our next option is that we have the option to highlight cells between a minimum value and a maximum value. So, to show this example,
I'm actually going to copy these numbers over,
and I'm just copying and pasting, and I'm going to delete these formats so that
we can start fresh. So, choosing our section and going back to Conditional
Formatting, Highlight Cells Rules and click on Between.
And we'll set our minimum at 4 and our maximum we'll leave at 8. So, this will highlight for us any cells that are greater than or equal to
4 and less than or equal to 8. So, and you see here what it's already doing. So we click OK
and the numbers 5 and 8 have been highlighted.
So we can also create a Conditional Formatting Rule to highlight cells that are equal in value to another cell.
And this can actually be a number or it can be
a text. So, for example - we'll scroll over a little bit - so we'll do the number 10.
And we'll type the word cow.
So, if I type here 10, 12, cow, bird and the cow jumped,
and we want to say
highlight anything that is equal to 10,
we see that the number 10 is highlighted for us. Now, we can also say highlight for us anything equal to cow.
And you see that only the word cow has been highlighted, not the cow jumped. Now, another thing, because typically when you
do anything with text in Excel formulas and rules you need to use quotation marks, in this case
you don't. I can just type the word cow and it does the same thing.
So, another thing you can do with Conditional Formatting is to highlight a cell that contains a certain something. So, the reason I say
"something," even though this is clearly labeled Text that Contains, is because this can be used to find specific text,
specific numbers, or specific characters - if you are looking for something with an !,
quotation marks, a disclaimer sign, as long as you can type it into the cell rules box,
this formatting will work for you. So, for example,
we'll highlight our area and let's say we want to highlight any cell that contains the word cow.
We'll make this green. And you see that it highlights the cell that contains only the word cow. But it also highlights the cell that
contains the words the cow jumped because the word cow is part of the sentence the cow jumped.
Now, let's say, still using the same area,
let's say want to highlight any cell that contains the number 2. And we'll make this yellow.
So you see that it highlights a cell with the number 12, because, to us, this is the number 12, to Excel it's a combination of the numbers 1
and the number 2, which, of course, contains the number 2. So it flags this as containing
whatever we ask for it to look for, in this case the number 2. So, this formatting option is
great, especially if you're working with a document that has a lot of data
and you need to find
a specific something and have it pop out for you. Another option you have with Highlighting Cell Rules is to highlight a date occurring
within any of the presets that Excel has chosen for you already. So you have some,
um, around recent days, weeks, or months. So to show this in an example, we'll scroll over a little bit,
and we'll type in a few dates quickly.
And, let's say what want to go back and highlight
any cells that have a date reflecting today - which is 9/14/2017.
And we see that we can easily highlight using this formatting.
So our final option in Conditional Formatting / Highlight Cells Rules is to highlight Duplicate Values. And what's neat about this option is that
not just can you highlight duplicate values, but you can can also highlight unique values.
So we'll cancel out of this for a minute, we'll go back to this column and say we want to highlight any duplicates.
We'll make them Yellow, click
OK. And right now there's no duplicates, but if I type the number 12 again you see that both 12s have now been highlighted. If I delete the
second 12, then this unhighlights the first one because now it's no longer
duplicated.
Now, if I want to highlight unique cells, I can go back and, this time, choose Unique - and we'll just do a Red Border -
and you see that all of our cells have the red border because
they're all unique values. But if I type the number 12 again, this goes back to the first formatting
that we had in here for duplicate values and makes them both yellow and removes
the unique formatting
that we had preset.
I hope you found this tutorial helpful to learn how to highlight cells using Conditional Formatting in Excel. If you
enjoyed it, please make sure to give it a Like and don’t forget to subscribe to this channel. Thanks!