Today, we’ll discuss the basics of Conditional Formatting and how to use a conditional formula with it.
[Intro Video]
Hi, this is Mike Negami, Lean Sigma Black Belt.
Recently one of my friends asked me a question about Excel's Conditional Formatting.
By the way, if you have any questions or video requests, please write them in the comment
section below.
This is a simple data analysis example.
In this table, the numbers are just lined up.
On the other hand, in the next table, by using Conditional Formatting in the ‘Gross Profit
Increase’ column, the numbers below 0 are highlighted in pink.
This helps us to find the next action to take.
This is called making a data analysis ‘Actionable’.
There is always a purpose in data analysis.
You should clarify it and make your data analyses actionable all the time.
I made a video earlier about this topic.
Please click on the link above and watch the video.
You can make your data analysis actionable very easily with this Conditional Formatting.
If you haven’t used it until now, you are missing some low-hanging fruit.
Please start using it today.
This example shows customers' Sales, Gross Profit, and Gross Profit Increase for the
last two months.
We would like to find customers with a declining Gross Profit and take countermeasures quickly.
Let's walk through this and set the Conditional Formatting to highlight the cells whose increases
are less than 0.
First select the range of cells where you want the Conditional Formatting, then select
‘Home’, ‘Conditional Formatting’ and ‘Highlight Cells Rules’ and ‘Less Than’.
On the next screen, enter ‘0’ here and click ‘OK’, then it’s done.
Conditional Formatting can be used so easily.
This Excel version is Excel 2016, but if you use 2007 or newer, this process is almost
the same.
These ‘Data Bars’ are also very useful.
For example, let’s select the This Month’s Sales column in Column D and choose any favorite
color of ‘Data Bars’, then you can visualize the differences in the sales among the others
very easily.
As you can see, there are many kinds of ‘Conditioning’ and various ‘Formatting’.
Which one do you think I use the most?
This one, ‘Duplicate Values’.
This will highlight all duplicated-value cells within the selected range.
In practice I often use it when there are many rows of data and I want to make sure
that there are no duplicate values.
I’ll show you what I mean.
Just select a range and choose ‘Duplicate Values’ and click ‘OK’.
Click that column’s Auto Filter and see ‘Filter by Color’.
If there is no pink color there, there are no duplicate values.
If a pink color shows and you select it, you can easily see which values are duplicated.
Until now, the cells that the conditions refer to and the cells that you put formatting in
were the same.
Now, if you want to change the formatting based on other-cell values, how could you
do it?
In practice, you’ll have this case more often.
Let's do it now.
Suppose you want to highlight entire rows up to the customer names with the same condition
as in the previous example.
First let's delete the conditional formatting we made.
Select the cell you’ve set it in and from ‘Conditional Formatting’, choose ‘Manage
Rules’.
Your previous formatting is recorded.
Select it and click ‘Delete Rule’, then click ‘OK’.
It’s now deleted.
Select all the columns in the table and choose ‘New Rule’.
There are many types of rules, but I always use this one: ‘Use a formula to determine
which cells to format’.
Put a formula in the text box above for ‘What condition’ and set ‘What formatting’
you want in the below section separately.
At first, always put an ‘=‘ in the conditional formula.
My condition of ‘Gross Profit Increase’ in Column F is below 0.
Since F3 is the top row in the selected range, enter ‘=F3<0’.
Then click ‘Format’ to set what formatting you want to set when your condition is met.
In addition to the previous ‘pink’ fill, you can change the ‘Number’ format, the
style and color of the ‘Font’ and ‘Border’.
In this example, click ‘Fill’ and choose any color you like, then ‘OK’ and ‘OK’.
However, the result has not come out as I expected.
This is because the conditional formula is not right.
Let's edit the Conditional Formatting we just made.
Choose ‘Manage Rules’ and select it and click the ‘Edit Rule’ button, then you
can go back to the same screen.
Here you need to use ‘Absolute and Relative Cell References’.
I made a video about this, so if you are not familiar with it please watch the video first.
This ‘F3’ doesn’t have a dollar mark, so it’s treated as a Relative Reference.
Since the selected range is from Columns A thru F, the conditions of the cells in Column
A are referring to Column F, and when they are less than 0, the color appears properly.
However, The conditions of the cells in Column B refer to Column G and ones in Column C refer
to Column H because they are Relative References.
Since the referred cells are all blanks and the values are 0, they’re NOT below 0, therefore
they don’t show the color.
The solution is simple, and you only have to change the Relative References to Absolute
References.
Just put a dollar mark in front of F in the conditional formulas.
Please do not put a dollar mark in front of the 3 because when you copy the cell and paste
it below, you need Relative References.
See the result.
It went well.
You can put Excel functions and multiple conditions in conditional formulas too.
Suppose the two conditions are A and B. When you want your condition as ‘A and B’,
you write ‘AND(A,B)’.
If you want your condition as ‘A or B’, you write ‘OR(A,B)’.
For example, let's add another condition that this month’s sales are 5,000 or more in
addition to the previous condition.
Put ‘AND(’ after the equal, and a ‘,’ after the previous condition.
This month’s sales are in Column D and this should be an Absolute Reference, so put $D3
and put ‘>=5000’ and close the parenthesis.
Press OK and OK.
Now only Customer D satisfied the two conditions.
This means ‘the customer is large and the gross margin from them is declining.
We need to deal with it immediately.’
The ‘Absolute and Relative Cell References’ in the 2nd half may have been difficult, but
even if you only practice the contents in the first half, the quality of your data analysis
will improve considerably, so please start using this Conditional Formatting from today.
Thank you very much for viewing.
Please click the ‘Subscribe’ button.
Also click and watch my other related videos.
Thanks.