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:
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:
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!
No comments:
Post a Comment
I'd love to hear from you!
-Nick