Hello and welcome to our new video
In this video we are going to learn
Conditional Formatting
In excel
How to do Conditional Formatting
In last video
We have seen
Number Formatting
In both of these
One word is common and that is
Formatting, so first of all let's understand
What is the meaning of Formatting
Let me open excel
What is the meaning of Formatting
In excel
We have seen
Where did we get Number Formatting
We got it here
In number group
We have seen it here
We called this
Number Formatting
Today We are going to talk about
Conditional Formatting
Where are we going to get Conditional Formatting
Here You will see
Tab Number Group
Number Formatting
On the side of Number Formatting
Conditional Formatting
This is 2013 version
Which I am using
May be your old version
That's why I said in starting
If possible
Download new version
What will happen
You will get new features
So Conditional Formatting
You will get it here
Let's open it
If I open it
We get to see many things
Like here
Highlight cell rules
Now I know
What is cell
All my boxes
I call it cell
After that
There are many things
Between equals to
What does it mean
How to use it
After that
Top and bottom rules
After that
There are many options
Data bars
Color scales
Icon sets
New rules
Clear rules
All these things
Under Conditional Formatting
Before that
Let's understand
What is the general meaning of formatting
Suppose I selected a cell
I put any data
I am putting my name
Now we know
If I want to give
Color to this cell
Background color
Or text color
I want to give text color
I selected it
After selecting
I have not told you this
But I will tell you
When we select it
You can see it
How to do it
You click it
On which cell
You want to work
What you have to do
Select the cell
I selected the cell
After that
You can see many things
Related to font
If I want to give
Color to text
What is the meaning of text
If I want to color
What I will do
My alphabet
Red color
If I click it
Red color
We can fill other colors
From side
Down If I want to give
Other colors
I can select
Green color
Or blue color
I selected blue color
After that
I want to change
Background color
For background color
Yellow color
For text
Blue color
For background
What happened
Color of cell
Changed Or text
If I want to change
Design I can do it
If I want to bold
It will become italic
Whatever editing
I am doing
Formatting Is it clear
Formatting Conditional Formatting Conditional Condition
Condition For example
Your father
Told you
Get good marks
Then I will buy you iPhone
Father said
Condition If you get good marks
Then I will buy you iPhone
If you
Don't get good marks
Then Condition If you don't get good marks
Then you will be abused
Condition Condition Condition Condition Condition Condition
Condition Condition Condition Condition let's apply
conditional formatting
I have gone to conditional formatting and I have seen
greater than now let's apply see what is the meaning here
some data is given like in this column and if someone tell
me above 20 all the numbers what will you do you will start
counting 25, 33, 41, 48, 56 like this one by one you will
start counting but in this you don't think time will pass
I can't tell in one go it will take 10, 15, 20 seconds and
now because this data is very small if I have many numbers
I have thousands of numbers and if someone ask me then how
will I tell so here we will use smart tricks that the
feature which is given in excel we will use that so that
our work becomes easy so what we will do in this column we
will select all the cells and then where we will go we will
go to conditional formatting and I have gone to highlight
cells rule highlight means highlight a cell after this if I
go to greater than then I will see a dialog box now by
default 52 is written here
I don't want 52
I want above 20 all the numbers will be highlighted so what
I do 52 by default
I remove it and I write 20 so you can see all the numbers
above 20 are highlighted a color is given you can see light
red dark red now if you don't want this red color and you
like another color you can change the color here it is
given dark yellow light red green now the color is changed
now what else I can do if you go down custom format is also
given now what is custom format custom means you can change
it manually like this is already given light red if you
select the option you will get
red but we want the border to be thick and other than that
the text color whatever I want so for that we have to go to
custom format
I go to custom format here I can see the font style suppose
I select bold and here you can see bold other than that
color is given now I want my favorite color
I want the color like this
I can see the text and I want the border to be filled the
border color
I select yellow and I click ok so you will see that my
color is 25, 33, 41 the custom color
I selected in custom color I selected font color
I have given blue color blue color blue color and bold all
the things I have shown how I have shown
I have shown bold and blue this is clear what we can do
border border of a cell if you want to color it or try
something around it if you want to show that thing suddenly
how you can do go to conditional formatting highlight cells
rule and perform greater than things are clear till here
after this
I will remove it suppose if you have put conditional
formatting you can remove it select it again then go to
conditional formatting at last you will see clear rules
what you have to do in clear rules you will see two things
clear rules from selected cells selected cells means from
here to here
I have selected all the cells other than this clear rules
from entire sheet in sheet 1 whatever whatever rules
I have put it will remove all suppose I will show you
I went to this option and here
I did conditional formatting again I went to greater than
and I said let's take 28 by default now what I have to do
I have to select this much and go to conditional formatting
and here I do clear rules and here I do clear rules from
entire sheet you will see
I have not selected this cell
I have not selected these cells in column E what I have done
I have selected only cells in column D but the formatting
in both is gone what we have to do
I will show you again what we have to do with single cell
how to remove formatting from single column we have to
remove formatting from entire sheet
I will show you again greater than by default what I have
done
I have selected all and went to conditional formatting what
I have done clear rules from selected cells from selected
cells what we have to do we have to clear what I have done
it is clear after this less than
I have to put less than less than 10
I will put less than 10
I think less than 10 is not here and second thing less than
10 is not here because we have not selected any cell
I will remove it from here what I have done
I have selected this column and I want from 29 below
whatever value I have it will come to me
I have gone to less than after going to less than from here
you can change color
I have selected yellow and go to custom we have done it in
case of greater than what we have done from here what I
have found below 28
I can highlight
I have found greater than and less than both are clear
remove formatting clear rules from selected cells after
this what option I have after that what option we have
between what happens in between you have to select two
numbers this number and this number in between whatever
values are there for example 10 and 20
what I want
I want those cells which lie between 10 and 20 what lies
between 10 and 20 10 will come 19,18,13 which data I have
highlighted
I have highlighted that data which lies between 10 and 20
from here we can give any color we can give custom color we
have seen it before
I am not doing it if you want you can try
I am OK from here
I have shown here between 10 and 20 after that clear rules
after that next condition we see equals to how to use
equals to particular I want this number like I want 18 it
is possible that if a number is coming twice it will
highlight it twice
I repeat any number for example
I write 19 here
I wrote 19 here and 19 here what we want we want 19 I write
19 here 19 19 what will happen
I selected these three columns
I went to conditional formatting
I put here 19 when I put 19 what happened in these three
columns 19 highlight clear and clear now I am clearing
because if I put another condition so that host post is not
there and things are clear so I am clearing again and again
otherwise Khichdi will be cooked
I don't want that
I am clearing selected cells next what is next text that
contains when we go inside we have to see what is text
suppose name of students or employees
I have to find a particular person
I have to highlight that person for example when I prepared
data of Rahul
I have to highlight Rahul for that
I have to have data of text so first of all
I write data of text
I go here and I write my list here
I dragged and here
Rahul is coming twice and Imran is also coming twice
I write Rahul once more
now what I have to do
I have to select whole column I selected whole column and I
go to conditional formatting and see here text that contains
Rahul I highlighted Rahul
Rahul in which Rahul's data is available this is clear
after that what is written next is date occurring now for
date we have to make a date format so what i do i make a
date format here i make a date format here i put date and
date we know that if we want to write a date then we will
not generalize it what we will do we will select a date so
first i put a date like today is 3 February so i put
03022023 and enter so if we see by default it is taking a
date here and what i have done i increase
column and what i do i drag so here my dates are here is it
clear now what i have to do i have to apply a condition
which is given in conditional formatting
select it select it and i have to move it down because we
have to see date before 3 February so so i move it and what
i do delete it and put 03022023 till here and i drag it and
if i drag it from top
so it shows me 31 January it shows me back date date before
3 February it shows me that date now it will work properly
i select it i go to conditional formatting and what i do
date occurring and it shows me yesterday so it shows me
date before 3 February date before 3 February so it shows
me date before 3 February you can change things from here
if i do today it shows me date before 3 February if i do
tomorrow it shows me date before 4 February after this in
the last 7 days current date last 7
it shows me so it highlighted those cells after this if i
go to last week so last week because it is not last week
date before 3 February you have to take that date then it
shows me if i talk about this week it shows me this week if
i go to next week it shows me so in this way if i talk
about this month or next month so from here it can be very
useful next month it is not visible because we are going in
February we have date of March so it shows you next month
so it can be very useful that you
a data and you have to find out last week what we do like
in companies this is the entry this is the raw material and
you have to find out last week raw material so from here
select last week and all columns will be highlighted so it
will be helpful after this let's see what else is written
duplicate value so here in duplicate value we know that 19
is a duplicate value or 10 is also a duplicate value like
10 is here and 10 is here if i want to find out the data
which i have written is there any
value in that data so what i will do i selected all data
then i went to conditional formatting in highlight cells
rule i have to do duplicate value see here 10 is duplicate
value so it highlighted 19 is more than one time so it
highlighted here one more thing is unique so what is the
meaning of unique duplicate value will highlight all other
values like if i selected unique so duplicate value it
highlighted all other values in grey color it highlighted
all other values in red color is it clear
clear it let's clear it let's clear it from entire sheet
after this what is left more rules so basically here you
can do cell values greater than less than, greater than or
equals to we have not seen so you can do in that what will
happen values greater than or equals to for example greater
than 10 or equals to so it will consider 10 so you can try
all these things if you want for now let's clear it so we
know in conditional formatting highlighting cell rules
works after that let's go to top
rules so here if i select this column and here i have to
find top 10 items what is the meaning of top 10 items what
is the highest so it highlighted those items 71, 78, 86,
93, 48 so it highlighted those items after that let's clear
it and let's go to top 10 what is the meaning of top 10 it
means 1% rich so only few people come in 1% so what is the
meaning of all these cells numbers these 3 numbers lie in
top 10% all other numbers lie in 90% apart from this let me
show you bottom 10 items in
10 items so it highlighted bottom 10 items these are the
lowest values let me clear it so you don't get confused
after that if i go to top bottom rules bottom 10% will show
lowest values in bottom so it highlighted 10, 13, 10 after
that let's go to above average or below average all the
numbers given here in those numbers if i do average of all
we know that i have 5 numbers i have sum of 5 numbers and
divide by 5 so all the numbers given here i will divide by
5 so all the numbers given here i
count and divide so i will get an average so in average if
we go to conditional formatting above average suppose 50 is
an average number so all the numbers above it will show so
the average may be 41 so all the numbers after 41 will show
let's see below here i go to below average so all the
numbers below 41 will show it will highlight it is clear
after that data bars, color scales icon sets let's see let
me clear it
next thing is data bars in data bars if i select it and go
here data bars what is the meaning of data bars my data
will show in bar form like in news channels we see that bar
is highest minimum value bar is lowest in school we learn
x-axis and y-axis so we create bar on x-axis here we can
show it in different colors and here we can show it in
solid colors so if you want to present your data in this
way then see here what is the benefit of it that someone
asked you that tell me which is the
number here so you don't have to go anywhere you just have
to see this thing that this bar is the biggest this means
that my data in this cell is the highest data this is
clear, from here I will clear it after that what is the
data bar we understood everything, in more rules you can do
custom things, you can give custom color you can give
border color you don't need this much, you can use these
things after that we went to color scales now what is color
scales how to show it, see in scale form
shows like 10, 20, 19 it shows in red color and the highest
number data it shows in green color are you understanding
this thing like red, yellow, green color it will show you
the highest it will show you the lowest it will show you
the middle so you can try all these things now icon sets so
all my numbers are equals for example here 12 numbers if I
select it it divides things first this symbol then this
symbol now see this symbol in marks less than it shows this
red symbol after that suppose
highest number it shows in up arrow and in down arrow it
shows my lowest number so you can present your data like
this let me clear this thing and if you go to icon set you
can see different icons you can use these icons to show
your data so I think this conditional formatting is clear
after that there is manage rules in manage rules there is
no requirement that there are many features but all the
useful features are given here in conditional formatting if
you want to change custom like we see
custom you can change border color so I think conditional
formatting is clear we have done everything in deep we have
applied rules if you have any doubt you can play this video
again see you in next video till then keep practicing