0:00
hello and welcome to tutorial's point in
0:02
this video we are going to learn about
0:04
conditional formatting in Excel with the
0:06
help of real world use cases here we
0:10
have some sample data of entity name
0:12
month here Revenue operational expenses
0:16
software Sales and Service Revenue this
0:18
is a huge data of a different number of
0:21
companies and we are going to apply
0:24
conditional formatting on this one so
0:26
without wasting time let's get started
0:28
so let's say here in the the service
0:30
Revenue column we want to highlight the
0:33
service revenue of those entities which
0:38
$6,000 so what we'll do is I'm just
0:41
simply going to select this entire
0:44
column of service Revenue by pressing
0:46
control shift down arrow and the whole
0:49
column is selected and here we have the
0:52
conditional formatting option I'll click
0:53
on this one and then I'm going to
0:56
highlight sell rules and then we have
0:58
the greater than option so I'll click on
1:00
greater than and here as you can see
1:03
format celles that are greater than here
1:05
we are going to put the amount so we
1:07
want to format those sales that have the
1:09
greater than amount of
1:12
$600,000 and we format them with the
1:15
help of this green color and click on
1:17
okay and as you can see all of these
1:20
sales have been formatted that have
1:21
service revenue of greater than
1:27
$600 but there is a catch here here as
1:30
you can see for example this right here
1:33
is not highlighted it's
1:34
$600,000 so we can modify our formula to
1:39
$600 as well so I'll again click on
1:42
conditional formatting and I'll go to
1:44
manage rules and we already have this
1:46
rule I'll select this one and then edit
1:48
Rule and as you can see it says sell
1:53
$600,000 dollar so instead of greater
1:56
than we will say greater than or equal
1:58
to this will include $600,000 as well so
2:02
I'll click on okay and click on apply
2:05
and okay and as you can see
2:08
$600,000 is also highlighted now let's
2:11
say that your boss has asked you to
2:13
change this green color into some other
2:15
color so here I'll again click on
2:17
conditional formatting and then manage
2:19
rules click on it and then we have this
2:22
rule I'll select this one and then click
2:24
on edit Rule and in the edit rule we
2:26
have this format option I'll click on
2:28
this one and then we can select any
2:30
color let's say we want to fill it with
2:32
blue color I'll select this blue color
2:35
and then click on okay and then click on
2:37
okay then click here on apply and okay
2:40
and as you can see all of these have
2:42
been covered in blue color but the text
2:45
is not really visible so I'm just going
2:46
to undo my change so let this be green
2:49
color for now now let's say that we just
2:52
don't want to highlight this Cale we
2:54
want to highlight the entire row that
2:57
has a service revenue of greater than
3:01
in that case what we'll do is we are
3:02
going to select our entire data so I'll
3:05
click on this cell and then I'll press
3:07
control shift right arrow and control
3:10
shift down arrow to select all of our
3:12
data let me just scroll up quickly then
3:15
we will go to conditional formatting and
3:18
then we'll go to manage rules I'm just
3:20
going to delete this rule we are going
3:21
to write a formula from scratch so I'll
3:23
just delete this Rule and then I click
3:26
on new rule and here we are going to
3:29
select this one use a formula to
3:31
determine which cells to format now here
3:34
we are going to write our formula the
3:36
formula is we want this value to be
3:40
highlighted if it is greater than or
3:46
600,000 right now here we have $2 doar
3:49
signs we only need one why because this
3:52
means that the column G is fixed with a
3:55
dollar sign but the rows can change
3:57
because we want to highlight all of the
3:59
rows that are greater than or equal to
4:03
$600,000 now to format with some color
4:05
I'll just click on format and then just
4:08
select any color I'll select this green
4:10
one because I like it and then click on
4:13
okay then again click on okay hit on
4:16
apply and okay and as you can see we
4:19
have all of the rows highlighted that
4:22
have a service revenue of greater than
4:27
$600,000 now before ending the video we
4:29
are going to take another example so
4:31
here I'm just going to create a new
4:33
sheet and I quickly paste in some data
4:36
so we have almost same data of entity
4:38
name month year and S service revenue
4:41
and we want to highlight the top 50
4:43
companies that are performing well in
4:45
the services Revenue so how do we do
4:48
that first of all we will select the
4:49
services Revenue column by pressing
4:51
control shift down arrow let me just
4:54
quickly scroll up for your betterment
4:57
and then we'll click on conditional
4:58
formatting and in we will say top bottom
5:01
roles and then we will do a top 10 but
5:03
we don't want top 10 we want top 50
5:06
companies right so I'll go to more rules
5:09
and I will say top and here I will write
5:11
50 and we will use a color to uh to
5:14
highlight them so let's select this
5:16
green color and click on okay okay and
5:19
as you can see top 50 companies have
5:22
been highlighted now similarly I want to
5:25
highlight the bottom 50 companies the
5:27
ones that have very less Revenue so
5:29
again we are going to select the entire
5:31
service Revenue column then go to
5:34
conditional formatting and then manage
5:36
rules so we already have this for the
5:38
top 50 and now I will create a new rule
5:40
and I will say format only top or bottom
5:43
ranked values and then instead of top 50
5:46
I will select bottom and then here I'll
5:48
write 50 and we will format them with a
5:51
red color so click on okay okay apply
5:55
and okay and as you can see we have
5:58
these red color which showcase the
6:01
bottom 50 companies that have very less
6:04
Revenue so that will be enough for this
6:07
video now the practice sheet is linked
6:09
in the description for you to download
6:11
and practice so make sure to like this
6:13
video share it with your friends and I
6:15
will see you in the next one
