Mathew Cropper


Mar 2013 {Tools}

Before I nuked my original blog one of my most popular posts was a step by step guide to using a rule to apply conditional formatting to an entire row if a condition in one cell is met. I thought it’d be good to re-post something similar here.

The screenshots below are taken from Excel for Mac, but the same steps apply in the Windows versions. It just looks a little different.

  • Here’s the data we’ll be playing with today. Santa has asked me to apply formatting to this year’s list so that any naughty children are in red.Step 1
  • Highlight the data you want to format and open up the ‘Conditional Formatting’ dialog box.Step 2
  • The format of the formula is broken up into a few sections.=$C4="Bad"
    • The opening = tells Excel that this is a formula
    • $C tells it that the data it will be evaluating is always in column C by using the dollar sign
    • 4 tells it that the first row of the evaluation is on row 4 of the sheet
    • ="Bad" is the condition it will check.
    • The dollar sign is important. In that blog post you’re looking for the mixed cell reference explanation, but all of them are good to know if you’re unfamiliar.

    Step 3

  • Set your preferred formatting, and then apply the rule. You should then get something like this:Step 4

And there you have it. If my explanation isn’t the clearest, hopefully the screenshots give you enough to go on. Pop any questions into the comments!