Formatting makes our documents look appealing to our eyes. The power of conditional formatting is far-reaching. Excel has not been left behind since we can use a specific formula to format our worksheets to our own liking. In this case, the formula should apply a given colour to alternate rows. This will enable an easy distinction between the different rows available.
Procedure:
1. Launch the Excel workbook and navigate to your worksheet.
2. Select the range of data you wish to apply color.
3. After highlighting your data set, navigate to the Home tab.
4. Under the styles group, click on the conditional formatting icon/button located on the ribbon.
5. Select the New Rule option. A new rule Dialog will be displayed.
6. Then click on "Use a formula to determine which cells to format" under the select a Rule type
7. Enter the following formula inside the rule description textbox.
=MOD (ROW (),2)
8. Choose a formatting style using the format button and preview. In our case, we choose a blue color.
9. Click Ok to confirm and affect the formatting of the rows.
10. Click on save to save changes. As you can see alternate rows will be colored in the color we choose in the format selection.
Description of the formula
Generally, the MOD function returns a modulus after the division of a number (Dividend) with another(divisor). A modulus is usually the remainder that results after division. The ROW () function, on the other hand, is responsible for posting the Row number to the MOD () function. The 2 which is used as an input of the MOD () function will be used as the divisor used to divide the returned row number. Therefore, Excel will only apply formatting on the data when the formula is true I.e. if and only if a value of one is returned by MOD () after receiving an odd row number from the ROW () function.
E.g. for row number nine: 9/2=4 rem 1, 9 is the dividend,2 is the divisor, 4 is the quotient and 1 is the modulus output. Row nine will thus be formatted.