Alternating row colors in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What color will row 5 be with this formula?

You apply this custom formula for alternating row colors in Google Sheets:
=ISEVEN(ROW())
What color will row 5 have if the first color is white and the second color is light gray?

Google Sheets

=ISEVEN(ROW())

A Light gray (second color)

B No color (default)

C White (first color)

D Red (error color)

💡 Hint

Remember that ROW() returns the row number and ISEVEN checks if it is even.

Row 5 is an odd number, so ISEVEN(5) returns FALSE. The formula colors even rows with the second color, so row 5 gets the first color, white.

❓ Function Choice

intermediate

Which formula colors odd rows in alternating colors?

You want to color odd rows with the first color and even rows with the second color using conditional formatting. Which formula should you use?

A =ISEVEN(COLUMN())

B =ISODD(ROW())

C =MOD(ROW(),2)=0

D =ROW()=ODD(ROW())

💡 Hint

Think about which function checks if a number is odd.

ISODD(ROW()) returns TRUE for odd rows, so it colors odd rows with the first color. The other options either check columns or have syntax errors.

🎯 Scenario

advanced

You want to alternate colors every 3 rows. Which formula works?

You want to color rows in groups of 3: first 3 rows one color, next 3 rows another color, then repeat. Which formula should you use in conditional formatting?

A =ISEVEN(INT((ROW()-1)/3))

B =MOD(ROW(),3)=0

C =ISEVEN(MOD(ROW(),3))

D =MOD(INT(ROW()/3),2)=1

💡 Hint

Divide the row number minus 1 by 3, then check if the result is even or odd.

INT((ROW()-1)/3) groups rows in sets of 3 starting at row 1. ISEVEN checks if the group number is even to alternate colors every 3 rows. Other options either check wrong conditions or produce wrong patterns.

📊 Formula Result

advanced

What is the output of this formula for row 10?

Given the formula:
=MOD(ROW()+1,2)=0
What is the result when applied to row 10?

Google Sheets

=MOD(ROW()+1,2)=0

A FALSE

B Error

C TRUE

D Blank

💡 Hint

Calculate ROW()+1 for row 10, then find the remainder when divided by 2.

ROW() for row 10 is 10. 10+1=11. MOD(11,2) is 1, so 1=0 is FALSE. But wait, the formula is =MOD(ROW()+1,2)=0, so for row 10, MOD(11,2)=1, which is not 0, so FALSE. The correct answer is FALSE.

❓ data_analysis

expert

How many rows will be colored with this formula in a 20-row sheet?

You apply this formula for alternating row colors:
=MOD(ROW(),4)<2
How many rows will be colored with the first color in a sheet with 20 rows?

Google Sheets

=MOD(ROW(),4)<2

A 8 rows

B 5 rows

C 12 rows

D 10 rows

💡 Hint

MOD(ROW(),4) cycles through 0,1,2,3. The formula colors rows where this value is less than 2.

MOD(ROW(),4) cycles every 4 rows: values 0,1,2,3. The formula colors rows where the value is 0 or 1 (less than 2). So in every 4 rows, 2 rows are colored. For 20 rows, 20/4=5 cycles, 5*2=10 rows colored.

Practice

1. What is the main purpose of using Alternating row colors in Google Sheets?

easy

A. To protect cells from editing

B. To sort data alphabetically

C. To add formulas automatically

D. To make rows easier to read by shading every other row

Solution

1. Step 1: Understand the feature's function

   Alternating row colors shade every other row to improve readability.

2. Step 2: Compare options with the feature's purpose

   Only To make rows easier to read by shading every other row describes shading rows for easier reading, others describe unrelated features.

3. Final Answer:

   To make rows easier to read by shading every other row -> Option D

4. Quick Check:

   Alternating row colors = shading rows [OK]

Hint: Think: Why shade rows? To read easier! [OK]

Common Mistakes:

• Confusing alternating colors with sorting
• Thinking it adds formulas automatically
• Assuming it protects cells

