Apply Conditional Formatting to the Whole Row

I have been asked many times during my career if it is possible to apply conditional formatting to the whole row rather than an individual cell. Well the answer is yes (otherwise this would have been a very short hub), and this hub will explain how to do it.

For this example, we have a list of members with their names and expiry dates in a worksheet. We would like to format a member in red when their membership expires. The range of cells holding the member details is A1:D19.

Let's set it up. Instructions for both Excel 2003 and Excel 2007 are provided.

Entering a formula for Conditional Formatting
Entering a formula for Conditional Formatting

In Excel 2003

  1. Click Format > Conditional Formatting
  2. Click the list arrow and select Formula is
  3. In the box type =$D2<TODAY()
  4. Click the Format button
  5. Click the Patterns tab and select a red colour
  6. Click Ok

In Excel 2007

  1. Click the Home tab on the Ribbon
  2. Click the Conditional Formatting button and click New Rule
  3. Select the Use a formula to determine what cells to format
  4. In the box type =$D2<TODAY()
  5. Click the Format button
  6. Click the Fill tab and select a red colour
  7. Click Ok

Conditional Formatting applied to the whole row showing expired dates
Conditional Formatting applied to the whole row showing expired dates

The formula we entered used a mixed reference. The column was fixed, but the row remained relative. This ensured it applied itself to the remaining cells in Column D.

The TODAY() function was used to calculate today's date, so that the formula will work just as well tomorrow, next month or 3 years time as it has today.

Related Links

Use Conditional Formatting to highlight duplicate records

One of the many improvements in Excel 2007 was its greater ability to deal with duplicate records. Conditional formatting can now be used to highlight duplicate records for your attention.

Software training videos

Online training videos for Excel, Word, PowerPoint, Photoshop, Dreamweaver, Flash, JavaScript and much much more..

Microsoft Excel training guides

Online Microsoft Excel training guides and quizzes. Improve your existing skills and learn new Excel tips.

More by this Author


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working