Excel Consultancy JPL looks at Conditional Formatting (CF)
This week’s excel consultancy hints and tips focuses on a topic in Excel which has great variety from a simple data bar across a cell to highlighting whole rows in colour based upon a formula. This topic is conditional formatting!
Conditional formatting based upon numerical rules
It is possible to use Conditional formatting to highlight cells which meet a certain condition. For example, if cells in a range are less than 250 make the cell colour green or else red.
See the workbook for an example – Conditional Formatting by Colour
Conditional formatting based upon using a formula
The example below is based upon a purchase accounts ledger. If the invoice is early, then its designated by a letter E in column H. By selecting the whole worksheet and applying CF by formula you can highlight each row where this is the case. To understand the formula, delve into Managing the rules in CF.
See the workbook for an example – Conditional Formatting by Formula
CF is a really useful tool and is often one that so many do not know about, but once they do cannot imagine not knowing about it anymore! It is often a tool that can make a job a lot easier and save you a lot of time in work! It is a very visual way of displaying information which can make displaying your data even easier for you.
We begin covering the topic of CF in our Basic Excel training course where we look at the topic based upon numerical rules. The CF rules using formulas is looked at in our Master Class Excel Bronze training course as it is a much more advanced feature and so it is best for those who already have a knowledge of CF as well as a very good strong knowledge of formulas.
Take a look at our website
For details of our Excel Consultancy training, please take a look at our website Microsoft Excel Training or email us with any queries on johnlegge@jplcomputer.co.uk.
If you liked this hint and tip, did you see our last hint and tip on SUM, SUMIF, SUMIFS, SUM Indirect?