I get asked this one a lot! How do I highlight an entire row when a particular cell contains a certain value? For example, if the total sales for the year are under $2,000, highlight the row in red. The secret is to use a formula to determine which cells to format. In fact, that is what the option is called.
1. Select all cells which might potentially be highlighted, remembering that we want to see the whole row highlighted.
2. From the Home tab and the styles group, choose the Conditional Formatting button.
3. Choose New Rule.
4. From the dialog box that appears, choose Use a formula to determine which cells to format in the Select a Rule Type: section.
5. In Format values where this formula is true:, enter a formula that will test for the desired results. In our case it would be =$G2>2000. Notice that the absolute cell reference symbol is only on the column. The row will change each time, but the column will always remain the same.
6. Click the Format button and set the desired formatting.
That's all there is to it!
If you have used conditional formatting in the past, you already know that as values change the conditional formatting responds accordingly.
Join us on April 12, 2011 for our Advanced Excel webinar for more great tips on working with Excel.