What is it?
Color alternate rows/columns using conditional formatting formula
How/Where should it be used?
In the formula option of conditional formatting
What’s the formula?
Columns:
=mod(column(),2)=0
=mod(column(),2)=1
Rows:
=mod(row(),2)=0
=mod(row(),2)=0
How does the formula work?
• MOD function returns the remainder when a number is divided by another number
• Column(), Row() function returns the column number or row number
• So, when a row/column function is divided by 2 we either get a 0(or) 1 as the output which is used to find the alternate rows/columns and color them
Detailed Steps:
Step 1: Open Conditional formatting window and select create rules
Step 2: Select the rule type as “Use a formula to select which cell to format”
Step 3: Type in the below formula in the box “Format values when this formula is true
=mod(column(),2)=0
Step 4: Click on Format and select the color that you want to choose
Step 5: The Output is as below
Step 6: Follow steps 1-5 with the formula below to get the output as below
=mod (column (), 2) =0
Note: Replace columns with Rows in the formula and follow the same steps to color alternate rows
