Borders and background colors in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the background color of cell A1 after applying this formula?

You use the formula =IF(B1>100, "#FF0000", "#00FF00") in a conditional formatting rule for cell A1. If B1 contains 150, what background color will A1 show?

A Green (#00FF00)

B Red (#FF0000)

C No color (default white)

D Blue (#0000FF)

💡 Hint

Think about the condition B1 > 100 and what color is assigned when true.

Since B1 is 150, which is greater than 100, the formula returns "#FF0000" which is red. So the background color of A1 becomes red.

❓ Function Choice

intermediate

Which function can you use to set a cell's background color based on its value?

You want to automatically color cells green if their value is above 50 and red if 50 or below. Which Google Sheets feature or function should you use?

A Use Conditional Formatting rules with custom formulas

B Use the IF() function inside the cell to change background color

C Use the COLOR() function in the cell

D Use the SETCOLOR() function in the formula bar

💡 Hint

Think about how colors are usually applied automatically in Google Sheets.

Google Sheets does not have functions like COLOR() or SETCOLOR() to change cell colors. Instead, Conditional Formatting rules with formulas are used to set background colors based on cell values.

🎯 Scenario

advanced

You want to add a thick border only around the outside of a range A1:C3. How do you do this?

You select the range A1:C3. Which border option will add a thick border only around the outside edges of this range, not between the cells inside?

A Select all cells, then click the 'All borders' button

B Select the range, then click the 'Inner borders' button and choose thick line style

C Select each cell individually and add thick borders on all sides

D Select the range, then click the 'Outer borders' button and choose thick line style

💡 Hint

Think about which border option affects only the outside edges of a selected range.

The 'Outer borders' option adds borders only around the outside edges of the selected range. Choosing a thick line style applies that thickness to those outside edges only.

📊 Formula Result

advanced

What happens if you apply this conditional formatting formula to range A1:A5?

Conditional formatting formula: =MOD(ROW(),2)=0 with background color set to light gray. What cells in A1:A5 will have the gray background?

A Cells A2, A4 will be gray

B No cells will be gray

C All cells A1 to A5 will be gray

D Cells A1, A3, A5 will be gray

💡 Hint

Remember ROW() returns the row number. MOD(ROW(),2)=0 means even rows.

The formula checks if the row number is even. Rows 2 and 4 are even, so cells A2 and A4 get the gray background.

❓ data_analysis

expert

You have a table with sales data in B2:E6. You want to highlight cells with sales above the average sales of their row. Which conditional formatting formula correctly applies this?

Given the range B2:E6, which formula should you use in conditional formatting to highlight cells in each row that are greater than the average of that row?

A =B2>AVERAGE(B2:E2)

B =B2>AVERAGE(B$2:E$2)

C =B2>AVERAGE($B2:$E2)

D =B2>AVERAGE($B$2:$E$2)

💡 Hint

Think about how to fix the row reference so it changes per row but columns stay fixed.

Using $B2:$E2 fixes the columns B to E but allows the row number to change as the formatting applies down each row. This compares each cell to the average of its own row.