Formula Conditional Formatting in Excel – using formulas to apply it

Formula conditional formatting in Excel: Excel icon

Formula Conditional Formatting in Excel – how can I use formulas to apply conditional formatting in my worksheets?

This week’s hint and tip is about formula conditional formatting in Excel and how you can use it in your worksheets. This is covered in our Master Class Excel Bronze course but we also decided to do a hint and tip on it. We are going to go through it now below.


What is it?

Conditional formatting is a feature within Excel that allows you to have formatting applied to cells based on certain conditions you set. It is a very useful feature and can be used to highlight patterns in data.

In this hint and tip we explore the use of Excel’s conditional formatting feature using a formula. In an earlier YouTube video produced last year with a blog, we looked at this feature but through using cell values whereas here we are going to use a formula.


Conditional formatting using a formula

The video below explains it all together with the attached worksheet but 2 points are critical:

  1. You must select the whole rows in order for that whole row to be filled with a colour
  2. The formula looks like this =$e2<today() This is saying if the expiry date in column e (the whole of column e as indicated by the $) starting at row 2 is less than today’s date then it meets the rule to be filled red

This feature is also explained in more detail in our Master Class Excel Bronze course.

For more Excel hints and tips visit our hints and tips page here.


The video below shows you how to actually use this feature through using a formula in a spreadsheet. We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more and then try it out on your own computer!

Click here to download the example spreadsheet shown in the video to try it out yourself!

We hope you have enjoyed this hint and tip on formula conditional formatting in Excel. Why not take a look at our previous one on green triangle errors in Excel​?

Please visit our YouTube Channel directed from our website or via this link for more videos.

Cookie Control

Cookie control

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

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

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