Thanks to conditional formatting, you can apply several banding schemes to your Excel worksheets, and we’ve discussed several of them already. One possibility we haven’t reviewed, however, is using data to determine which rows are banded. Throw in a little data validation and you can let users band (or shade) specific rows by simply choosing an item from a list.

To illustrate this technique, we’ll use data validation to display category values. When the user chooses a category, conditional formatting will shade records for the selected category. For example, I choose Beverages from the data validation list is G2 to identify all beverage records.

Steps

Implementing this banding scheme is easier than you might think. First, we’ll create a unique list of category values from the actual data. Using that list, we’ll create the data validation list in G2. Then, we’ll add a conditional format that will shade rows where the Category value equals the value selected in G2. When applying this to your own worksheets, you might not need all three steps.

Step One

First, create a unique list of category values, as follows:

  1. Select the list of values. In this case, select G4..G46. Be sure to include the header cell (G4).
  2. Click the Data tab.
  3. In the Sort & Filter group, click Advanced. In Excel 2003, choose Filter from the Data menu, and then select Advanced Filter.
  4. In the resulting dialog box, check the Copy To Another Location option.
  5. The List Range option should reflect the selection ($G$4:$G$49) from step 1.
  6. In the Copy To control, enter $I$4. You can copy the list most anywhere you like. I chose to copy the list near the data range, so you could see it in the figures.
  7. Check the Unique Records Only option.
  8. Click OK and Excel will copy a list of unique category values to column I.

Step Two

Now you’re ready to build the validation list to make the whole process easy and intuitive for the user:

  1. Select the cell where you want to create the validation list. In this case, that’s G2. (I also entered some explanatory text in G1, to help alert the user – this isn’t necessary for the technique to work, but doing so helps the user.)
  2. Click Data Validation in the Data Tools group (on the Data tab). In Excel 2003, choose Validation from the Data menu.
  3. From the Allow dropdown, choose List.
  4. In the Source control, enter =$I$5:$I$20.
  5. Click OK.

Step Three

With the validation list in place, you’re ready to apply the conditional formatting that will create the bands:

  1. Select the data range. In this case, that’s A5..G49.
  2. Click the Home tab.
  3. In the Styles group, click the Conditional Formatting dropdown and choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu.
  4. In the upper panel, highlight the Use A Formula To Determine Which Cells To Format option. In Excel 2003, choose Formula Is from the Condition 1 control.
  5. In the Format Values Where This Formula Is True control, enter the following formula: =$G5=$G$2. (There’s no option name for this control in Excel 2003.) Pay close attention to the absolute references. If you don’t enter the $ symbols correctly, this technique won’t work.
  6. Click Format and then click the Fill tab (Patterns tab in Excel 2003) in the resulting dialog box.
  7. Choose a color and click OK.

Using it!

After applying the conditional format, you’re ready to put all the pieces together. From the validation list in G2, select any category and watch Excel update the bands, accordingly.

There are a couple of things you’ll want to keep in mind when using this technique:

  • If you use the ribbon version’s table feature, be sure to use a banding color that contrasts with the table’s light blue banding.
  • Because the unique list of category values isn’t dynamic, this technique works best with a static list of values. You’ll have to work much harder if you want the validation list to update when you enter a new category value.

Editor’s note: An example Excel worksheet is provided as an aide to understanding this technique.

Also read: