Software

10 steps to shading alternate rows in a filtered worksheet

Excel makes it easy to set up patterned shading -- unless you filter your data. This slick workaround prevents the filter from disrupting your shading scheme.

To improve readability, some people shade every other row in a busy Excel sheet. A simple conditional format, which we explain in Shade Excel worksheet rows, columns, and sometimes both, is all you need. Unfortunately, this common technique won't work if you filter the records. That's because the conditional format relies on a formula that uses the ROW() function in the form

=MOD(ROW(), interval) = 0

where interval is the number of rows in the alternating pattern.

Because Excel's ROW() function considers every row in the range, and not just the records that match a filter's criteria, this formula can't accommodate a filtered range. To both shade and filter, the answer is still conditional formatting, but with a more flexible formula.

Note: This article is also available as a PDF download.

1: Select the range

The first step is the simplest: Select the data you want to format. Don't select any column or row headings; select only the data. In the example sheet shown in Figure A, select A2:C9. (If filtering is already in play, be sure to remove the filter to select the full range.)

Figure A

Select the range you want to shade and filter.

2: Start formatting

This technique takes advantage of Excel's rather robust conditional formatting feature. To initiate this feature -- with the range still selected -- simply choose Conditional Formatting from the Format menu.

3: Specify a formula

In the Conditional Formatting dialog box, you have several options. Initially, the Condition 1 setting is Cell Value Is. This technique relies on a formula, so choose Formula from the Condition 1 drop-down list, as shown in Figure B.

Figure B

The Formula Is option applies formatting based on the results of a formula.

4: Enter the formula

Choosing the Formula Is option displays a new control. Enter the shade-producing formula, shown in Figure C, into this control:
=MOD(SUBTOTAL(3,$A$1:$A2),2)

Figure C

Enter the formula

The shade-producing formula uses SUBTOTAL() instead of ROW().

If you recall, the ROW() formula relied on the row number -- that's why it won't work in a filtered set. It continues to consider hidden rows. This new formula uses the SUBTOTAL() function, eliminating that limitation.

5: Specify the format

Now it's time to set the shade format. Click the Format button and complete the following steps:

  1. In the Format Cells dialog box, click the Patterns tab.
  2. Click one of the colored squares.
  3. Click OK to return to the Conditional Formatting dialog box, which shows the selected color in the Preview Format control.

6: Return to the shaded sheet

Once you've specified the conditional format, click OK to return to the sheet. As you can see in Figure D, the formula shades every other row in the range you selected in step 1.

Figure D

The conditional format at work!

7: Compare the two formulas

Right now, you can't tell the difference between the ROW() function formula and the SUBTOTAL() formula. The conditional format shades every other row. The one disadvantage is that this formula works well alternating only every other row. For instance, to shade every third row, you might change the last argument in the SUBTOTAL() formula from 2 to 3, but the results won't be what you expect. Instead of shading every third row, it shades the second and third rows, leaving only one row unshaded. That's still a type of alternating shading, but it's not what we're use to seeing.

8: Define an AutoFilter

To define an AutoFilter, select the data range, A1:C9, and choose Filter from the Data menu. (This time you want to include the column headings.) Then, select AutoFilter. Excel will add drop-down arrows to the column headers, as shown in Figure E. Each drop-down list contains a unique list of values in its respective column.

Figure E

Choose an item from an AutoFilter list.

9: Choose criteria

At this point, you've compiled the pieces: You applied a conditional format and an AutoFilter. All you have to do now is filter the records and watch the conditional format formula do its work. Before you do, pay close attention to which items are shaded and which aren't. In particular, notice that there are three AT6-02 items. Two of those items, in rows 3 and 5, are shaded. The third, in row 8, isn't shaded.

Now, let's see what happens to that shading when you apply a filter. To filter the list, simply choose an item from this list. For this example, choose AT6-02 (as shown in Figure E).

10: Format at work

Figure F shows the result of filtering the sheet by choosing AT6-02 from column A's AutoFilter list. As you can see, the shading format works perfectly. By viewing the row numbers, you can see that the filter hid rows 2, 4, 6, and 7, but the shading works fine -- more or less. In this case, because the filtered set has only three rows, only one row is shaded, which looks a bit odd. This is less of an issue when you have dozens of rows.

Figure F

The SUBTOTAL() formula alternates shading, even for a filtered recordset.

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.

9 comments
efs710920mex
efs710920mex

The formula changes according to the language you have Office installed. In Spanish, for instance, this formula would become: =RESIDUO(SUBTOTALES(103,$A$1:$A2),2)=0

oddacorn
oddacorn

In looking into how the SUBTOTAL function works, I found that you also have an option when choosing the Function_num (first) parameter to have it ignore hidden rows. If you change the formula to use "SUBTOTAL(103, $A$1:$A2)" instead of "SUBTOTAL(3, $A$1:$A2)", then the alternate shading will also work correctly in a worksheet when you're manually hiding some of your rows. Thanks for the great tip; I wasn't even using the ROW() method!

rich_odwazny
rich_odwazny

You can eliminate the problem noted in step 7. Apply the NOT function to the formula given in step 4 after changing the interval as desired. For example, to shade every fourth row: =NOT(MOD(SUBTOTAL(3,$A$1:$A2),4))

RayG314
RayG314

So this works for filtered worksheets, but how about shading alternate rows of a worksheet only once, and allowing for different sorting of the data to not damage the shading? And how can this technique be used for alternate shading of two rows, then two rows unshaded, then two rows shaded, etc.?

mcain64
mcain64

Thanks for the tip! The filter shading problem has irritated me for a long time.

The Daleks
The Daleks

It does alternate shading of sorted and filtered lists automatically.

Vani V
Vani V

Create a dummy column and type in 1 and 2 in the first two cells of dummy column. In the 3rd cell just put in the formula =cell1 and 4th cell , =cell2. Now select Cell 3 and Cell 4 and at the right hand end of it, you will see a little + symbol and double click on it. It will generate 1 and 2 in the following cells. Put auto filter. Select 1 and Select the range and color it. Now when you remove filter and show all, you have alternate rows shaded. Now remove the dummy column. If you want to shade 2 rows and 2 rows unshaded. Do the same trick. Type 1,1,2,2 in the first 4 cells and follow the same steps. This works for me like in a matter of few clicks. No writing formulae and conditional formatting. Ofcourse different strokes for different folks. Ugh, boy explaining clicks is complicated :P not as simple as the actual process :) Enjoy

ralph.bacon
ralph.bacon

using MOD(SUBTOTAL(2,[range]),2) So now you can select the entire sheet, apply the above formula and all empty rows below your data will be left unshaded (rather than shaded which looks odd). Incidentally, to remove shading from the top (column heading) row just select that row and delete the condition formatting from it. This is a brilliant tip, thanks a lot!