Highlight Every Alternate Set of N Columns in Google Sheets

GiGiKing·March 13, 2024·0 comments

In this tutorial, I will share with you a conditional format rule that allows you to dynamically highlight every alternate set of N columns in Google Sheets. This feature is incredibly useful when you want to visually distinguish different sets of columns in your spreadsheet.

The Formula to Highlight Every Alternate Set of N Columns

To apply this conditional format rule, we will work with the entire worksheet, specifically in the range A1:Z1000. Later on, you can adjust the formula to suit your desired range.

Here’s the formula I used in the example above to fill the background with a light blue color:

Rule #1:

=MOD(COLUMN(A$1) - COLUMN($A$1), $A$1 * 2) < $A$1

And here’s the formula for the light red color:

Rule #2: (Optional)

=MOD(COLUMN(A$1) - COLUMN($A$1), $A$1 * 2) >= $A$1

To control the number of columns in each set, you can simply change the value in cell A1.

Applying the Conditional Format Rules

If you’re new to Google Sheets, follow these steps to set up the two conditional format rules:

Open your file and navigate to the corresponding sheet tab.
Go to the menu Format > Conditional formatting.
Set the Apply to range to A1:Z1000.
In Format Rules > Format cells if > Custom formula is, enter the formula for Rule #1.
Choose the fill color option for the format style (light blue).
Click Add another rule and repeat steps 4 and 5, but this time using the formula for Rule #2 and selecting the light red color.
Click Done.

Now, when you enter a value in cell A1, you will see the alternate sets of columns highlighted according to your chosen colors.

Highlighting Columns Starting From a Different Column

If you don’t want to apply the background coloring to your entire sheet and want to leave some columns in the front, you can modify the formula accordingly.

For example, let’s say you want to highlight every alternate set of 5 columns starting from column D. In this case, you would use the following formulas:

Light Blue:

=MOD(COLUMN(D$1) - COLUMN($D$1), $A$1 * 2) < $A$1

Light Red:

=MOD(COLUMN(D$1) - COLUMN($D$1), $A$1 * 2) >= $A$1

Make sure to set the Apply to range for both rules as D1:Z1000. Then, simply enter the value 5 in cell A1.

That’s all there is to it! With these simple steps, you can highlight every alternate set of N columns in Google Sheets, adding visual clarity and organization to your spreadsheets.

For more useful tips and tricks on Google Sheets, visit Crawlan.com.