Conditional Formatting

Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6
Video Transcription
00:00
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.
00:19
So if this sounds a little bit confusing, I'll make it very simple.
00:25
So we have some kindof weekly
00:28
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.
00:41
Then, well, we have to make it somehow look different.
00:48
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
01:00
this one,
01:03
then we'll make it red.
01:07
And then let's copy, form it throughout
01:14
the whole range.
01:17
It's just by right clicking on this
01:19
here. Yeah, this is what we got
01:23
and Dr Drag dropping it to the end of the corn,
01:29
and we got all the situations in which we are below quota.
01:34
We wanted to be read so that we know we failed,
01:38
and it's a simple as that.
01:41
Now let's, uh, copy this
01:44
table
01:46
right here,
01:48
just the will use.
01:51
And, uh, let's look further into possibilities off
01:57
conditional formatting. So, for example,
02:00
let's go here and the highlight cell. So let's say that this is greater than
02:07
okay, 32,000 for example.
02:10
That means that it's
02:14
extremely difficult to achieve or it's it's a heavy quota. So well, simply put the ability
02:25
bread text or whatever. In this case, I'm going to go with custom format. So I wanted to be bold
02:34
and ah, I want Phil off the
02:39
the whole cell to be light green, for example, No
02:46
light yellow.
02:49
And, uh,
02:51
yeah, leave it like that. And then let's drag drop it to the end. Right click off course.
02:57
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.
03:06
Well, now have
03:07
ah, Yello. These cells
03:10
and ah then go further more.
03:16
And let's say that we want these cells where we are
03:21
better than quota
03:23
to be blue.
03:23
So we go, here we
03:27
go. Here we click on greater than we click on this cell here
03:32
and let's say, uh,
03:35
we go custom red form it. And we wanted to be
03:39
blue,
03:40
and we just likened this and then we expand
03:46
Phil former thing.
03:49
And now this is it. So these are the things that you can do with simple
03:55
pretty find for months of cells.
03:59
Now we can go further than this.
04:01
We can make
04:04
more conditions for former thing, and I will show you how to do it right now.
04:12
Okay, let's do another copy of this
04:15
range
04:16
copied here. We'll just pays the failures.
04:20
Sorry, we copied
04:23
just one.
04:25
So let's base the Williams.
04:28
And then let's look at some of these. So, for example, we have data bars
04:33
and if we select thes cells here into conditional formatting and weakened ate the bars
04:43
and for his own political blue one
04:46
eso
04:46
Among this set of cells of group of cells,
04:50
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,
05:11
it will look a TTE the group of cells and say OK, this fund, for example, is very low,
05:16
so it has much shorter blue bar. Ah, it, uh,
05:24
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,
05:34
if where we go here, for example,
05:39
Anglican, I consents
05:42
so we can add this
05:44
and this one thes these air showing if the well you is close to the average or below the average or
05:56
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.
06:11
There is more. And to this and let's go, for example, here and now we can do
06:20
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.
06:40
Let's go for a top 10%.
06:43
And, uh, yeah, it's like this. So this cell is stops 10% off the range,
06:49
but if we select again
06:53
and undo the former thing and now go to
06:58
Doc 10 items
06:59
we get.
07:02
So these are the best, These air top 10.
07:06
And this is how we can highlight the best performers in some some
07:15
competition or something like that.
07:17
Um, let's do another copy
07:21
and
07:23
let's
07:26
do one more thing.
07:28
And that is
07:31
new rule.
07:34
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,
07:46
it wouldn't up 10. It will be highlighted green or something like that,
07:53
Um
07:54
so we can do the 1st 1 which is format cells based on their values. And let's select a range
08:01
and let's a conditional former thing, and now you roll and form. It sells based on their well, yours
08:11
and the format style can be some kind of data bar, for example,
08:16
Ah, and, uh,
08:20
minimum can be defined. Men usually like the lowest value, for example, off the range
08:28
and maximum would be
08:31
something else, so it can be automatic or
08:35
whatever, but in this case, we're going to go with number,
08:39
and we're going to say the maximum
08:41
is
08:43
I don't know.
08:46
34
08:48
1000. No. 30
08:50
3000.
08:54
And here we're going to go
08:56
with number off
09:01
30,000.
09:05
Yeah.
09:07
And, uh
09:09
so this is going to
09:11
give us
09:18
the coloring. So basically, this is about 30,000 and below 30,000
09:24
and ah, this
09:26
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
09:37
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
09:56
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.
10:07
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
10:26
and make sure that you yourselves get the color
10:30
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
10:46
painted in a way that you see them first.
10:50
This is the reason why conditional formatting is there, and it's really helpful in greeting very long and very big
10:58
the sheets.
Up Next
Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By