Conditionally formatting a row based on the value in a single cell

The title of this post is probably longer than the post itself ;-)

I’ve already mentioned that I use excel to manage task lists from time to time. Sometimes reality catches up and a task is invalid before you get to it. You could obviously remove the task from the list, but I prefer to keep a reference of the task and why it became invalid. Instead I set the value of my status column to “Cancelled”. (Other possible values are: “Not started”, “Ongoing”, and “Done”.)

Now in case a task gets cancelled I want to apply a strikeout effect to the entire row, not just that one cell. This is not to difficult to establish, but I always forget about one of the details and need to look up an old copy. Hence I’m writing this post “for future reference”, hopefully somebody else out there finds it useful.

Have a task list at hand and select the rows in it:

Office 2010 Ribbon > Home tab > Conditional formatting > new rule

Select “Use a formula to determine which cells to format”

The formula syntax needs to be along the lines of:


Click the “format” button and check the “strikeout” effect:

The end result:

Now go ahead and try to make a rule that sets a green background for tasks where the status is set to done:

Things that usually go wrong (it’s in the details):

  • Typing the wrong cell number; you need the row number of the first row in the area you selected
  • Forgetting about one of the equal sings or the quotes
  • Not specifying the formatting to apply
  • Incorrectly selecting the area the formatting needs to apply to

Tagged with:
Posted in Office
IT Pro Summer GP
IT Pro Summer Grand Prix

IT Pro Summer Grand Prix

Microsoft Virtual Academy

MS Virtual Academy

Pro-Exchange / Pro-Lync / Pro-Office365

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 158 other followers

%d bloggers like this: