6 hours 3 minutes
Now we're going to talk about conditional formatting men. Conditional formatting is one quite often Lee used function or future of excel. That is important if you want to emphasize the content of the cell with the look of the cell based on the content.
So if this sounds a little bit confusing, I'll make it very simple.
So we have some kindof weekly
the sales report. We have quotas for that week, and we have revenue, and basically, we have to make sure that we never revenue is actually smaller than quota.
Then, well, we have to make it somehow look different.
So if we just take on one of these cells and click on conditional former thing to get to this dropdown list, so if this one is less than
then we'll make it red.
And then let's copy, form it throughout
the whole range.
It's just by right clicking on this
here. Yeah, this is what we got
and Dr Drag dropping it to the end of the corn,
and we got all the situations in which we are below quota.
We wanted to be read so that we know we failed,
and it's a simple as that.
Now let's, uh, copy this
just the will use.
And, uh, let's look further into possibilities off
conditional formatting. So, for example,
let's go here and the highlight cell. So let's say that this is greater than
okay, 32,000 for example.
That means that it's
extremely difficult to achieve or it's it's a heavy quota. So well, simply put the ability
bread text or whatever. In this case, I'm going to go with custom format. So I wanted to be bold
and ah, I want Phil off the
the whole cell to be light green, for example, No
yeah, leave it like that. And then let's drag drop it to the end. Right click off course.
And we just a feel for mating only. And because because this is a part of for mating. Conditional formatting is former thing as well.
Well, now have
ah, Yello. These cells
and ah then go further more.
And let's say that we want these cells where we are
better than quota
to be blue.
So we go, here we
go. Here we click on greater than we click on this cell here
and let's say, uh,
we go custom red form it. And we wanted to be
and we just likened this and then we expand
Phil former thing.
And now this is it. So these are the things that you can do with simple
pretty find for months of cells.
Now we can go further than this.
We can make
more conditions for former thing, and I will show you how to do it right now.
Okay, let's do another copy of this
copied here. We'll just pays the failures.
Sorry, we copied
So let's base the Williams.
And then let's look at some of these. So, for example, we have data bars
and if we select thes cells here into conditional formatting and weakened ate the bars
and for his own political blue one
Among this set of cells of group of cells,
there are those that have the maximum value and those that have the minimum. Ellie. And basically this data are goes from zero to the maximum value and shows for each cell. How good is it in this range? So from zero to the maximum value it will,
it will look a TTE the group of cells and say OK, this fund, for example, is very low,
so it has much shorter blue bar. Ah, it, uh,
it is not working with the the range from minimum to maximum. It's always working with a range from zero to the maximum. Of course,
if where we go here, for example,
Anglican, I consents
so we can add this
and this one thes these air showing if the well you is close to the average or below the average or
above the average. So if it's green, it's above the average. If it's red, it's below. If it's yellow, then it's close to the average off all of these cells. So these indicators can be quite interesting to use.
There is more. And to this and let's go, for example, here and now we can do
even more So we have these ah, top bottom rules. So sorry. So we can, for example, to stop 10 items stumper step 10% Bottom 10 NYTNS. But about average below average.
Let's go for a top 10%.
And, uh, yeah, it's like this. So this cell is stops 10% off the range,
but if we select again
and undo the former thing and now go to
Doc 10 items
So these are the best, These air top 10.
And this is how we can highlight the best performers in some some
competition or something like that.
Um, let's do another copy
do one more thing.
And that is
And now we're going into defining a rule on our own. So all these conditional former things are treated in Excel is rules. So the rule for this cell is that if it's, ah,
it wouldn't up 10. It will be highlighted green or something like that,
so we can do the 1st 1 which is format cells based on their values. And let's select a range
and let's a conditional former thing, and now you roll and form. It sells based on their well, yours
and the format style can be some kind of data bar, for example,
Ah, and, uh,
minimum can be defined. Men usually like the lowest value, for example, off the range
and maximum would be
something else, so it can be automatic or
whatever, but in this case, we're going to go with number,
and we're going to say the maximum
I don't know.
1000. No. 30
And here we're going to go
with number off
so this is going to
the coloring. So basically, this is about 30,000 and below 30,000
and ah, this
gives you the bar length gives you have we how heavy fulfilled that criteria. So we have this one. For example, 37 5000. It is higher
then. 34,000 so it can be used as a 100% and above fulfillment of some criteria. And this is why it's fully here. For example, this one is 32,382. So it's above
minimum. It's below maximum, so it's not fully filled. Those that remained uncultured are those that are below the maximum with a minimum value.
So a conditional formatting. I'm not going to explain all of the possibilities for conditional formatting here because they're quite a lot of them. The the most important thing is that you can do conditional formatting on very complex criteria
and make sure that you yourselves get the color
or your fund gets color or it gets added and I come there and make your table or worksheet or your Excel file extremely readable so that the most important things are highlighted or
painted in a way that you see them first.
This is the reason why conditional formatting is there, and it's really helpful in greeting very long and very big