I use conditional formatting in a lot of my spreadsheets, mainly to show me when dates are coming up, e.g. when a training date is less than a week away, or if an invoice is overdue etc.  Most of the time I use it to format individual cells (see my earlier tutorial on how to do this), however a client recently asked me how to highlight the entire row that contains that cell. This tutorial shows you how 🙂

In the example below we want to highlight the entire row where the cost (column I) is less than €25.00…

We first select the entire range of data we want to format, excluding the column headings (in this example A4:I16)…

Go to the Home tab on the ribbon, click Conditional Formatting, then click New Rule…

The New Formatting Rule dialogue box appears.  In the Select a Rule Type section, choose Use a formula to determine which cells to format…

In the Format values where this formula is true field, we need to create a formula that evaluates to either TRUE or FALSE. For cells where this formula evaluates to TRUE, conditional formatting will be applied.

We want to highlight the rows where the value in column I is lower than 25, therefore we need to type in the formula =$I4<25. We want to ensure that the conditional formatting for all cells in the selection refers to column I, so we use absolute ($) column references.

We then need to choose the formatting to apply to rows where this formula is TRUE. Click Format… then click the Fill tab, choose a background colour and click OK. In this example I’ve chosen red…

We can then see a preview of the cell formatting…

Click OK and we’ll now see entire rows filled in red where the cost in column I is less than 25…

That’s it! I hope you’ve found it helpful 🙂