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 course where we look at the topic based upon numerical rules. The CF rules using formulas is looked at in our Master Class 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?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy