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

Analysis Tool Pak Plug-in for Excel 2007

In the event you have Office 2007 and need to install the Analysis Tool Pak plug-in here is the step-by-step.

Installing the Add-in

1) Click the Office button

2) Select Excel Options

3) Select Add-ins

4) Select Analysis Tool Pak

5) Click OK

Validate the Install

1) Select the Data Tab

2) Analysis Group is Missing

Load the group

1) Click the Office button

2) Select Excel Options

3) Manage Add-Ins

4) Select Analysis Tool Pak

5) Click OK

Validate the Group

1) Select the Data Tab