Automatically Shade Every
© 2009 DJH Training & Application Solutions Inc - Updated 2022
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:
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.
On the Home tab > Styles group click the Conditional Formatting button > New Rule
Select the Use a formula to determine which cells to format option in the Select a Rule Type: list
Input the following formula:
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:
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.