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.

Practice

1. What is the main purpose of adding borders to cells in Google Sheets?

easy

A. To separate and organize data visually

B. To change the font style of the text

C. To calculate sums automatically

D. To protect cells from editing

Solution

1. Step 1: Understand the role of borders

   Borders are lines around cells that help separate and organize data visually.

2. Step 2: Compare with other options

   Changing font style, calculating sums, or protecting cells are different features unrelated to borders.

3. Final Answer:

   To separate and organize data visually -> Option A

4. Quick Check:

   Borders = Organize data visually [OK]

Hint: Borders help you see data sections clearly [OK]

Common Mistakes:

• Confusing borders with font or formula features
• Thinking borders calculate values
• Assuming borders protect cells

2. Which of the following is the correct way to add a background color to a cell in Google Sheets?

easy

A. Right-click the cell and select 'Insert formula'

B. Select the cell, then click the paint bucket icon and choose a color

C. Double-click the cell and type the color name

D. Use the SUM function to apply color

Solution

1. Step 1: Identify how to add background color

   In Google Sheets, you select the cell and click the paint bucket icon on the toolbar to pick a background color.

2. Step 2: Eliminate incorrect options

   Right-clicking for formulas, typing color names, or using SUM function do not apply background colors.

3. Final Answer:

   Select the cell, then click the paint bucket icon and choose a color -> Option B

4. Quick Check:

   Paint bucket icon = Add background color [OK]

Hint: Use the paint bucket icon to color cells fast [OK]

Common Mistakes:

• Trying to type color names directly in cells
• Confusing formulas with formatting
• Looking for color options in the wrong menu

3. You apply a thick red border to cells A1 to A3 and a light blue background color to cells A2 and A3. What will cell A2 look like?

medium

A. It has a thick red border and a light blue background

B. It has no border and no background color

C. It has a thick red border but no background color

D. It has a light blue background but no border

Solution

1. Step 1: Understand border application

   Cells A1 to A3 have a thick red border, so A2 has this border.

2. Step 2: Understand background color application

   Cells A2 and A3 have a light blue background, so A2 also has this background color.

3. Final Answer:

   It has a thick red border and a light blue background -> Option A

4. Quick Check:

   Border + background color = thick red border + light blue background [OK]

Hint: Borders and backgrounds can combine on the same cell [OK]

Common Mistakes:

• Assuming background color overrides borders
• Thinking borders apply only to one cell
• Ignoring overlapping formatting

4. You want to remove all borders from cells B1 to B4 but accidentally only remove the outer borders. What is the likely cause?

medium

A. You deleted the cell contents instead of formatting

B. You applied a background color instead of removing borders

C. You selected 'Clear borders' but only chose outer borders option

D. You used the paint bucket icon instead of border options

Solution

1. Step 1: Identify border removal method

   Removing borders requires selecting all borders or 'Clear borders' fully, not just outer borders.

2. Step 2: Understand partial removal effect

   If only outer borders are removed, inner borders remain, causing incomplete removal.

3. Final Answer:

   You selected 'Clear borders' but only chose outer borders option -> Option C

4. Quick Check:

   Partial border removal = only outer borders cleared [OK]

Hint: Choose 'Clear all borders' to remove every border [OK]

Common Mistakes:

• Confusing background color with border removal
• Deleting data instead of formatting
• Using paint bucket icon for borders

5. You want to highlight a group of cells from C1 to C5 with a yellow background and add a thick green border only around the entire group (not between cells). Which steps should you follow?

hard

A. Select C1:C5, apply thick green border to inner borders only, then yellow background to all

B. Select C1:C5, apply thick green border to all cells, then add yellow background to C1 only

C. Select C1:C5, apply yellow background to C1 only, then thick green border to each cell

D. Select C1:C5, apply yellow background, then choose thick green border 'Outer border' only

Solution

1. Step 1: Apply background color to the group

   Select cells C1 to C5 and apply yellow background to all selected cells.

2. Step 2: Add thick green border only around the group

   Choose the thick green border style and apply it as 'Outer border' so it surrounds the entire group without inner lines.

3. Final Answer:

   Select C1:C5, apply yellow background, then choose thick green border 'Outer border' only -> Option D

4. Quick Check:

   Outer border + full background = highlight group clearly [OK]

Hint: Use 'Outer border' to frame a cell group cleanly [OK]

Common Mistakes:

• Applying borders to each cell instead of outer border
• Adding background to only one cell
• Using inner borders instead of outer border