In this video, we do a deep dive into the essential features of conditional formatting. We cover all the rules except for “Custom formula is” (check out the advanced video for that one!) as well as how to copy rules and even turn off the conditional rules but keep the formatting.
hey folks and welcome to this video we
are going to be talking about
conditional
formatting and in this particular video
I’m going to cover all of the basics the
foundations the Essential Knowledge you
need to know about conditional
formatting and in the other video that’s
part of the series of two is all of the
advanced tips and tricks so if you are
if you are already familiar with
conditional formatting then feel free to
jump over to that other video and learn
all about the more Advanced Techniques
but in this video what we’re going to do
is focus on what we call conditional
formatting which is formatting that is
applied based on conditions being met
within your data so things like numbers
exceeding certain thresholds and you
want to highlight those values to bring
some attention to them so as I said in
this video what we’ll do is focus on the
foundations the basis so under format
rules we’ll be looking at all of these
builtin rules so the text ones the date
ones the number ones what we will skip
in this video is the custom formula ones
so we’ll leave the custom formula ones
to the advanced video so if you’re after
custom formula ones you want to apply
formatting across an entire row or an
entire column then go and check out that
custom formula video part
two okay let’s highlight the whole Ed
set and the quick way to do that by the
way is to press control a or command a
and it will just highlight to the edges
of the bound so the first thing to know
about conditional formatting is you have
to highlight all of the range of cells
you want to apply it to so it’s not like
a pivot table where for a pivot table if
I want to do a pivot table on this whole
data it doesn’t matter if I’ve
highlighted the data or not I can just
be clicked here inser a pivot table and
it will grab all of that data that’s
linked if you like continuous to this
cell conditional formatting is a bit
different we have to highlight the whole
data set there to make sure we get all
of the rows and columns we want we’ll go
up to format and we’ll go to conditional
formatting and it’s always defaults to
just saying format the cells if they’re
not empty so this is a good place to
start because it illustrates what we’re
doing here what the formatting is doing
it’s just saying if the cell is not
empty in this range we’ve chosen a apply
this style here which we can set so we
could say bold we could say not bold
italic not italic change the color of
the text change the background of the
text all these sorts of things so so
let’s just switch it to empty they’re
the easiest two and you can see there
obviously there’s no text in these empty
ones so it won’t show up with text so
that doesn’t really count but you can
see
that all it’s doing is just let’s get a
yellow it’s going to be something that’s
kind of useful to say hey look there’s
some blank cells in your Des set that
you might very easily Miss if you didn’t
have the formatting on but with the
format thing it can highlight this blank
cells so that’s the first one just to
show you is
the is empty and is not empty now really
really important with conditional
forming to keep an eye on this
range because sometimes the the range
can get out of whack out of line with
what you want to to highlight so if you
find anything going wrong with your
rules always come back and just check
this range is the range you want to
apply the formatting too and if it’s not
just change it delete it highlight it
again if you want click okay at a second
range if you need to that sort of thing
all right back in conditional
formatting let’s just clear that one out
now that was the empty one I have my
data highlighted already We’ll add
another
Rule and this time time we did is empty
and is not empty let’s take a look at
these contains these text ones so we’ll
say text
contains and we’ll say
Ross and there we go you can see it’s
highlighted those ones for us now you
could go obviously and
change the formatting to whatever you
wish say to that yellow there so that’s
saying find any text that contains the
word Ross now if you had some funny word
with Ross in the middle here it will
capture it and highlight it so you have
to be
careful that’s going to find the it’s
not looking specifically for the name
Ross per se it’s just looking for those
four characters r o SS doesn’t matter if
they’re capitals or lowercase now if we
want to say well I know that you know
I’m really looking for people who have
the surname Ross well let’s try saying
ends
with and now it’s just found those only
those three you can see it now doesn’t
have that one Ross Smith if we do it a
starts with you’ll see it highlights the
Ross Smith but now it doesn’t highlight
those other Ross ones now if you’re
sitting here thinking well how do I get
this to apply to a whole row that’s what
I really want to
do check out the advanced conditional
formatting video because we can’t do
that with these built-in format rules we
need to use the custom format rules and
my second video conditional Advanced
conditional formatting is all about
these custom rules okay so that was
contains does not contain well that’s
going to highlight everything that
doesn’t have a Ross if you want to kind
of do the opposite uh starts with and
ends with we did text is exactly well
it’s not going to highlight any Rosses
but what if we said we want to say the
text is exactly buyer then it’s going to
highlight these buyer rows for it which
is really nice okay so let’s take a look
at date conditional formatting
and let me tell you a little bit about
dates first before we do the conditional
formatting I’ll let you in on a little
trick that you may or may not know about
with dates is let me just highlight some
dates here let me change them to
numbers huh very
odd well that’s how dates are stored in
spreadsheets that actually just numbers
and it’s how many days since the 1st of
January 1900
and because they’re numbers it lets you
do things like add dates together take
dates away to get how many days there
are between two dates so dates are
numbers and why I’m telling that is
because if we let’s take them back to
proper dates but let me just say leave
the first view as numbers just so we can
keep this in my all the so there’s
middle three there let’s make them
numbers okay so they’re just showing as
numbers those dates and let’s just
delete this column for a moment so this
I want this column here to be showing so
now I’ll highlight my data set just as
before and you’ll see I’ve got the date
column so we’re going to add a rule and
we’ll say the date
is and we’ll start with if we say date
is it’s going to highlight any cell
where the date is exactly equal to
today if I want to do something
different I could say pick some other
options they have here like today uh
tomorrow yesterday in the past week in
the past month none of which are going
to show up with this data set or I can
pick a an exact
date and then just type it in
third 24 there we are the 2nd of March
2024 so that state is now if I do date
is
after the 3 the 2nd of March 2024 let’s
just make it the 1st of March 2024
you’ll see it then captures that one you
see it’s highlighted all my dates but
it’s also highlighted all of these
numbers so in fact what we could do just
to show you is we could format these as
dates and it’s the year it’s the 11th of
January
2798 the 25th of November
534 uh so you know it’s able to format
them as dates because these are big
numbers so just keep that in mind and
perhaps what we might say in this
example is let’s pedal back a little
because we’re getting a little confused
here is let’s just turn these back to
dates because we’re talking about dates
we want them to be dates and let’s just
highlight the date
column rather than including these
number columns so just really all of
that was just to be make you aware of
that issue when you’re highlighting and
if you’re suddenly wondering why other
cells are being highlighted with your
conditional formatting it might be
because you’re doing dates and you have
these big numbers that the spreadsheets
thinks are dates so now date is after an
exact date of the uh 1st of March
2024 and now it just highlights the
dates that we want and again we could go
and change those colors if we want to
something so that’s how we work with
dates let’s just highlight another Rule
now and let’s do a number rule let’s do
the number rules now so we’ll
say greater than greater than or equal
to less than less than or equal to is
equal to is not equal to and is between
and is not between so lots of different
things with numbers again let’s just be
a little more precise and change this by
just typing in F1 to
F21 and that will just then make sure
we’re just working on this number column
only and we’ll say we’ll do the number
is greater
than and we’ll say uh
400,000 so what are the properties over
400,000 we’ll say done
so let’s highlight this column here and
add another Rule and this time we’ll say
is greater than we’ll say let’s say
700,000 uh and let’s make the 700,000
one a different color to make sure we
can see it we’ll make those
ones uh the the bright orange
done okay hang on a minute you’re
thinking why are all my everything over
400,000 is highlighted but these ones
over 700 there’s one there there’s one
there and one there they’re not being
highlighted why is
that so let’s go conditional formatting
again and by the way let me show you
this trick as well since we’re here
format conditional
formatting you see if I highlight the
rules it puts the green border around
this column F to tell me where the
ranges the rules are being applied now
if I X out of here come over here click
outside of that column F where that rule
is applied and I say format conditional
formatting then it doesn’t think there’s
any rules appli to this column it’s
prompting me to create a rule so it only
opens the rules that you have applied if
you’re in the range where they’re being
applied so now when I say conditional
forming it does in fact show them
because I’m clicking in column F so
really important to know that now
then this is the another very important
thing to know about conditional
formatting rules is that the order
they’re in here is the order they are
applied the top one takes precedence
over ones underneath and if I had a
third rule down here that would be third
in the pecking order so really I want to
be highlighting anything that’s over
700,000 first because that will turn
them orange and then everything over
400,000 is beyond below that because
those 700 sort of take precedence over
the 400 so you can grab where those dots
are to the side when you’ll
see you’ll see when it changes to that
four-way movement Arrow you can swap you
can drag your rules around and if I had
a third one I can drag that into the mix
as well and that is how you can just
make sure you get the order right so
there highlighting the ones that you
need to see first now another question
that comes up a lot with conditional
formatting that we’ll we’ll answer here
is how do I copy this into another
somewhere else how can I copy these
rules somewhere else okay so we’re going
to copy them somewhere else but first of
all let me just add a couple more rules
to make this a little easier to see so
let me highlight whole data format
conditional formatting add another rule
is empty we’ll say we’ll make the empty
cells bright yellow there they are those
those ones so we’ll say done we’ll add
another Rule and we’ll say text
contains buyer so those ones there and
we’ll make that one bright purple
done okay so we we’ve got these funky
rules that we’re really proud of that we
want to apply to we have a new data set
over here and we want to apply the rules
now what’s important is that I have the
same
columns and positions of the columns in
this data set here so the rules can be
copied across to the same positions but
that’s about the only thing you really
need to be careful of otherwise we can
just highlight this data set
the whole thing so just command a or
control a or drag across if you prefer
so highlight the whole thing use the
format painter here the paint format
click that come into sheet two and then
click on listing in the top corner there
and you saw it applied those rules for
me and now you can see because it’s
going to apply it to the same size range
as this one which went down to roow 21
so it’s gone down to Road 21 this time
and it’s highlighted all of these as
blanks for us now couple of blanks there
we’ve got far less buyers now in this
data set you can see we had more buyers
in this data set so we know it’s a
different data set we’ve got uh the
values being applied still and if I open
the conditional formatting then all the
rules have copied across perfectly
they’re just applied as they are in the
sheet one data set so that’s actually
really handy so that’s the paint format
trick you can move your conditional
formatting rules very quickly from one
sheet to another okay so now suppose
want to make this permanent here we have
the
data format conditional formatting okay
it’s all conditional formatting still
decided that I don’t need that anymore I
just want this applied permanently I’m
going to copy that step one while I have
my whole data highlighted step two is to
format clear formatting so click clear
formatting under format or use the
shortcut that looks like that then the
next step is to somewhere my data here
is to rightclick paste special and say
format only so paste special format
only and then it applies all those
formats back but it applies them as
permanent now and if I open my format
conditional formatting rules I don’t
have any rules these aren’t rules
anymore they’re just background colors
of cells now so that’s how you do it if
you want to
apply the conditional formatting or
change the condition formatting into
just permanent
formatting great well that’s a look at
all of the basic foundational features
of conditional formatting it’s a hugely
useful technique to understand and be
able to apply what I really encourage
you to do is go away and practice what
you’ve learned in this video once you’ve
mastered all that which really shouldn’t
take you too long come back and check
out the advanced video where you’ll
learn how to do things like apply these
formats across entire rows which gets
very useful or down entire columns or
highlight rows where it’s a seller over
a certain value so we’re going to go
into a lot more examples that are more
complex but also more likely to be the
sort of real world problems you’re
trying to solve so thanks for watching
and if you enjoyed this video please
give it a thumbs up and consider
subscribing to the channel so you don’t
miss future videos all right thanks for
watching folks I’ll see you again soon
in another video bye for now