How to Color Alternating Rows and Columns in Excel

This updated guide explains how to Color Alternating Rows and Columns in Excel through clear steps, practical examples, and important checks that help prevent common errors.

1. Coloring Alternating Rows

Step 1: Open the Excel file -> select the lines to be alternated coloring -> Go to the HOME tab -> Conditional Formating -> New Rule .

Step 2: The dialog box appears, click Use a format to determine which cells to format -> in Format values where this formula is true enter the formula = MOD (ROW (), 2) (depending on the version using commas or dots comma) -> Click Format and select fill color.

Step 3: Choose the Fill tab -> select the fill color in BackGround Color -> OK .

Result:

Step 4: To change the fill color you do the following:

Go to tab HOME -> Conditional Formatting -> New Rule .

Step 5: The dialog box appears, click Format dialog box appears, select the fill color.

Result:

2. Colored Alternating Columns

Similar to other alternating row coloring only:

- In Step 1 : Select the column to be colored.

- In Step 2 : Enter the formula = MOD (COLUMN (), 2) .

Result:

3. Colored Alternating Rows of Columns Provided There Is Data and No Data

If the column is in odd (or even) position and has data ? coloring. If the column in an even (odd) position has data, no coloring.

Same as the above steps but in step 2 enter the following formula:

= And (Mod (Row (), 2), CountA ($ A1: $ G9) : This statement will color the row at odd places with data if not containing data, do not color or column at even position. nor color.

Result: The fourth row has data, but because of the even cell, it does not color.

You can also reverse the even line with data ? coloring, odd lines or no data and even lines without data ? no color by changing the statement: = And (Mod (Row ( ), 2)> 0, CountA ($ A1: $ G9) .

Above are some tips for alternating line and column coloring.