Imagine examining hundreds of rows of raw data in Excel in an attempt to find a
pattern or trend. You’d go mad!
Thankfully one of the tools we can use to make this task simpler is Conditional
Formatting.
Excel includes many default Conditional Formats, including colour scales, icon
sets and data bars to name a few. More on these below.
Table of Contents
Get the Practice File & Cheat Sheet
When to Use Excel’s Conditional Formatting
How to Apply Conditional Formatting
Remove Rules
Manage Rules
Guidelines
How to Use Stop If True
Custom Rules
Conditional Formatting Formulas
Get the Practice File and Cheat
Sheet
Enter your email address below to download the free files.
Get Workbook & Cheat Sheet
By submitting your email address you agree that we can email you our Excel newsletter.
When to Use Excel’s Conditional
Formatting:
If you want to be informed in real time
Answer questions visually
Analyse data: find exceptions, find relationships, find trends, etc
Enhance data presentations
You can choose from inbuilt rules like:
Top 10 or Bottom 10 using percentages, average or item
Greater than, less than or equal to
Text that contains a specific word or phrase
A date occurring
And even identify duplicate values
Or you can insert a formula and create a custom conditional format. More on that
later.
Colour coding can be simple like font colour, font style and cell fill, or more
elaborate with icons, colour scales or data bars.
Examples
How to Apply Conditional Formatting:
Select the range of cells you want formatted.
From the Home tab go to the Styles group and select Conditional
Formatting.
The menu will appear with your formatting options.
When you choose one of the options a cascading menu will appear.
Depending on which option you choose you will be prompted to make
more selections.
Note: You can specify a custom format or use one of the defaul formats.
You can choose to format the cell fill, font style, colour, size, bold, italic,
underline and more.
Remove Rules:
Click the Conditional Formatting command.
Select Clear Rules. A cascading menu appears.
Choose to clear rules from the entire worksheet or the selected cells.
Manage Rules:
Click the Conditional Formatting command on the ribbon.
Select Manage Rules from the menu. The Rules Manager dialog box will
appear as below.
You can change the list of rules displayed by selecting from the ‘Show
formatting rules for:’ list.
Guidelines:
1) Conditional formatting is, at its most simple, a format or group of formats. That
means if you copy and paste the cell you also copy and paste the conditional
format.
2) You can have more than one rule for a cell or range of cells.
3) Rules at the top of the list (as seen in the Rules Manager) take precedence.
That is, a rule at the top of the list takes precedence over any rules below it.
4) New rules are added to the top of the list by default. You can change the
order of the rules by clicking the arrow buttons in the Rule Manager.
5) If rules don’t conflict then both rules will be applied. For example; one rule
formats the font colour and the other rule is for the cell fill, both rules can be
applied.
6) If rules conflict, for example both rules format the font colour, then the
preceding rule, the rule highest in the list, will be applied.
How to Use Stop If True
You can see in the Rules Manager above that to the right of each rule there is a
check box for ‘Stop If True’.
We mentioned above that rules take precedence from top to bottom. Therefore if
you wanted to stop the formatting once a particular rule was ‘true’ you can simply
check the box beside the rule in the Rules Manager.
For example if you checked the box on the first rule and it tested ‘true’ Excel
would not continue on with the remaining rules. This feature enables you to
avoid rules that conflict by stopping them at the first occurrence that tests true.
Note: this option isn’t available for colour scales, icon sets or data bars.
Custom Rules:
Whilst the built in formats are great, from time to time you might want to do
something different.
You can specify custom conditional formats by selecting New Rule from
the Conditional Formatting Menu. The dialog box below will open.
Then select the type of rule you want, and specify your criteria in the edit the rule
description.
Note: Different Rule Description options will appear depending on which Rule
Type you select.
Most of them are self explanatory except:
Conditional Formatting Formulas
The last Rule Type in the list above is ‘Use a formula to determine which cells to
format’. Basic examples of formulas you can use here are:
=$F35>500
(row 35 is the first row in my table – see example below)
This formula will apply the conditional format to all values greater than 500.
The absolute reference for column F is instructing Excel that the conditional
format is dependant on column F. If you only used a relative reference for the
column the formula won’t work properly.
Now, if you’ve been paying attention you’re probably thinking, why would I use a
formula to format cells >500 when the ‘Greater Than’ formats are already built
into the Highlight Cell Rules menu.
Well, because if you select the whole table before inserting the rule it will
highlight the whole row like this: