How to Make Alternating Row Colors in Excel

I was at work the other day staring at endless rows and columns of data on a spreadsheet with my boss. She made a statement about how she “wished there was a way to separate the rows to make it easier to look at.” I smiled and said there was and sprang into action, putting my Excel skills into use. Using a simple formula in conditional formatting I made every other row a different color. She was impressed.

So here’s my tip to get alternating row colors when not using tables or shared worksheets. The trick is to use conditional formatting to apply a fill color to even numbered rows by using this formula:

 

=(MOD(ROW(), 2)=0)

How does it work? The function MOD returns the remainder after a number is divided by a divisor. The ROW function returns the row number of a reference. So the formula takes the current row number and divides by two. The resulting remainder is either a 1 or a 0. If we set the conditional formatting formula equal to 0 then every other row will be colored in. Make sense?

 

 

Here’s a short video showing exactly how to use it if you’re confused:
 

I hope that helps!