Excel Tip: Apply Conditional Formatting to the whole row


Conditional Formatting Menu in Excel 2013

One of the Microsoft Excel features I use quite a bit is Conditional Formatting. This is the feature (introduced in Excel 2007) that lets you re-colour a cell in your worksheet depending on the criteria you specify, e.g. highlight any cells containing the word “Server 2003” in red.

The problem I was having was that I wanted the whole row to be highlighted, not just the particular record. It turns out this is fairly easy to do, even though it looks a bit difficult.

Step 1 – Create a new rule

  • The easiest way to start is to select one cell containing the text you want to highlight
  • Click the conditional formatting button on the toolbar and go to Highlight Cell Rules > Text that contains…
  • Format the text how you like, e.g. Light Red Fill with Dark Red Text
  • Click OK

You should now have one cell in your spreadsheet that is formatted how you want

Step 2 – Apply rule to the whole table

  • Click the conditional formatting button on the toolbar and go to Manage Rules…
  • You will see your new rule listed but the Applies to box will only reference one cell e.g. ‘ =$B$2
  • Change the text in the Applies to box to refer to the whole table e.g. ‘ =$A$1:$H$100 ‘
  • Click the Apply button

Now that rule will highlight any matching text in the entire table, not just one cell

Step 3 – Use a formula to highlight the entire row

We now need to edit the rule type to use a formula instead of the predefined “text that contains” rule

  • From the Rules Manager, with your rule selected click the Edit Rule button
  • Select the Rule Type, “Use a formula to determine which cells to format”
  • In the box, Format values where this formula is true, you type formula that will find the text. I’ll break it down below.
    • First type ‘ ‘ so Excel knows its a formula
    • Next put in a relative column reference (that just means put a dollar in before the letter) and the row number you want to start on e.g. ‘ $B2 
    • Next add another ” 
    • Finally, add the text you want to search on in quotes ‘ “Server 2003” 
    • Altogether that makes: =$B2=”Server 2003″
  • Click OK, and then click Apply and you should see the entire row formatted
  • Tip: Watch out if you use the cursor keys when typing the formula, it actually makes cell references appear rather than move the cursor!

Use a partial text search

The above is good if you know the exact full text but what if you want several similar strings to use the same rule? One way is to just copy the existing rule and change the text. However, that is quite time-consuming and, unfortunately, Excel doesn’t have a “Copy Rule” button. A better way is to use the Search formula.

  • I want to format all rows that contain the following strings
    • Windows Server 2003
    • Server 2003 Enterprise
    • Microsoft Server 2003 Server  R2 x64
  • They all contain ‘ Server 2003 ‘ so I can use the search formula below
    • =SEARCH(“Server 2003”,$L2)

That’s all there is to it. Once you get the hang of it, it is really easy to do and you can apply it to any cell that you can use a formula to find

 

Let me know in the comments if you have any problems with it, or have any other clever uses for conditional formatting. Here’s some other good ones on from TechRepublic – 10 cool ways to use Excel’s conditional formatting feature

Advertisements

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s