Highlight Rows in Excel that Meet a Criteria

Microsoft Excel is one of the most widely used reporting tools by Planners and Schedulers and it is no surprise that most Planning & Scheduling software have facilities to export to Excel or to generate reports in Excel. This quick tip, will show how to highlight rows in an Excel worksheet that meet a criteria.

This quick tip assumes you know how to apply Conditional Formatting in Excel therefore assuming we have an Excel file with schedule data (P6 schedule export) as shown below.

And a Project Manager has requested that we should highlight activities with finish dates (column F) between 01-Feb-2011 and 31-May-2011. We can easily do this by using the following Conditional Formatting formula with a yellow fill.

=AND($F2>=DATEVALUE("01/02/2011"),$F2<=DATEVALUE("31/05/2011"))

Note the mixed referencing style ($F2) used in the first cell of our Finish column. This absolute column and relative row reference is key to highlighting rows that meet the set criteria.

Next, we need to apply this formatting across the whole data range, i.e. $A$2:$F$22, as shown below.

And we will have all rows that meet this criteria highlighted in yellow.

Thanks and hope you find this post useful.