Tyler Durden's Fight Club Blog

Conditional formatting is an Excel feature you can use when you want to format cells based on their content. For example, you can have a cell turn red when it contains a number lower than 100. But how do you highlight an entire row?

If you’ve never used Conditional Formatting before, you might want to look at Using Conditional Cell Formatting in Excel 2007. It’s for an earlier version of Excel, but the interface really hasn’t changed much.

But what if you wanted to highlight other cells based on a cell’s value? The screenshot above shows some codenames used for Ubuntu distributions. One of these is made up; when I entered “No” in the “Really?” column, the entire row got a different background color. To see how this was done, read on.

The first thing you will need is a simple table containing the data you’d like to format. The data doesn’t have to be text-only; you can use formulas freely. At this point, your table has no formatting at all:

Now it’s time to format your table using Excel’s “simple” formatting tools. Format only those parts that won’t be affected by conditional formatting. In our case, we can safely set a border for the table, as well as format the header line. I’m going to assume you know how to do this part. You should end up with a table looking like this (or maybe a bit prettier):

And now we come to the meat and potatoes. As we said at the outset, if you’ve never used conditional formatting before, this might be a tad too much to begin with. Read our earlier primer on the subject and once you’ve got that down, come back here. If you’re familiar with conditional formatting, let’s forge on.

Select the first cell in the first row you’d like to format, click “Conditional Formatting” in the “Styles” section of the “Home” tab, and then select “Manage Rules” from the drop-down menu.

On the “Conditional Formatting Rules Manager” dialog box, click “New Rule”.

In the “New Formatting Rule” dialog, click the last option, “Use a formula to determine which cells to format”. This is the trickiest part. Your formula must evaluate to “True” for the rule to apply, and must be flexible enough so you could use it across your entire table later on. Let’s analyze my sample formula.

=$G15 – this part is a cell’s address. G is the column which I want to format by (“Really?”). 15 is my current row. Note the dollar sign before the G – if I don’t have this symbol, when I apply my conditional formatting to the next cell, it would expect H15 to say “Yes”. So in this case, I need to have a “fixed” column ($G) but a “flexible” row (15), because I will be applying this formula across multiple rows.

=”Yes” – this part is the condition that has to be met. In this case we’re going for the simplest condition possible – it just has to say “Yes”. You can get very fancy with this part.

So in English, our formula is true whenever cell G in the current row has the word “Yes” in it. Do not close the “New Formatting Rule” dialog box yet.

Next, let’s define the formatting. Click the “Format” button on the “New Formatting Rule” dialog box. On the “Format Cells” dialog box, go through the tabs and tweak the settings until you get the look you want. Here we’ll just be changing the fill to a different color on the “Fill” tab.

Once you’re got the desired look, click “OK” on the “Format Cells” dialog box. You can now see a preview of your cell in the “New Formatting Rule” dialog box.

Click “OK” again to return to the “Conditional Formatting Rules Manager” and click “Apply”. If the formatting of the cell you selected changes, that means your formula was correct. If the formatting doesn’t change, you need to go a few steps back and tweak your formula until it does work.

Now that we have a working formula, let’s apply it across our entire table. As you can see above, the formatting applies only to the cell we started off with. Click the button on the right side of the “Applies to” field and then drag the selection across your entire table (except for the headings).

Once done, click the button next to the address field to get back to the full “Conditional Formatting Rules Manager” dialog box or press “Enter”. You should still see a marquee (or selection) around your entire table, and now the “Applies to” field contains a range of cells and not just a single address. Click “Apply”.

Every row in your table should now be formatted according to the new rule.

That’s it! Now all you have to do is create another rule to format rows that say “No” (there was never an Ubuntu version called Chipper Chameleon, and we think that’s a shame). If your data is more complex, you may need to set up even more rules. Follow this method and in no time you’ll be creating intricate spreadsheets with data that pops right off the screen. Feel free to post screenshots of your creations in the comments!