2. Which of the following is the correct way to apply alternating row colors using a custom formula in Google Sheets?

easy

A. Use formula =ROW() in the custom formula box

B. Use formula =ISEVEN(ROW()) in the custom formula box

C. Use formula =COLUMN()=1 in the custom formula box

D. Use formula =SUM(A1:A10) in the custom formula box

Solution

1. Step 1: Identify formula for alternating rows

   The formula =ISEVEN(ROW()) returns TRUE for even rows, perfect for shading every other row.

2. Step 2: Check other formulas

   =ROW() returns row number, not TRUE/FALSE; =COLUMN()=1 checks columns, not rows; =SUM(A1:A10) sums values, unrelated to row shading.

3. Final Answer:

   Use formula =ISEVEN(ROW()) in the custom formula box -> Option B

4. Quick Check:

   ISEVEN(ROW()) = alternate rows [OK]

Hint: Use ISEVEN or ISODD with ROW() for alternating rows [OK]

Common Mistakes:

• Using ROW() alone without ISEVEN or ISODD
• Confusing columns with rows in formula
• Using unrelated functions like SUM

3. Given the custom formula =ISODD(ROW()) applied for alternating row colors starting from row 2, which rows will be shaded?

medium

A. Rows 1, 3, 5, 7, ...

B. Rows 2, 4, 6, 8, ...

C. Only row 1

D. No rows will be shaded

Solution

1. Step 1: Understand ISODD(ROW()) behavior

   ISODD(ROW()) returns TRUE for rows with odd numbers: 1, 3, 5, etc.

2. Step 2: Consider starting from row 2

   Even if shading starts at row 2, the formula checks actual row numbers, so rows 1, 3, 5... are shaded.

3. Final Answer:

   Rows 1, 3, 5, 7, ... -> Option A

4. Quick Check:

   ISODD(ROW()) = odd rows shaded [OK]

Hint: ISODD(ROW()) shades odd rows, regardless of start [OK]

Common Mistakes:

• Assuming shading starts exactly at row 2
• Confusing odd and even rows
• Thinking no rows are shaded

4. You applied the formula =ISEVEN(ROW()) for alternating row colors but no rows are shaded. What is the most likely reason?

medium

A. The formula was entered without the equals sign

B. The sheet has no data

C. The formula should be =ISODD(COLUMN())

D. Alternating colors only work on columns, not rows

Solution

1. Step 1: Check formula syntax

   Conditional formatting formulas must start with an equals sign to work properly.

2. Step 2: Evaluate other options

   Sheet data presence doesn't affect shading; =ISODD(COLUMN()) shades columns, not rows; alternating colors do work on rows.

3. Final Answer:

   The formula was entered without the equals sign -> Option A

4. Quick Check:

   Formula needs = sign to work [OK]

Hint: Always start formulas with = in conditional formatting [OK]

Common Mistakes:

• Omitting the equals sign in formulas
• Confusing row and column functions
• Believing alternating colors don't work on rows

5. You want to shade every third row starting from row 2 in Google Sheets using a custom formula for alternating colors. Which formula should you use?

hard

A. =ISODD(ROW())

B. =ISEVEN(ROW())

C. =MOD(ROW()-2, 3) = 0

D. =MOD(COLUMN(), 3) = 0

Solution

1. Step 1: Understand the requirement

   Shading every third row starting at row 2 means rows 2, 5, 8, etc. should be shaded.

2. Step 2: Analyze the formula options

   =MOD(ROW()-2, 3) = 0 returns TRUE for rows where (row number minus 2) is divisible by 3, matching rows 2, 5, 8... Others either shade every even/odd row or columns, not every third row.

3. Final Answer:

   =MOD(ROW()-2, 3) = 0 -> Option C

4. Quick Check:

   MOD with ROW() controls row intervals [OK]

Hint: Use MOD(ROW()-start, interval)=0 for custom row patterns [OK]

Common Mistakes:

• Using ISEVEN or ISODD for every third row
• Applying MOD to COLUMN() instead of ROW()
• Not adjusting row number by start offset