In this tutorial, you will get a conditional format rule to dynamically highlight every alternate set of n columns in Google Sheets.

For example, if n = 2, with the said rules, we can highlight columns A and B with light blue color, C and D with light red color, and repeat the same.

You can control the set of n columns within a cell by referring to that cell in the formula.

Similar to highlighting every alternate set of n columns, I have a formula for the rows, which you can find here – Highlight a Set of Alternate Rows in Google Sheets.

Formula to Highlight Every Alternate Set of N Columns

We will apply the conditional format rule for an entire worksheet, i.e., in the range A1:Z1000.

Later we can adjust the formula for our required range.

I’ve used the following formula in the above example to fill the background with a light blue color.

Rule # 1:

=mod(column(A$1)-column($A$1),$A$1*2)<$A$1

And the below formula for the light red.

Rule # 2: (Optional)

=mod(column(A$1)-column($A$1),$A$1*2)>=$A$1

I’ll explain how to insert the above two rules to highlight every alternate set of n columns in Google Sheets.

Before that, please note that the n in the above formulas is the cell reference $A$1 which is highlighted in green.

That means you can control the number of columns in each set from cell A1.

A1 value and how it affects the highlighting:

If you don’t want dynamic behavior, then replace $A$1 with the corresponding number.

If you are a newbie, follow the below steps to set up the above two conditional format rules in Google Sheets.

Steps to Apply the Rules to Highlight Alternate Set of N Columns

First, open the file and go to the corresponding sheet tab. Then follow the steps below.

1. Go to the menu Format > Conditional formatting.
2. Apply to range – A1:Z1000.
3. Format Rules > Format cells if > Custom formula is – enter above Rule # 1 formula.
4. Format Rules > Format style – select fill color (light blue).
5. Click Add another rule and edit steps 3 and 4 above. In step 3, insert the above Rule # 2 formula, and in step 4, choose the light red color.
6. Click Done.
7. Enter 3 in cell A1 and see what happens.

We have learned how to highlight every alternate set of N columns in Google Sheets.

Fill Color Starting From a Different Column (Other than A)

I know you may not use the above background coloring in your entire sheet.

You may want to leave a few columns in the front.

For example, keep columns A to C out of the covered area of the above highlighting rules.

So it’s crucial to know how to modify my formula to apply the fill color starting from a different column other than A.

You can use the below two formulas to highlight every alternate set of 5 columns from column D.

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

The Apply to the range for both the rules must start from column D, i.e., D1:Z1000.

Finally, enter 5 in cell A1.

That’s all. Thanks for the stay. Enjoy!

Resources

• How to Highlight Every Nth Row or Column in Google Sheets.
• How to Highlight Only Texts in a Column or Row in Google Sheets.
• Highlight Duplicates in Single, Multiple Columns and all Cells in Google Sheets.
• Highlight the Earliest Events Based on the Date Column in Google Sheets.
• Google Sheets: Highlight Rows When the Value Changes in Any Column.
• Highlight the Top 10 Ranks in Single or Each Column in Google Sheets.
• How to Highlight an Entire Column in Google Sheets.
• Applying Alternating Colors to Visible Rows in Google Sheets & Excel.