asfenmatter.blogg.se - Highlight every other row in excel

#HIGHLIGHT EVERY OTHER ROW IN EXCEL PDF#
#HIGHLIGHT EVERY OTHER ROW IN EXCEL FULL#

Instead of shading every third row, it shades the second and third rows, leaving only one row unshaded. 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. The one disadvantage is that this formula works well alternating only every other row. The conditional format shades every other row. Right now, you can’t tell the difference between the ROW() function formula and the SUBTOTAL() formula. Figure D The conditional format at work! 7: Compare the two formulas As you can see in Figure D, the formula shades every other row in the range you selected in step 1. Once you’ve specified the conditional format, click OK to return to the sheet.

Click OK to return to the Conditional Formatting dialog box, which shows the selected color in the Preview Format control.

In the Format Cells dialog box, click the Patterns tab.

Click the Format button and complete the following steps: This new formula uses the SUBTOTAL() function, eliminating that limitation. If you recall, the ROW() formula relied on the row number - that’s why it won’t work in a filtered set. Enter the shade-producing formula, shown in Figure C, into this control: =MOD(SUBTOTAL(3,$A$1:$A2),2) Figure C The shade-producing formula uses SUBTOTAL() instead of ROW(). 4: Enter the formulaĬhoosing the Formula Is option displays a new control. Figure B The Formula Is option applies formatting based on the results of a formula. This technique relies on a formula, so choose Formula from the Condition 1 drop-down list, as shown in Figure B. Initially, the Condition 1 setting is Cell Value Is. In the Conditional Formatting dialog box, you have several options. To initiate this feature - with the range still selected - simply choose Conditional Formatting from the Format menu. This technique takes advantage of Excel’s rather robust conditional formatting feature.

#HIGHLIGHT EVERY OTHER ROW IN EXCEL FULL#

(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.

In the example sheet shown in Figure A, select A2:C9. Don’t select any column or row headings select only the data. The first step is the simplest: Select the data you want to format.

#HIGHLIGHT EVERY OTHER ROW IN EXCEL PDF#

Note: This article is also available as a PDF download. To both shade and filter, the answer is still conditional formatting, but with a more flexible formula. Where interval is the number of rows in the alternating pattern.īecause 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.

That’s because the conditional format relies on a formula that uses the ROW() function in the form =MOD(ROW(), interval) = 0 Unfortunately, this common technique won’t work if you filter the records. A simple conditional format, which we explain in Shade Excel worksheet rows, columns, and sometimes both, is all you need. To improve readability, some people shade every other row in a busy Excel sheet. This slick workaround prevents the filter from disrupting your shading scheme. 10 steps to shading alternate rows in a filtered worksheetĮxcel makes it easy to set up patterned shading - unless you filter your data.