Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

conditional formatting

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting

Conditional Formatting

ResolvedVersion 2016

Manisha has attended:
Excel Intermediate course

Conditional Formatting

Hi,

Could you please tell me how I can conditional format the below scenario.

I have two date columns (Column D & E), and a percentage completed column (Q)

If there is 30 days remaining on either side of the date in column E, and the percentage completed is over 80% I would like that column to be green.
and then less than a month but between 21% to 79% would be amber, and less than a month and below 20% would be red

RE: Conditional Formatting

Hi Manisha

Thanks for your enquiry.

You need to use the Formula option with Conditional formatting. Any formula you the answer must result in a TRUE or FALSE result. You will create 3 separate test.

Using the AND function will allow you to have multiple criteria and the final answer will be either TRUE or FALSE. Because you have multiple tests you will need to select the option to STOP WHEN TRUE on the conditional format so just one rule is applied.

Rule 1
GREEN =AND((Col E - Col D) >= 30 , % Complete >= 80%)

Rule 2
AMBER =AND((Col E - Col D) <= 30 , % Complete >= 21%, % Complete < 80%)

Rule 3
RED =AND((Col E - Col D) <= 30 , % Complete <= 21%)


I hope this helps. Please get in touch if you need any further assistance.

Kind regards

Dennis

Sun 18 Apr 2021: Automatically marked as resolved.

Excel tip:

Display Functions on Worksheets

Functions in Excel can be difficult to recall their format/syntax

For example, you want to use the =PMT function.

Enter =PMT, then select keystroke, CTRL+SHIFT+A.

This usful memory jog, will display the arguments of a function on a worksheet, allowing the user to proceed with the generation of the function



View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.