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.
The Conditional Formatting features in Excel 2007 are massively improved and now include many default Conditional Formats, including colour scales, icon sets and data bars to name a few. More on these below.
Before you read on, download the practice workbook here.
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.
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.
- Click the Conditional Formatting command.
- Select Clear Rules. A cascading menu appears.
- Choose to clear rules from the entire worksheet or the selected cells.
- 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.
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.
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:
Here's a tutorial on how to use formulas to set Conditional Formats.
For Extra Credit add Filters to your data and use the formatting as your filter criteria.