Categories
Excel

MOD Function: Highlight Every Other Row or Column

You can apply different colors to rows or columns based on their position using conditional formatting and the MOD function. This function returns the remainder of a division operation. For instance, you can use it to check if a row number is odd or even and assign a color accordingly.

Here is an example of how to do it.

  1. Select the range of cells that you want to format.
  2. Click Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format from the New Formatting Rule dialog box
  4. In the Format values where this formula is true box, enter one of these formulas:
    • To highlight every other row, use =MOD(ROW(),2)=0
    • To highlight every other column, use =MOD(COLUMN(),2)=0
  5. Click Format button and choose the fill color that you want from the Fill tab.
  6. Click OK to close the Format Cells dialog box and then click OK again to apply the rule.

Apply color to alternate rows or columns dynamically

Although the above method applies the formatting specified to every second row or column quickly and easily, it is not dynamic. Rows containing no data will still have the formatting applied. This looks slightly untidy and makes reading the spreadsheet a bit more difficult. Making the highlighting of every second row or column dynamic takes a little more formula tweaking:

  1. Again, select the range, for example, A1:E15.
  2. Click Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format from the New Formatting Rule dialog box
  4. In the Format values where this formula is true box, enter this formula:
    =AND(MOD(ROW( ),2),COUNTA($A1:$E1))
  5. Click Format button and choose the fill color that you want from the Fill tab.
  6. Click OK to close the Format Cells dialog box and then click OK again to apply the rule.

Note that you do not reference rows absolutely (with dollar signs), but you do reference columns this way.

Any row within the range A1:E15 that does not contain data will not have conditional formatting applied. If you remove data from a specific row in your table, it too will no longer have conditional formatting applied. If you add new data anywhere within the range A1:E15, the conditional formatting will kick in.

This works because when you supply a formula for conditional formatting, the formula itself must return an answer of either TRUE or FALSE. In the language of Excel formulas, 0 has a Boolean value of FALSE, while any number greater than zero has a boolean value of TRUE. When you use the formula =MOD(ROW( ),2), it will return either a value of 0 (FALSE) or a number greater than 0.

The ROW( ) function is a volatile function that always returns the row number of the cell it resides in. You use the MOD function to return the remainder after dividing one number by another. In the case of the formula you used, you are dividing the row number by the number 2, so all even row numbers will return 0, while all odd row numbers will always return a number greater than 0.

When you nest the ROW( ) function and the COUNTA function in the AND function, it means you must return TRUE (or any number greater than 0) to both the MOD function and the COUNTA function for the AND function to return TRUE. COUNTA counts all nonblank cells.

Conditional Formatting

  1. Setting Up Checkboxes for Conditional Formatting
  2. Highlighting Formula Cells with Conditional Formatting
  3. Sum Cells That Meet Conditional Formatting Criteria
  4. Highlight Every Other Row or Column with MOD Function and Conditional Formatting
  5. Enable and Disable Conditional Formatting with a Checkbox
  6. Sort Data By Conditional Formatting Criteria