Conditional Formatting

Resolved · Urgent Priority · Version 2016

Fri 9 Apr 2021, 10:37 Delegate Manisha said...

Manisha has attended:
Effective Communication Skills course
Effective Communication Skills course
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

Mon 12 Apr 2021, 00:26 Trainer Dennis said...

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.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Create a hyperlink navigation sheet

In large files, it is often useful to have a front sheet with hyperlinks to the key databases and summary calculations in your spreadsheet. Hyperlinks can save you and (more importantly) those less familiar with your spreadsheet a great deal of pointless scrolling between and within sheets.

Hyperlinks appear as underlined text and can jump to any cell or range name in your file. You can also use hyperlinks to jump to other files.

To create a hyperlink to a location in the active workbook: (1) Select the cell that contains the text you want to use as the hyperlink and choose Insert|Hyperlink.(2)Click Place in this document.(3)Choose the sheet you want to link to or the range name from the list of "Defined Names".(4)If necessary, type the cell reference in the Type in the cell reference box. (5) Click OK.

View all Excel hints and tips