Excel 2007 Conditional Row Formatting

It’s pretty easy to highlight every other row in Excel using a condition. In the matters of highlighting a row based on a value of single cell is another story. Not entirely complex but nonetheless requires some knowledge of writing formulas and knowledge of the INDIRECT function. Bastien blogged about Conditional row color based on a cell value which illustrates the process quite nicely. The steps I am about to cover mimic Bastien’s procedures with the exception of targeting Excel 2007 instead of Excel 2003.

Sample data set

Step 1: Highlight the rows

You will need to highlight the rows that are targeted in which you wish to apply the conditional formatting.

Step 2: Click on Conditional Formatting

Conditional Formatting is found under the Home tab of the Ribbon.

Step 3: Select Manage Rules

Step 4: Click New Rule

Step 5: Select Use a formula to determine which cells to format

Step 6: Enter the following formula which uses the INDIRECT function

=INDIRECT("C"&ROW())="M"

Then click the Format button to specify the formatting options

Step 7: Click the Fill tab

Step 8: Select the background color

Once you have selected a color click the OK button to proceed

Step 9: Click OK

Step 10: Click OK to apply formatting

Final results

To highlight the rows that have a Gender value of F in column C simply repeat steps 4 – 10 and switch the conditional value to F as in (Step 6)

Formula Syntax:

=INDIRECT("C"&ROW())="F"
Advertisements

3 thoughts on “Excel 2007 Conditional Row Formatting

  1. Yes I searched before asking says:

    How does it work if you want the same thing except instead of it =”M” it contained “M”. e.g. all of these would highlight the row:
    Sun blade
    HP Blade
    IBM BLADE

    I’ve looked at IF and AND and SEARCH and can’t get it to work as desired.

  2. Questor says:

    How does it work if you want the same thing except instead of it =”M” it contained “M”. Maybe your example isn’t good for this question đŸ™‚

    Say if I wanted to highlight rows where a certain column contained “blade” (case insensitive), I’d want all of these to have a colored row:
    Sun blade
    HP Blade
    IBM BLADE

    I’ve looked at IF and AND and SEARCH and can’t get it to work as desired.

    [corrected text, turned on javascript]

Leave a Reply

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