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 rangeThe 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.)
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 formulaIn 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.
The Formula Is option applies formatting based on the results of a formula.
4: Enter the formulaChoosing the Formula Is option displays a new control. Enter the shade-producing formula, shown in Figure C, into this control:
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:
- In the Format Cells dialog box, click the Patterns tab.
- Click one of the colored squares.
- 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 sheetOnce 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.
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 AutoFilterTo 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.
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 workFigure 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.