Computer Training & Application Solutions made Simple
Contact us for a free consultation
  • Click to send us an email. We'd love to hear from you!   Ottawa       613-656-4229
  • Click to send us an email. We'd love to hear from you!   Montreal    514-342-9494

Automatically Shade Every 2nd Row in Excel

By Deborah J. Sparks

QUESTION:

"I work with huge spreadsheets that are much easier to read/edit when every second row is shaded. Our file is shared, so we can't use tables. I have to redo my shading manually every time I add or remove rows, which is very tedious and time consuming. Is there a faster 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
  2. Click the Format menu > Conditional Formatting
  3. Select "Formula Is" and type (or copy and paste) the following:

    =MOD(ROW(),2)

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



More Newsletter Articles

© 2009 DJH Training & Application Solutions Inc.

WOULD YOU LIKE TO USE THIS ARTICLE IN YOUR NEWSLETTER, EZINE OR WEB SITE? You can, as long as you include this complete blurb with it: An entrepreneur at heart, Deborah J. Sparks established DJH Training & Application Solutions Inc. over 20 years ago, realizing her dream of creating a company known for providing high quality computer software training, application development and support and exercising her passion for teaching. To learn more and to register for DJH's free newsletter, Innovations, please visit www.djhsolutions.ca.