Using the MOD and ROW functions, you can tell Excel to perform tasks on data in every other row.
MOD returns the remainder after a number is divided. It takes the form MOD(number,divisor). Using the divisor "2", you can tell whether a number is odd or even.
ROW returns the row number of a cell reference. It takes the form ROW(reference). If no reference is given it uses the current row.
You could, for example, shade alternate rows using conditional formatting and the MOD and the ROW functions. (Click On Thumbnails for bigger pictures!) |
1. |
Select your data range. |
|
Conditional Formatting Box |
| 2. |
On the Format menu, click "Conditional Formatting." |
| 3. |
Under Condition 1, select "Formula Is". |
| 4. |
In the data entry box, type”=MOD(ROW(),2)=0” |
| 5. |
Click the Format button. In the Format Cells dialogue box, click the Patterns tab.
|
|
Format Box
|
| 6. |
Select a colour from the pattern Dialogue, then click OK |
| 7. |
Click OK again in the Conditional Formatting box. |
|
 |
| |
| The formula in step 4: =MOD(ROW(),2)=0, checks whether the row number divided by 2 has a remainder of 0. If this is true, the row is coloured in. If false the row is left as it is. |
