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!