By any reason, if you want to highlight every nth row or column in Google Sheets, you can easily do it with the MOD function. You can use my custom MOD formulas in Conditional Formatting.

Even though you may think that there is no point in using this in your sheet, try to learn it. At least you can understand how a function is flexible. Here the function what I am going to use is MOD and it’s all about using the dividend in MOD.

Highlight Every Nth Row or Column in Google Sheets

Let’s start with highlighting rows. To highlight rows, we can use the ROW function together with MOD. For highlighting columns, the COLUMN function replaces ROW in MOD.

Here first you can learn how to highlight every nth row in Google Sheets.

Nth Row Highlighting

The formula to Highlight Every Nth Row from the First Row in Google Sheets

Here is the formula to highlight every 3rd row in your Google Doc Spreadsheet. You can change the divisor in this formula from 3 to any number. Accordingly, the row highlighting will change from 3 to the number you have provided as the divisor in MOD. If you opt 4, the formula will highlight every fourth row.

Formula # 1

=mod(row(),3)=0

Note: Useful only to start the highlighting from row 1 in your sheet. That means the row count will start from row 1 and start highlighting every third row.

If you want to start the highlighting from any specific row, there is an alternative formula (formula #2).

How to apply this conditional formatting custom formula in Google Sheets?

First, select the range where you want the highlighting to apply.

Go to the menu Format > Conditional Formatting

This formula will highlight every third row in my sheet from row 3. See the range A1: E13. Even if I select the range B4: D20, the row counting will start from B1. So the first highlighted row in my sheet will be B6 and then B9, B12, B15 and B18.

You May Also Like: How to Highlight Cells Based on Expiry Date in Google Sheets

Highlight Every Nth Row That Starts from Other than The First Row (Highlight Every Nth Row in Custom Range)

As mentioned above, if you want the counting to start from any specific row other than the first row and then highlight every nth row, you can use this formula.

Formula # 2

=mod((row(A5:A10)-row($A$5)+1),3)=0

I have used the above formula to highlight every 3rd row in the range A5: E10. This formula counts A5 as my first row and start the first highlighting in the row A7: E7.

If you use the above formula # 1 in this range, the first highlighting row will be A6: E6.

If your formula # 2 range is A2: Z100, you should change the formula as below.

=mod((row(A2:A200)-row($A$2)+1),3)=0

Nth Column Highlighting

The Formula to Highlight Every Nth Column from the First Column in Google Sheets

Similar to the row highlighting, there are two formulas in highlighting nth column also.

Formula # 3

=mod(COLUMN(),3)=0

This formula highlights every third column in Google Sheets. The column number counting starts from Column A.

For example, my data range is D1: K10. This formula # 3 will highlight column F and I. It skips column C (first third column) as it’s not in the range.

How to Highlight Every Nth Column That Starts from Specific Column Number

If you want to start the count from column D and highlight the first third column as column F, here is the formula.

Formula # 4

=mod((column(D1:D10)-column($D$1)+1),3)=0

For the range B1: J20, the formula will be as follows.

=mod((column(B1:B20)-column($B$1)+1),3)=0

This formula will highlight the column D, G and J.

This screenshot shows how the formula 2 and 4 above works for the range B1:J20.

That’s all. This way anyone can easily highlight every Nth row or column in Google Sheets.