Microsoft Excel
Automatically Shade Every
Second Row

QUESTION:  When we convert a regular data range to a table, we can apply a table style that shades every second row. However, if our data is not converted to a table for a variety of reasons, sometimes we end up manually applying a fill colour and then when we add or remove rows, we have to start all over again.  Is there a better way?​

​

ANSWER:  Yes, there is a *much* faster way! Use the MOD function in a conditional formatting formula:

​

1. Select the range, for example A2:D25, to which you wish to apply the formatting, making sure that the active cell is on row 2. 

2. On the Home tab > Styles group click the Conditional Formatting button > New Rule

3. Select the Use a formula to determine which cells to format option in the Select a Rule Type: list

4. Input the following formula:

=MOD(ROW(),2)

​

   5. Click the Format button, select the desired format, then click OK twice

 

Let's break the formula down:

​

The MOD function divides a value and returns the remainder. For example, =MOD(4,2) would result in 0 as 4 divided by 2 has no remainder. However, =MOD(5,2) would result in 1. In this case, we are using MOD to divide the current row number ROW(), by 2 and return the remainder.

​

In "Excel language" 0 has a value of FALSE and anything greater than 0 has a value of TRUE. Because conditional formatting depends on TRUE to format, all odd-numbered rows will be formatted.

​

If you want to apply a different format to all even rows, add the following formula as your second condition:

​

=NOT(MOD(ROW(),2))

​

By adding the NOT function, Excel will, in essence, "translate" the TRUE to FALSE, and apply formatting to all rows that do have a remainder - in other words, all of the even-numbered rows.

​