In the next two lectures we're going to take a look at Excel's conditional formatting.
So until now, we took a look at basic Cell Formatting, we looked at
number formatting.
Now we're gonna see how we can influence the Cell Formatting based on a condition. Excel has a lot of
easy to use inbuilt conditions.
Let's take a look at these two in this lecture. And we're going to take a look at data bars and icon
sets in the next lecture.
First off, what is conditional formatting?
I've actually used this throughout all the workbooks you've been using on the index page. Whenever you
come to select an option from here.
So let's say, you say, I've done this and I understand this.
These cells turn green.
If you select some other option like I want to review this later, they turn another color.
This uses conditional formatting. To get to it and to see what type of rule I'm using here.
You need to go and manage the rules.
If you already have rules inside, you can go and manage them or edit them. And you see the different
rules that are used here. To edit them
you can click on it and take a look at the type of formatting that is used.
Now in our example we're gonna start from scratch.
So let's go to our conditional tab.
Let's see some of these in-built functions that we have.
Let's say for yearly salary
I want to highlight the cells that are greater than a specific value.
So I'm going to go with this. Here I can directly input a value or I can use a cell reference.
If I have some number sitting in a cell, I can reference it or let's just type something in.
I'm going to go with a hundred thousand and then I can decide on the color that I want.
So you have some built in custom formatting in there to make it easier for you. If you don't like any
of these built in ones you can go to a custom format and decide on the fill color that you want,
the font color and so on. And then click on
Ok.
Let's go back with this in-built one, click on OK, that's our formatting. So these were the numbers
that are greater than 100,000.
If I change this one to 90,000 press enter, it's not highlighted anymore.
That's the beauty of using conditional formatting.
So in addition to this we had other options. We can do less than, between, equal to, if a text contains
a certain word, duplicate values, and so on.
So for our dates for example, let's go and see who started to work between the first of January 2018
to first of January 2019 and decide and the formatting and then click on OK. And that's our dynamic
formats.
If for some reason you want to go and change that formatting or clear the formatting, you do it from here.
First highlight the area, go to clear rules and clear the rules from either the selected cells
or from the entire sheet. Entire sheet means any conditional formatting you've used anywhere on this
sheet will be deleted.
In this case I'm going to go and clear it from the selected cells. If you want to go and update the existing
conditional formatting, you go to manage rules. You can see the existing formatting used. You can click
to edit it, completely change the conditional formatting to another one. Or, you can add a new rule to
this.
You're not restricted to just one rule. You can select the cells that are greater than one hundred
thousand and format them this way. But you can also add a new rule and go with format only cells that
contain, if the cell value is less than, let's say thirty thousand.
We want to format these in a red color, click on OK, and click on OK. And we see the two rules here. Click
on Apply, if you like what you see, click on OK.
Other useful options are to do a top bottom type of conditional format.
So first off, let's go and clear the rules from the selected cells
and let's go and highlight the top 10 items or the top 10 percent.
Let's go with top 10 percent actually.
I want highlighted in green
and click on OK. In addition to this I want to highlight the bottom 10 percent.
The red fill is ok.
Click on
OK.
And that's all dynamic.
So it takes a look at this dataset and from this data set
It computes the top 10 percent and the bottom 10 percent and highlights them in a dynamic way. So if you take
the 185,000 and and we change this to 85,000 that disappears from
the top 10 percent.
If we do the same for this one, that disappears. And this one, our top 10 percent changes to these values.
Okay so that's how easy it is to use conditional formatting.
The one thing you need to keep in mind is that when you copy cells that have conditional formatting
behind them you will take the conditional formatting with you.
So be aware of this.
So if I copy this area and paste it here and now let's go to conditional formatting options, I see the
rules in there. And I may not want this.
So don't forget to go there and clear the rules from the selected cells. If you want to copy this without
conditional formatting, just copy,
go here,
Paste special, paste the formulas and Number Formats but not the conditional formatting.
Okay.
In the next lecture let's take a look at how we can use conditional formatting together with data bars
and icons to bring attention to specific data points.