Shading Rows with Conditional Formatting

@Becky
To shade every other pair:
=MOD(ROW()+1,4)<2

How would you modify the formula if you want to shade two rows, skip two rows, shade 2, skip 2, etc......

Here's a tip to make the whole sheet alternating row colors. Instead of selecting just the data you want alternating rows for, use the sheet selector in the upper left (below the name box) to select the entire sheet, and then apply the conditional formatting.

Is there any way to write the formula that would keep the alternate row shading when some of the rows are hidden, either by hiding specific rows or by applying filters?

@Tina

You probably skipped step 1. "Select the data (range) whose alternating rows you want to shade.".

I tried to do this as a test on a document and while the first line shaded, nothing else did. What am I doing wrong?

I love this tip. The formatting stays on alternate lines even when I sort the data. Thank you

MOD is the Modulo function. Simply put, a division action is performed ( in this case: ROW / 2 and captures the remainder.

So, if the row # is evenly divisible by 2 ( an even number) you get a Modulo = 0, if the row # is not evenly divisible by 2 ( an odd number ) you get a non zero Modulo.

=MOD(ROW(),2)=0 uses the ROW() function to return the row # of each row in the selected block, then this is divided by 2 and the remainder taken, if the remainder = 0, then the whole statement is "true" and the conditional format will be applied to the row which was tested. If the MOD is NOT equal to 0 then the whole statement is false and the conditonal formating will not be applied.

If you used =MOD(ROW(),1)=0 then all rows would be shaded.

If you used =MOD(ROW(),3)=0 then only rows 3, 6, 9, 12,... etc would be shaded

and so on.

IMO it would be helpful to the readers if the formula were explained (specifically, how the MOD function works) rather than simply listing it.

Ed, I agree. Another "feature" of conditional formatting is the ability to bypass/remove it by pasting into a cell.

Response to Kelly Brown Re:

I think the response to the first comment on this web page answers your question.

http://www.myonlinetraininghub.com/excel-conditional-formatting-zebra-stripes

Also lots of good tips on this page.

http://www.myonlinetraininghub.com/toggle-excel-conditional-formatting-on-and-off

Bob, to use this for columns instead, change "row" in the formula to "column". Simple as that. But sorry, the only thing I know about Excel on an Apple device is there are many differences from the Windows version.

Re: Shading Rows with Conditional Formatting. I couldn't get this to work on 3 tries. What am I missing?

I would appreciate advice as to how this formatting can be done using Excel for Mac v15. Also, can this be done for alternate columns?
Thanks, Bob

Conditional formatting is a great tool. But, beware if you over use it, like in a large spreadsheet, it can slow thing down considerably.

Kelly
This helper column version changes every time the relevant column value changes (N in the example).
Start with "A" in the cell U6, place this formula in U7, then copy down as required.
=IFERROR(IF(IF(NOT(N7=N6),1,0)=1,IF(U6="A","B","A"),IF(U6="A","A","B")),"")
Set your conditional formatting for the "A" or "B" of column U.
Revise to suit your required columns/rows.

I am seeking a similar formula but instead of shading every nth row, I want to shade a block of rows every time a value in a certain column changes.

I can email an example that was done manually if you like.