Welcome to Excel 2016
class video number 16. If you want to download
this Excel file business 218 video 16 or the PDF files and
follow along, click on the link below the video. Oh, man, we've got to talk
about the Christmas tree feature of Excel
conditional formatting. And we're gonna do conditional
formatting to visualize data. And we're going to learn
about some built in features which just take a few collects. And then the true power
of conditional formatting, logical formulas. Here are our 14 topics. Now we're going to start
on the sheet cells contain. And the first couple
examples we're going to look at how to do
a particular conditional formatting with a built in
feature and with the formula. Now before we see how to add
this conditional formatting, let's go over to the answer
sheet cells contains, answer, and look
at how this works. Here's our criteria. As soon as I select Gigi
instantly only the cells that contain Gigi
get the formatting. Now, here's how conditional
formatting works. For all of the cells in
the highlighted range, behind the scenes there's
a true false logical test. And for each cell, we ask the
question, hey, cell content, are you equal to Gigi. Because it comes out true,
the formatting is applied. The next cell gets exactly
the same logical test. Hey, cell content,
are you equal to Gigi? Since it comes out false,
no formatting is applied. Now, let's go back
over to the sheet. And for our first
example, we simply want to highlight the range
with the sales rep name. And since it's a
simple, does the cell contain a particular item,
we have a built in feature. Now conditional formatting is
in the Home ribbon styles group and there is our conditional
formatting button. I click the drop down
and there's a bunch of amazing built in features. Highlight cell rules like
is the number greater than, is the text equal to. Top and bottom rules. We could do the top 10%. Bottom, above average, data
bars, color scales, and icons. We will see examples
of all of these. Let's do the first one. Highlight cell rules. And I want to say is
the cell equal to. Here's our dialog box. And luckily, there's
a collapse button, which means we can link
the criteria to the cell. So with the cursor in this
text box, I select E5, and we're asking
the question, is any cell in that range equal
to whatever we put an E5. Now, the default is
oftentimes not what you want. So we select the
Formatting drop down, and here is the real
power, custom formatting. One, two, three,
four different tabs. For most of my examples I'm
just going to select yellow. You can pick whatever you
want and mix and match from whichever tab. I'm going to click OK. Click OK, and instantly I have
applied conditional formatting. When I select the drop down
here and point to Gigi, only the cells that contain
Gigi get the formatting. Now, if we want to highlight the
entire record or the entire row based on a particular
condition, there's no built in feature to do this. Now luckily, we know how
to do logical formulas. And any time you have
a logical formula, it comes out true or false. So the idea here is
that in our dialog box for conditional
formatting new rules, we're actually going to
have to build a formula. Now, before going up to
Conditional Formatting, New Rule, and building the
formula in the dialog box, most of the time
it's just easier to build the actual logical
formula in the cells, copy it over and down. And see if that patterns
of trues and falses work over here in the cells. Once you see that the trues
and falses are working, then you can paste it
into the dialogue box. Now, how in the world
are we going to do that. That means every single row
has to look at the sales rep and ask the question, is
it equal to the criteria? When we come down here,
is Dwan equal to Gigi? For this record, is that
sales rep equal to Gigi? But remember, each cell
has to have a logical test. True or false, is it
going to get the color? For this record,
all three cells are going to get the color yellow. But watch this. The cell is going to say, hey,
is the criteria equal to Gigi? And this one is exactly
like what we did over here, because the actual cell
doesn't contain Gigi. But watch what happens
when we go over here. Well that cell
doesn't contain Gigi. Yes, but we could
build a formula that's asking the question of
the sales rep for this record. Is the sales rep at this
record equal to Gigi? True. Is the sales rep for this
record equal to Gigi? True. Now, let's come
over here and see if we can build this
formula, because, remember, all three cells have to
get true to get yellow. So we need to see
all trues here. When our formula is copied
down since Gigi is not the sales rep, all of
these need to say false. You ready? Equals. And I'm going to ask the
question of the sales rep for this record. And when this formula
gets copied over it has to be locked on Gigi. So I'm going to hit the
F4 one, two, three times to lock the column
reference, but not the row. And that works perfect,
because when I copied down, there's no dollar sign
in front of the five, so the five will move to a six
and see the next sales rep. Then I have to ask the question,
are you equal to the criteria? And now I want to lock this in
all direction with the F4 key. And that's the formula. Because we have a comparative
operator, when I Control Enter it gives me true or false. When I copy it to the side,
there are our three trues. When I copy it down, look at
that, the patterns of trues and falses are working fine. All three trues mean
one, two, three cells will get the color yellow. All three falses mean
one, two, three cells will not get the color yellow. Now, we copied this
formula over and then down, so we want to be sure and
come to the last cell, F2, and look at that. Our formula is working perfect. Now you can go to any
particular run watch, F2. That cell is looking at the
sales rep for this record, and delivers a false. When I come here,
F2, that formula is also looking that Mo. Now, notice, this
formula delivers a false. Even though it's looking at
Mo, the result of this formula will govern the formatting
for this cell right here. Similarly, F2, this
formula is looking at the sales rep
for this record, and the result from
that formula will govern the formatting for this cell. Now, Escape. We need to highlight
the range and go up to Conditional
Formatting New Rule, but if we did that we have
to re-type the formula, and I already created
it in the cells. So very carefully, I'm going
to come to the upper left hand corner, F2, and copy the
formula in edit mode. Control C, Escape, and then I'm
going to highlight the range. And very carefully, make
sure the active cell is in the upper left hand
corner, parallel to the cell that I copied the formula. If I highlighted it this
way, with the active cell in the lower right hand corner,
the formula would not work. So I'm very carefully, copy
the cell from the upper left. Make sure the active cell
is in the upper left. Now I go up to Conditional
Formatting New Rule. There's a bunch of cool
options, and I want the last one when I'm using a formula. Use a formula to determine
which cells to format. Then I come down here, format
values where this formula is true, and Control V. Now, something very
important about this formula. This formula is not going to
actually go into the cell, because, of course,
if it was in the cell, it would replace the content. The dialog box will in
memory behind the scenes copy the formula
over and then down. The dialog box will
deliver a true or false to each one of the
cells, and either apply the formatting, true, or not
apply the formatting, false. Now we have to create
our formatting. So I click on the Format button. We have four tabs. You can choose
whatever you want. I'm going to choose fill yellow. Click OK. Click OK. And just like, that the
whole row is highlighted. When I change the
criteria to Dwan, instantly the record
has the formatting. Now, these formulas
over here have nothing to do with the actual
application of the formatting. I'm going to remove it with
Alt, E, A, A. Remember, we just created those
formulas, because it was easier to see the patterns of trues
and falses of how it works. Then we copied the formula and
put it into the dialogue box. Now I'm going to Control Z and
leave this there as a trail. Now, let's go over to our
next example on the sheet, below average. We have the same data set
and here are our sales, and I'd like to highlight any
number that is below average. Luckily, there's a built
in feature to do this. Conditional Formatting, Top
Bottom Rules, and then down here we want Below Average. Now, the formatting
is that red again, so I'm going to change it. You can change it to
whatever you want. Custom Format. Four tabs. I'm selecting the
yellow fill, click OK. Click OK. And just like that, it
actually behind the scenes calculated the
average and said, hey, are you cell content
below average? It came out false,
so no formatting. Are you cell content
below average? It came true, so
we've got the format. Now, if we would like to
highlight the whole row, and probably if
it's a bigger record we want to see the whole row. We want to see customer,
sales rep, and other data. So in this case we have
no built in feature, but that's not a problem. We can use a logical formula. I'm going to build
it actually down below the data set in the cells. In that range right there. Now in this case, for every
single cell in the record, we're going to have
to look at the sales. So every one of these
cells right here have to be looking there. And then we have to
compare it to the average. So let's see how to do this. Equals, and this is the
upper left hand cell, as if we're putting
the formula in the cell and copying it over. Remember, each one of these
cells has to look at the sales, so I'm going to click
on the Sales number. And as it copies to the
side it needs to be locked, so I hit the F4 key
one, two, three times. But that G5 is
allowed to move down to G6, which would then give
us the next sales number. Then I have to ask the question,
are you less than the average? And I'm going to highlight
the entire column. And this has to be
locked in all directions, because every
single cell in this range we'll have to
look at the average. Now there is our formula. Control, Enter. Copy it over. Copy it down. And we could see the
patterns of trues and falses. All three cells will have
to get a true in order to get that formatting. Now, I want to go to this
diagonally furthest one away and hit F2. And notice, the
formula works fine. It's looking for this record
right here at the sales number and comparing it to
the calculated average. In this cell right
here, it's looking at the sales number for
this record, right here, and comparing it to the average. This one, also, looking
at the right sales number. Now, one downside
to this formula is that this average
calculation here actually has to calculate in
every single cell. It actually would be
more efficient to put the average calculation in a
helper cell off to the side and then in the formula
just refer to that. In this example we're going to
lead the average function here. In our next example, top three,
we'll do a similar example and we'll use a helper cell. The reason that that matters
is because, remember, calculation time is sometimes a
concern in large spreadsheets. If average function has to
calculate in every single cell, that increases calculation time. Not only that, but
conditional formatting is actually volatile. Every time you do anything
in its spreadsheet, like insert a row or
edit a cell and hit Enter, conditional formatting
actually recalculates. Not only that, but
for big spreadsheets, as you're scrolling down
and exposing more cells that contain conditional formatting
it's continually recalculating. I actually have a bunch of
notes over in the PDF sit you could read if you'd like. Conditional formatting to
sort of like a double whammy, because it calculates
as we scroll down and expose more cells, but
also if we used a formula to determine true or
false for the formatting those formulas also
have to calculate. All right, now, let's copy the
formula in the upper left hand corner, Control C, Escape. And I'm going to
highlight the whole range and the active cell
is the upper left. Now, I'm going to not do
manually Conditional Formatting New Rules. I'm going to use the
keyboard Alt H, L, N. And here, because I need
to get down to the bottom, instead of arrowing down,
I'm going to hit PageDown. Actually when I use page
down it jumps to the bottom, then I'm going to hit Tab
to move to the dialogue box. So the entire keyboard is
Alt, H,L, N, PageDown, Tab. You don't have to
use that keyboard, but if you do a lot of
conditional formatting, that's a pretty fast keyboard. Now I Control V, and
there is the formula that will in memory be copied
over and down to deliver trues and falses for our
conditional formatting. Now I click on Format. You can use whatever
formatting you want. I'm going to say yellow fill. Click OK. Click OK. That is amazing. And I change this to 2000. Watch what happens. Instantly the conditional
formatting changes. Now I'm going to Control Z.
All right, so below average. We can highlight the whole
record with a logical formula, or if we want just the contents
of the cell to be formatted we can use a built in feature. Now let's go over to
our next example, Top 3. Now, on this sheet
we have the same data set and the same
numbers, but I want to highlight the top 3 values. If we're actually looking
at individual cell content, then we can use the
built in feature. I highlight the range. Home, Style,
Conditional Formatting, Top and Bottom Rules. There's Top 10, Bottom 10. We want the top 10. It's easy enough to
change it to the top 3. We simply change it to 3. And I'm going to do
custom formatting. We can do whatever we want. I want fill yellow. Click OK. Click OK. And just like that,
there is the top 3. Now if we want the whole
record highlighted, then we need to so actually
find the third biggest value. Instead of make in our
formula in the cell like we did with the average
function in our last example, instead of having large
and every single cell, and having to
calculate many times, I'm simply going to put it in
what's called a helper cell. Equals. And we can use the
large function. The array, those
are the numbers. I don't need to lock it, because
I'm not copying this anywhere. Comma. 1 would give us the max. 2 would give us
the second biggest. We want to search for
the third biggest. When I hit Enter I
can clearly see that amongst all of these numbers
is the third biggest. It's this one right here. In the top left
cell I say equals. And I'm trying to ask the
question for this whole record, is the particular
number-- and I need to lock it with the F4 key 1, 2,
3 times-- are you greater than or equal to the third largest. Now I'm going to hit F4, that
needs to be locked everywhere. You can compare this to the
average function we just did this cell is
simply referring to the large function which is
calculating only one string y calculation Control Enter
copy it over this formula would be much more efficient
in terms of calculating time. And there is our patterns
of trues and falses. I could see for any
particular cell. Each cell in this row
is locked on the number of this particular record. The criteria greater than or
equal to is the third largest. When I go down a row,
instantly it moves. Now we can edit
mode and copy this. Control C, Escape. Highlight the entire range. Active cell is the
top left corner. Alt, H,L, N, PageDown,
Tab Control V. I'm going to add formatting,
something like yellow. Click OK. Click OK. And there we have it. Now, of course, this is dynamic. If I change this to
10,000, instantly all the conditional
formatting changes. Control Z. And don't forget,
any time we put this over here, by all means, once we're
done, we don't need it. Alt E, A, A. There we go. By the way, Control Z, Alt E,
A, A, of course, is just Home. Over to editing. Clear and Clear All. Now, let's go look at our
next example, data bars. On this sheet we have
student quiz scores. The maximum score is 50. And over here we've already
calculated the frequency for our lower and upper limits. And what we'd like
is a cell chart. Right in the cells we're going
to use conditional formatting to give us a bar chart, so that
70 will get the tallest bar, 13 will get the
smallest, and so on. It'll be exactly
like a bar chart, but we'll accomplish with
conditional formatting. Now we actually need
the numbers in the cell. So right next to the frequency
count, active cell at the top, I'm going to say equals
one cell to my left, and to populate that formula
through the highlighted range. Control and Enter. Now we have numbers in the cells
conditional formatting will work. Click Conditional Formatting
and there it is, data bars. We have gradient fill or solid. I'm going to use solid. And there it is. 70 is the tallest,
13 is the smallest, and each one of the
numbers has a bar length that represents the number. Sometimes you want the
numbers in the cells. Other times-- like here,
we don't want the numbers. We already have the
numbers right here. We can certainly edit,
highlight the range, and if you want
to edit, you go up to Conditional
Formatting, Manage Rules. Or you could use the
keyboard Alt, O, D. We can click the Edit button,
or we can double click. And there it is. Show Bar Only. If we check that it
will hide the numbers. I click OK. Click OK. That is looking beautiful. Now I actually want to
prepare this for printing, and I want to add
some borders, but I don't want borders everywhere. And I actually don't want
any of the gray grid line. So I'm going to turn
the grid lines off. View, Show Grid Lines, uncheck. And there you go. I want not borders everywhere,
but just a few borders. I want a line right here to
distinguish between the limits and our count. And then I want to line right
here to separate frequency and our data bars. Let's highlight. Control 1 to open
up Format Cells, and we want the Border tab. We select our line,
then our color. The defaults will work for us. I'm going to select
on the left and right. That means it'll put a line
just on the left and the right. So I'm going to click, click. If you don't like to click you
can use these little icons down here. When I click OK, click
off, to the side. There's the two lines
separating upper and lower limit frequency and data bars. Now I'd like a line
below the label. So I'm going to
highlight, Control 1. I want it on the
bottom, so I come over here, click on the bottom. Click OK. Now I'd like one for our title. And this one's going to
be a little bit darker. Control 1. I'm going to select medium line,
and I want this on the bottom, so I click On The Bottom. Click OK. Now that's looking pretty good. We could print this out and we
have a great frequency table with our data bars. Now we want to go look at our
next example, color scales. Here we have percentage
change in sales from last year for
each one of the cities and each one of the months. And what we'd like is with color
scale we can add three colors. Red will represent the
bottom third of the values, so anything with red will tell
us that's in the bottom third. White will be the middle third,
and blue will be the top third. So if, for example, Oakland has
the biggest percentage changes, those will be mostly blue. And here's what's amazing
about the color scale, is it will rank
numbers by color. Even though there's only
one, two, three colors. The reddest of the
red, the darkest red, will be the minimum value. And then slowly, because
white is the next color, it will be mixed with white,
getting fainter and fainter til it gets all the way to white. The same thing will
happen with blue. The maximum value will
be the darkest blue, and it will slowly
add white till it gets towards the middle third. And this color scale
is amazingly easy. We simply highlight
all the numbers that are going to get the scale. We go up to Home,
Conditional Formatting. And there it is, Color Scales. I'm going to use this first one. Blue, white, red color scale. And instantly, there we go. Check that out. We can clearly see a lot of red
for Los Angeles, a lot of blue for Oakland. So we know lots of the
biggest percentage changes were in Oakland. Lots of the smallest
percentage changes were in LA. We could also see Tacoma had
some pretty good blues here. And it looks like San
Francisco had some faint reds. Now if you actually calculate
the middle value, called the median, you will
get exactly four, and those are the white ones. So that's a pretty good
way to visualize data. Let's go look at
another example. We want to go see icons. Now on this sheet, we
have Google stock prices. And what we'd like to do is
calculate the difference. So here's 5/19 and then on
5/20 we went up by about $9. So I want the differences here. But then, when it goes up, I
want a green arrow pointing up, when it goes down I want
a red arrow pointing down. And if there happens to be
no change, like right here, I want a sideways
pointing yellow arrow. Now, I first need a formula
to calculate the difference. I'm going to say equals,
today's closing adjusted price and I'm going to subtract
yesterday's, and that'll tell me the difference. Up by $9.42. Now I'm going to copy this down. I'm going to copy all the way
one right before the last, because, of course, we don't
have two values to calculate the difference for that one. But there it is. We have some ups and downs. Now I'm going to
Control, Shift, Down. And I want to highlight
this whole range. And when we go to Home,
Conditional Formatting. Here are icons. The way icons work are
similar to color scales, but when you see
three icons, it's going to mark all of the
top third values as green, the middle third as yellow,
and the bottom third as red. If you use four, it'll divide
the data set into four parts. Five into five parts. Now we're going
to have a problem, but we'll go edit this. If I click this, of
course, what it's doing is it's dividing the data
set into three parts. But let's go edit this. Remember, editing is
Manage Rules or Alt, O, D. I can double
click to edit this, and let's see what's
going on here. There's three different
icons, so there's three different upper
and lower limits. Now these are percents, so it's
dividing based on percentage. The bottom 33% are red,
middle 33% are yellow, and the top 33%, or
one third, are green. I'm going to change
these to numbers. And I'm going to say whenever
you find greater than zero, then I want a green. Now if I click this, I
don't see an equal to zero, but that's not a problem. I'm going to say anything
greater than or equal to 0 will get a yellow. But wait a second. Isn't there a conflict here? No, because the way
it works is they'll apply the green one first. Then anything left
over gets the yellow. And then the red. So because all of the values
bigger than zero have a green, only the equal sign
will be enacted here. And then everything
less than zero we'll get that and click OK. Click OK. And the hat is amazing. Now there's another
way we can do this. And we actually could have
done this right in the cell. If you only want to see the
arrows, you can say equals-- and I'm going to do
the same formula-- today's price minus yesterday's. And notice, when I copy this
down, sometimes it's negative. Sometimes it's zero. Sometimes it's positive. Well there's a
specific function, F2, that'll take any positive number
and deliver a one, any zero and deliver zero, and
any negative number and deliver minus 1. It's called hey, give me the
sign, the sign of the number. Close parentheses,
Control, Enter. And I can copy this down. And so now we're going to have
just ones, minus 1, and zero. And notice that
perfectly divides the set into three pieces. Minus 1 is in the bottom
third, zero's the middle third, and one is the upper third. I go to Conditional
Formatting, Icons. And I'm going to
select this one, and instantly we get
the correct arrows. Alt, O, D. Because I'm going
to double click and hide. Show icon only,
click OK, click OK. And so there you go. Lots of times you'll
see spreadsheets with the actual
change or difference, whether it's stock price
or sales or daily units or whatever. And then next to
it you could use this conditional formatting
icon trick to give us arrows. Now let's go look
at our next example. It's whole column. We saw how to highlight a
whole row, but what we'd like is a whole column. So I'm going to highlight
the entire range. And instead of building our
formulas first in the cells, this is a relatively
easy formula and sometimes you want to go
straight to the dialogue box and build it. Now, the formula
we're going to need is that every cell
in this column has to look at
the column header, but when we copy
over to February, every cell in this column
has to look at the column header for February. Each one of these
column headers has to be compared to the
month we select up here. All right, so the active cell
is in the upper left corner. I'm going to use the keyboard. Alt H, L, N, PageDown, Tab. And now I'm going
to build my formula. Now, click on the
first column header. Remember, even though
all of these cells are getting the
conditional formatting, we're certainly allowed
to look at some cell outside, of course. This needs to be locked
when we copy down, so it has to be
locked on B5, but when I copy it to the side
it needs to move to C5, so hit the F4 key once. Now, I didn't actually hit it. Notice, the default is always
absolute in this dialog box, here. So all I have to do is
hit F4 one time to lock the row, but not the column. Then I need to ask the question,
are you equal to the July or whatever criteria
we select up here. Notice, it's absolute. When we first clicked on a cell
and that's what we want here. Now that will work. Now, I'm going to format
this, and I'm not just going to add the
same old the yellow. I actually would like to
add some number formatting. And the reason why
these percentages have zero decimals showing, and
when I select a month I want to expose a bunch
of hidden decimals. So I'm going to go over to
percentage, select 2, click OK. Now, when I click OK here
the conditional formatting does two things. It adds the fill and
the number formatting. But now we simply come up
here, and this is so cool. I'm going to select
May and instantly I see highlighted in yellow
and decimals exposed. If I want to see
January, there it is. All right, so that's
conditional formatting for an entire column. Now let's go over to
this sheet And and Or. We're going to talk about
conditional formatting with an And logical test
and an Or logical test. I am going to build this
one in the cells over here. But, notice, for each
one of the records, we're going to have
to ask two questions. Is the sales rep equal to
whatever we select here? And is the customer equal to
whatever we selected here? Now I'm going to come over here. Two conditions, right? They both have to come out true. That's the perfect job
for the And function. Logical test for this
record, sales rep, and I need to lock it. F4 one, two, three times. As I copy across the columns
it needs to be locked at an A5, but when I copy down, it needs
to move to A6, A7, and so on. And then I asked the question,
are you equal to sales rep selected from that cell? F4. There's the first logical test. Comma. The second one, I'm
going to look at customer and lock it with the
F4 key 1, 2, 3 times. Column locked, but not the row. Are you equal to Google? F4, close parentheses,
Control, Enter. Copy it to the side
and then copy it down. We're looking for our
patterns of trues and falses. It looks like Dwan Google,
we got trues everywhere. So that whole row will have
whatever formatting we choose. I'm going to last cell F2. It looks like we've got all
the cell references working. Now I'm going to copy the upper
left hand cell in edit mode. Control C, Escape. Now I highlight, making sure
that the upper left corner is the active cell. Alt, H, L, N, PageDown,
Tab, Control V. I'm going to format
it just with yellow. Click OK. Click OK. And just like that when I come
over here and I select Gigi, there are no records where
Gigi sold to Google customer. If I select Mo there is one. So that's And. Now Or. We learned before that we
could use the Or function to check this customer against
the first customer on our list. And then check this one
again against Amazon. And then this one against IBM. We could use the Or with
three logical tests. But also when you see this
situation, we have a list here, and we have a single item. Is this item over in this list? Any time that's the
logical test, yes it's an Or logical test. But we can use the Match
function to ask, hey, is this IBM in this
list over here? So I'm going to equals match. And now the lookup value
is going to be customer, and when I copy it
across the columns I need it locked on the B, so
I hit F4 one, two, three times. Lock the column,
but not the row. That's the lookup value. Comma, lookup array. That's this list of customers. F4, Comma, 0. Because that list is not sorted. Close parentheses. Now two things about using
a Match for Or logical test. One is, if you have
a bunch of criteria here this is going to be
much faster than creating that Or formula. Also, we're going
to learn something about the Conditional Formatting
dialog box and errors. Control, Enter. We know that when we
get a number that's the relative position, so
IBM was in one, two, three, the third relative position. When I copy it down,
we know that NA's means the idea was not in the list. So Target is not in this list. Now the great thing about
this is, in the cells we have problems with
errors like this. It can cause great trouble. But not in the Conditional
Formatting dialogue box. Remember, our complete list
what triggers formatting. To get the formatting it's
either true or any nonzero number. What triggers no formatting
is false, zero or errors. So from the upper
left corner I F2. Copy it in edit mode,
Control C, Escape. Highlight. Make sure the upper left
corner is the active cell. Alt, H, L, N, PageDown, Tab,
Control V. There's our formula. Formatted. We can format it
however we want. When I click OK, click OK. Just like that, I
have conditionally formatted an Or logical test
using the Match function. Now, if I delete all these,
of course, now we get nothing. But now if I select
Target and Vans, just like that it is working. Now let's go look-- we have
a couple more conditional formatting examples. Let's go look at a weekend. This is a great example. This is a schedule, and
we have all of these days and here's the times. We type things here for
particular appointments. But what we would like is,
since this is a template, I want to be able to
just change these dates. And whenever it is a
weekend or a holiday-- and weekends for this company
are Fridays and Saturdays-- or a holiday,
which is May 30, we want to automatically
add some color. In essence, the color will
say this is blocked out. Now this one's going to require
that we create our formula over in the cells,
because we're going to use a brand new function
called Net Work Days. Now, Net Work Days
is an older function. In 2010, they came out with Net
Work Days dot international. And dot international
does some amazing things. Now, before we use Net
Work Days dot international for our conditional
formatting trick, let's just figure out
how this function works. Now if we have a list
of days like this we know how to count the days. We take the last day
minus the first day. It tells us how many days
in between those two dates. But what if we want
to only work days? That's where this
function comes in. You give it the start date,
comma, the end date, and then you tell it what
your weekend is. Our company has weekends
of Friday and Saturday. Now what makes the dot
international amazing is this list. In earlier versions we only
had Saturday and Sunday. So I'm going to select seven
for Friday and Saturday, comma. And then we need our holidays. It will skip over
the holidays also. Now I'm going to hit F4. Close parentheses. Right now we're just
seeing how net work days dot international works. There are 15 workdays
within that range of dates. By itself it's pretty
amazing, but here's how we're going to use it. I'm going to give it the
start date right here, and I'm going to lock it with
the F4 key 1, 2, 3 times. And we're going to do something
really crazy to end date. I'm going to use
this same start date. And then lock it with
the F4 key 1, 2, 3 times. Now what in the
world would that do? Any time you have the
same start and end date you can only
get a count of one when this shows up as a work
day, and you get a count of 0 when it shows up as
a weekend or holiday. So that's our formula. Control, Enter, copy
it over, copy it down. Now it's almost our
formula, because, of course, all of these ones
if we dump them in the Conditional
Formatting dialogue box would give us true. And how would be highlighting
the actual workdays. That's not what we want. So we're going to
amend this formula, because what do we want? We want to ask
the question, when does net work days dot
international come out to be zero. We just say, when
you're equal to zero. That is a logical formula
with a comparative operator. Now it will deliver true
when it sees a zero. Double click and send it down. And there's our pattern
of trues and falses. Now we could see for
this Monday May 30. True, true, true, true, true. All of those trues will
be format in this row. So I'm going to go up
F2, copy in edit mode, Control C, Escape,
highlight the range. Active cell in the
upper left hand corner. Alt H, L, N, PageDown, Tab. Now I'm going to Control V.
Format however you want it. I'm going to add yellow. Click OK. Click OK. And just like that. The whole row is blocked
out, because that is either a weekend or a holiday. When these dates change
everything will update. Now we have one last example. We want to go over
the sheet not in list. Now, here's our example we
did back in our lookup video. Here's a library database of
all the books we should have. Here's the list of
the inventory count. We're interested in the
books that are missing. So we have to say, are
you not in this list? Any time we're comparing
two lists we start off with the Match function. And actually we did
this exact formula with a column of
trues and falses back in our lookup video. We're going to ask the
question, hey, lookup value. I'm looking up this book in the
library database, comma, lookup array. That's the entire
list of books we found in our inventory count. Control, Shift, Down. F4, Comma, 0, because
the lists are not sorted, close parentheses. And Match, of course, delivers
the relative position. The Stones Diary is in
the eighty third position in our inventory list. Now I'm going to copy this down. And, of course, numbers
mean they're in both lists. We are interested in the NA's. That means the book is missing. That book is not
over on this list. So what do I do at the top? F2 in the active cell. I ask the question, is NA? Which says, are you an NA? When it sees an NA it
will deliver a true. Close parentheses. I want to populate this edited
formula all the way down, so I Control, Enter. And there you go. There's our trigger for
the book is missing. So I come to the
top cell F2, copy it in edit mode, Control C, Escape. Click on the top cell. Control, Shift, down arrow. I'm going to use Control,
Backspace, just to jump back to the active cell. Alt, H, L, N, PageDown,
Tab, Control V. And there's our formula. Format. And I'm going to use
fill red, font white to mean this is a missing book. This is trouble. I'm going to click OK. And there it is. We have conditionally
formatted this entire list. So it's easy to see the
books that are missing. Wow. That was a lot of amazing things
about conditional formatting. We saw how to conditionally
format, not in a list. We learned how to conditionally
format for the weekend. We talked about And and
Or conditional formatting. We conditionally
formatted a whole column. We used icons. We did this amazing
color scales. Data bars. And our first three examples,
actually six examples, we did top three with
built in and formulas, below average with
built in and formulas. And we started off with does
the cell contain built in? Does the record contain
a particular item? All right. That was a lot of fun with
Conditional Formatting. Next video we'll have an
introduction to dashboards. All right. We'll see you next video.
