Instructions on how to color alternating rows and columns in Excel

The following article details how to color alternating rows and columns in Excel.

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.

Good luck!