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.
- Highlight the data you want to format and open up the ‘Conditional Formatting’ dialog box.
- The format of the formula is broken up into a few sections.
- The opening
=tells Excel that this is a formula
$Ctells it that the data it will be evaluating is always in column C by using the dollar sign
4tells 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.
- The opening
- Set your preferred formatting, and then apply the rule. You should then get something like this:
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!