• ARTICLES
SEARCH

How-To Geek

How to Highlight a Row in Excel Using Conditional Formatting

image

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 one version back, 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 different background and font colors. To see how this was done, read on.

Creating Your Table

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:

image

Setting The Look-and-Feel

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):

image

Creating The Conditional Formatting Rules

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 and try 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 Home tab) and select Manage Rules.

image

 

In the Conditional Formatting Rules Manager click New Rule.

image

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.

image

Next, let’s define the formatting. Click the Format button. In the Format Cells dialog, 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.

image

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

image

Click OK again to get back to the Conditional Formatting Rules Manager and click Apply. If the cell you selected changes formatting, 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.

image

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 next to the Applies to field and drag the selection across your entire table.

image

Once done, click the button next to the address field to get back to the full dialog. You should still see a marquee around your entire table, and now the Applies to field contains a range of cells and not just a single address. Click Apply.

image

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

image

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!

A technical writer for Tibbo Technology by day, Erez is obsessed with customizing anything and everything. After years of using Litestep and Blackbox, switching to a custom keyboard layout (Colemak), extending Word and Excel with elaborate VBA, losing weight with an AutoHotkey script he developed and spending countless hours tweaking Foobar2000 to get it to look "just right", Erez decided the time has come to share some of this obsession with the world at large.

  • Published 03/15/11

Comments (13)

  1. PTR

    In you last table, shouldn’t the entire area be green? Looks like row 16 is not.

  2. Luc Schots

    Actually, no – cell G16 has a ‘NO’ entry, so the formula evaluates to FALSE – no special formatting

  3. Steve

    This is really neat. I do a lot of simple spreadsheets and I can see where using this would be very useful to the users. Now I just need to see how I can expand on this little bit. Keep ‘em coming. Thanks.

  4. Oz

    This is neat and useful. Keep it coming and may be expand to the next level. How about something on ‘what if’ analysis?

  5. Michael

    Great trick. I note that in my copy of Office 2007, the formula must test the first occurrence of the value in a column to work with a single cell test.

    It also works if the formula tests a range, such as:
    =$G2:$G100=”yes”. Testing a range works better in my thought process…

    Please keep writing tricks at this level. How about something on vlookup? Streamlined if/then analysis?

    Onward!

  6. Ian

    Thanks for the tips..
    I use conditional formating all the time.
    One of my favourite tricks is to format on financial data, so that any negative amounts automatically change to red font. Makes it so easy to work with and manage the data.

  7. Conyers

    Frankly I think that’s asobltuely good stuff.

  8. Jared

    do these rules above apply to pivot tables too? I am having trouble getting more than just the cell the formatting is based on to highlight even with the proper $ sign notation.

  9. Dipendra Ray

    Its great helpful tips. Thanks guys.

  10. Nate

    This was a great help. Thank you.

  11. Aleek

    OMG…. I was looking for this so long……. I tried formatting with the formula but I missed the fixed column part ($) which does the trick actually….. Nice trick !!! Kudos

  12. Ravi

    This was a great help for me. Thank you so much.

  13. Sufyan

    Hi,

    I am having a problem when trying to copy the same formula to all the other rows. How do i do this?? i am not able to automatically copy this formula to all the other rows..:( any help please?

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!