An Excel conditional format that expands with grouped data

It’s not hard to add an alternating band format to a table by applying a table style. It’s quick and easy, but it’s limited. You’ll get a single-row fill color that alternates with a row of white (or no fill color). That makes discerning rows easy, but it won’t help you spot a group. If you want an alternating band that expands to accommodate an entire group, whether a single row or several, you’ll need a conditional format. In this article, I’ll show you a conditional format rule that relies on a helper column. The result will be a dynamically expanding band that accommodates a group.

I’m using Office 2016 on a Windows 10 64-bit system, but you can use this technique with earlier versions. You can work with your own data or download the demonstration .xls or .xlsx file.

The data

Our simple data set is shown in Figure A. As you can see, three columns repeat values, and there’s potential for repeats in all five columns. We could use an alternating band format, but that’s not what we want. Specifically, we want all records in the same region to be the same color when the data set is sorted by the region.

Figure A

We’ll create an alternating band that expands when the data is sorted.

Built-in style

You can quickly add an alternating band format to a data set using a built-in table style, but it won’t accommodate groups. Let’s take a look at how quickly you can use this feature:

Click anywhere inside the data set.
Click Format As Table in the Styles group (on the Home tab).
Choose a style.
Confirm whether your table has headers (Figure B).
Click OK and Excel applies the selected style (Figure C).

Figure B

Create the table.

Figure C

The built-in style formats the table.

It’s fast and convenient, but it can’t read your mind. If you sort by one of the columns, as shown in Figure D, the format remains a single-row band. Excel’s smart, but that’s expecting a bit too much.

Figure D

It’s always a single-row format.

An expanding format

Getting Excel to expand each band to accommodate groups doesn’t require a mind reader. In this case, we want the band to expand for each region. To accomplish this, we’ll add a helper column and add a conditional format rule that references the values in the helper column.

The first step is creating the helper column. You can position it to the left or right of the data set or you can place it somewhere out of sight. However, you must leave a blank column between the data set and the helper column so the helper column formulas don’t sort with the data set. This will make more sense later.

To create the helper column, do the following:

Select A3 and enter the value 1. (Remember to leave a blank column.)
Select A4 and enter the following formula: =IF(F4=F3,A3,-A3)
Copy the formula in A4 to the remaining cells in the range (A5:A34 for our example data set).

As you can see in Figure E, the formula returns a column of 1s and -1s. The values alternate because the regional values (in column F) never repeat in adjacent cells. If you’re working with your own data set, you might see a different pattern of 1s and -1s.

Figure E

The helper column evaluates the values in column E.

The helper expression is a simple IF() function that returns true when the compared values (F4=F3) are the same. When this component is true, the expression returns the value immediately above. When the two values aren’t equal, the expression returns the negative of the value immediately above. The resulting value will always be a 1 or a -1 because the first value in the column is 1. Because the regional values don’t repeat from one cell to the next, the pattern alternates between 1 and -1. You’ll see why this matters shortly.

With the helper column in place, you’re ready to apply the conditional format as follows:

Select the data set (C3:G34).
Click the Conditional Formatting option in the Styles group and choose New Rule.
In the resulting dialog, select Use A Formula To Determine Which Cells To Format in the top pane.
In the bottom pane enter the formula =$A3=1.
Click Format.
Click the Fill tab, choose a color, and click OK. Figure F shows the formula and fill color.
Click OK to return to the formatted data, which is still a single-band fill because of the current sort.

Figure F

This expression applies the fill color when the expression returns true.

Ready to see what all the fuss is about? Figure G shows the result of sorting the data set by the region. As you can see, the bands expand to accommodate all the rows in a region–the result is an expanding band that makes it easy to discern each group.

Figure G

The fill color expands.

This solution is easy to implement and gets the job done, even if you sort by another column as shown in Figure H. As you can see, the formatting continues to group on the regions regardless of which column you use to sort.

Figure H

The band accommodates the region groups regardless of how you sort the data.

Earlier, I mentioned that there must be a blank column between the helper column and the data set. That column keeps the helper column from sorting when you sort the data set. Normally, that wouldn’t be a problem, but because the first cell in the helper column contains a literal value and not a formula, sorting is problematic. You won’t want to sort the helper column.

Functional with limits

This solution is easy and it works, but it lacks flexibility. Right now, the band references only the region column. So bear in mind that if you want the band to accommodate groups in the column you’re sorting, this solution won’t work.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

An Excel conditional format that expands with grouped data