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