Software

Make every user a power Excel user with dynamic conditional row banding

Combine data validation and conditional formatting to implement an easy-to-use dynamic banding option for your users.

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:

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
BlueCollarCritic
BlueCollarCritic

Is there a way to expand this to include multiple columns? Example: Imagine you have a balance Sheet and you'd like to apply this conditional banding to it using the following rule: If MTD (Column G) is

perkins42
perkins42

I think I figured out what was wrong. First though, I did download your example and we were in complete agreement. What I think I did was start with a repurposed spreadsheet (most of the setup was done - I thought I'd save myself some steps). This turns out to have been a bad idea. When I copied the same data into a new spreadsheet pasting values, and formats separately to insure a clean copy) and started from the beginning, it worked fine. And I'm the one who keeps telling people they have to be careful with shortcuts. Lesson learned.

perkins42
perkins42

While I think this is very neat, I can only get it to work in Excel 97-2003. In both Excel 2007 and Excel 2010 all that happens it's really non-dynamic. When I select the item from the validation dropdown nothing happens until I either scroll down the page showing the area that were previously hidden or go to another worksheet and come back again, then it is updated. My first thought was the calculation option was set to manual, so I checked and it is auto. Btw, I've tried this on two different copies of Excel 2010, one version of Excel 2007 and one copy of Excel 2002 and it only works properly in the earliest version. I've no idea what I might be doing wrong. Any thoghts?

TAPhilo
TAPhilo

If it is a SMALL spreadsheet then this works well, however it does force a person to use another column and now you have to manage that also. And if you happen to insert a new row that happens to exist within the column where the values are it would break. It would be better if there was a way to do conditional formatting using existing values within a cell in a row to do the formatting.

ssharkins
ssharkins

The example was created in 2010, so it should work. If you'll contact me privately, I'll send you a copy of the example worksheet -- maybe that will help.

ssharkins
ssharkins

I just remembered, you can download the example worksheet -- are you working with the downloadable workbook?