How to Create Custom Conditional Formatting Rules and Edit Existing Rules in Microsoft Excel

In this lecture, I am going to talk about how you can create custom conditional formatting
functions, or how you can modify existing ones.
So like always you need to select the range and then conditional formatting, and here
you can see new rule.
And here I will go through these briefly in just a minute because firstly, I want to show
you another way to access this menu
Is if you press conditional formatting, manage rules, new rule, and as you see, the same menu
pops up.
So, here you have different types of rules, so firstly; format all cells based on their
value, and then here you can change the different types.
So firstly, 2 color scale, and here you can type the minimum and maximum values.
Either by values, or by pressing here, and select the cell, or hard-coded values, three
colors.
Then you can select the midpoint, by default it selects the 50th percentile, but you
can change that.
Data bars, also, note that you can change the colors on all of these and icon sets.
And as you can see, the menu is always changing.
The next option you have is “Only cell that contains” and then you can type, again,
hard-coded values numbers, or cell references.
And then here you can use values, specific text, dates and all of these.
And here you can use between or between equal
to, not equal to, etc., and just change the format how you want the text wherein the condition
applies to look like.
The next one is “Format only top or bottom ranked values.”
This one is very simple, this is top or bottom rules which we looked at previously, and then,
here is select top or bottom and then the values or percentages.
“Format only values that are above or below average”, again very simple, above or below,
equal or above, equal or below and then in case you have done math or statistics, you
will know standard deviation.
If you need some statistical analysis, you can select how many standard deviations should
be highlighted which is quite useful because you can't have these, I don't think originally in the
menu.
For statistical work, this is extremely useful.
“Format only that have unique or duplicate values”, there might be different reasons
for it, but very simple menu; duplicate or unique.
And here I will probably be talking the most about this function in the future lectures,
because here you can use a formula to determine which cells to format, and this
is extremely powerful.
And we will go through some examples in the future lectures.
For now I will press cancel and close.
Actually, when you will have some rules, I will just quickly demonstrate.
You can click edit rules to modify existing rules.
So for example; I will highlight cells greater than let’s say 70
So manage rules, and here you have where it applies; “Edit Rule”, and then you
can see this menu where you can edit the condition based on which it formats the cells.
So see you guys in the next lecture

How to Create Custom Conditional Formatting Rules and Edit Existing Rules in Microsoft Excel

Latest Posts