Excel makes it easy to set up patterned shading -- unless you filter your data. 10 steps to shading alternate rows in a filtered worksheet. By Susan Harkins in 10 Things, in Software.
Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 This article shows you how to automatically apply shading to every other row in a worksheet. You can apply the shading by using a simple conditional formatting formula. Alternatively, you can apply a predefined Excel table style to your data. Technique 1: Apply shading to alternate rows by using conditional formatting One way to apply shading to alternate rows in your worksheet is by creating a conditional formatting rule. This rule uses a formula to determine whether a row is even or odd numbered, and then applies the shading accordingly. The formula is shown here: =MOD(ROW,2)=0. On the worksheet, do one of the following:.
To apply the shading to a specific range of cells, select the cells you want to format. To apply the shading to the entire worksheet, click the Select All button. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click New Rule. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format. In the Format values where this formula is true box, enter =MOD(ROW,2)=0, as shown in the following illustration. Note: To view or edit the conditional formatting rule, on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. Technique 2: Apply shading to alternate rows by using an Excel table style Another way to quickly add shading or banding to alternate rows is by applying a predefined Excel table style.
This is useful when you want to format a specific range of cells, and you want the additional benefits that you get with a table, such the ability to quickly display total rows or header rows in which filter drop-down lists automatically appear. By default, banding is applied to the rows in a table to make the data easier to read.
The automatic banding continues if you add or delete rows in the table. If you find you want the table style without the table functionality, you can convert the table to a regular range of data. If you do this, however, you won't get the automatic banding as you add more data to your range. On the worksheet, select the range of cells that you want to format. On the Home tab, in the Styles group, click Format as Table. Under Light, Medium, or Dark, click the table style that you want to use. Tip: Custom table styles are available under Custom after you create one or more of them.
For information about how to create a custom table style, see. In the Format as Table dialog box, click OK. Notice that the Banded Rows check box is selected by default in the Table Style Options group. If you want to apply shading to alternate columns instead of alternate rows, you can clear this check box and select Banded Columns instead. If you want to convert the Excel table back to a regular range of cells, click anywhere in the table to display the tools necessary for converting the table back to a range of data. On the Design tab, in the Tools group, click Convert to Range.
If you're using a named Excel table, you can apply a style that shades alternate rows with colour. In the table shown below, the row shading has two rows of grey followed by one row of white. To create this table style, I duplicated one of the existing styles, and modified the row shading. If you don't want to use a table, or table styles aren't available in your version of Excel, you can still have shaded rows, by using. Shade Rows With Conditional Formatting To shade the rows, we'll use the MOD function in a conditional formatting formula. To see how it works, we can test the MOD function on the worksheet, in column G.
We want a set of 3 rows – two with shading, and then a row with no colour. With the MOD function, we'll get the remainder, if the row number is divided by 3. =MOD(ROW,3) The result for each row is shown in column G, and is either 0, 1 or 2. We can shade rows where the result is 0 or 1, and leave the rows with 2 as no fill colour.
To check the result of the MOD function, we can add a formula in column H, to see if it's less than 2.