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 .

Instructions on how to color alternating rows and columns in Excel Picture 1

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.

Instructions on how to color alternating rows and columns in Excel Picture 2

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

Instructions on how to color alternating rows and columns in Excel Picture 3

Result:

Instructions on how to color alternating rows and columns in Excel Picture 4

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

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

Instructions on how to color alternating rows and columns in Excel Picture 5

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

Instructions on how to color alternating rows and columns in Excel Picture 6

Result:

Instructions on how to color alternating rows and columns in Excel Picture 7

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) .

Instructions on how to color alternating rows and columns in Excel Picture 8

Result:

Instructions on how to color alternating rows and columns in Excel Picture 9

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.

Instructions on how to color alternating rows and columns in Excel Picture 10

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

Instructions on how to color alternating rows and columns in Excel Picture 11

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!