Font and text styling in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula with text styling?

In Google Sheets, you use the formula =TEXT(A1, "@") where cell A1 contains the text "Hello". What will be the output in the cell with this formula?

Google Sheets

=TEXT(A1, "@")

A Hello

B HELLO (all uppercase)

C Error: Invalid format

D "Hello" (including quotes)

💡 Hint

The TEXT function formats numbers or text according to the format string. "@" means text as is.

The TEXT function with format "@" returns the text exactly as it is in the referenced cell without adding quotes or changing case.

❓ Function Choice

intermediate

Which function changes text to bold in Google Sheets?

You want to make the text in a cell bold using a formula in Google Sheets. Which of these options will achieve that?

A None of these; text styling like bold is done via cell formatting, not formulas

B Use the formula =TEXT(A1, "bold")

C Use the formula =ARRAYFORMULA(BOLD(A1))

D Use the formula =BOLD(A1)

💡 Hint

Think about whether formulas can change font style in Google Sheets.

Google Sheets formulas cannot change font styles like bold or italic. These styles are applied using cell formatting options, not formulas.

🎯 Scenario

advanced

You want to highlight cells with red font if the value is negative. How to do this?

You have numbers in column A. You want the font color to turn red automatically if the number is less than zero. Which method should you use?

A Use the formula =IF(A1<0, "red", "black") in another cell

B Apply conditional formatting with the rule 'Format cells if less than 0' and set font color to red

C Use the formula =TEXT(A1, "red")

D Change the font color manually for each negative number

💡 Hint

Think about automatic font color changes based on cell values.

Conditional formatting allows automatic font color changes based on rules like 'less than 0'. Formulas cannot change font color directly.

📊 Formula Result

advanced

What does this formula output with mixed text and numbers?

Given cell A1 contains the number 123, what is the output of the formula =TEXT(A1, "00000")?

Google Sheets

=TEXT(A1, "00000")

A 123

B Error: Invalid format

C 00123

D 00000

💡 Hint

The format "00000" means show 5 digits, adding leading zeros if needed.

The TEXT function formats the number 123 as a 5-digit number with leading zeros, so it outputs 00123.

❓ data_analysis

expert

How many cells in range B1:B10 have bold font applied?

You have a range B1:B10 with some cells manually set to bold font and others normal. Using Google Sheets formulas only, how many cells are bold?

A Use =COUNTIF(B1:B10, "*")

B Use =SUMPRODUCT(--(GET.CELL(20, INDIRECT("B"&ROW(B1:B10)))=1)) with named range

C Use =COUNTIF(B1:B10, "bold")

D It is not possible to count bold cells using formulas alone

💡 Hint

Think about whether formulas can detect font styles in Google Sheets.

Google Sheets formulas cannot detect font styles like bold. This requires Apps Script or manual counting.

Practice

1. Which toolbar button in Google Sheets would you use to make text bold?

easy

A. The button with a paint bucket icon

B. The button with an I icon

C. The button with a B icon

D. The button with a U icon

Solution

1. Step 1: Identify the bold icon in the toolbar

   The bold button is represented by a capital letter B in bold style.

2. Step 2: Match the icon to the correct function

   Clicking the B icon makes selected text bold.

3. Final Answer:

   The button with a B icon -> Option C

4. Quick Check:

   Bold text = B icon [OK]

Hint: Bold text uses the B icon on the toolbar [OK]

Common Mistakes:

• Confusing italic (I) with bold (B)
• Choosing underline (U) instead of bold
• Selecting paint bucket which changes background color

2. Which of these is the correct way to change the font color of a cell's text in Google Sheets using the toolbar?

easy

A. Right-click the cell and select 'Font Color'

B. Click the paint bucket icon and select a color

C. Click the bold button and choose a color

D. Click the letter A with a color bar underneath and pick a color

Solution

1. Step 1: Identify the font color button on the toolbar

   The font color button shows a letter A with a colored bar below it.

2. Step 2: Understand the paint bucket icon function

   The paint bucket changes the cell background color, not text color.

3. Final Answer:

   Click the letter A with a color bar underneath and pick a color -> Option D

4. Quick Check:

   Font color = A with color bar [OK]

Hint: Font color uses the A icon with a color bar [OK]

Common Mistakes:

• Choosing paint bucket which changes background color
• Thinking bold button changes color
• Right-click menu does not have 'Font Color' option

3. If cell A1 contains the text "Hello" and you apply the formula =UPPER(A1) in cell B1, what will be the font style and text in B1?

medium

A. "HELLO" in uppercase letters, font style unchanged

B. "hello" in lowercase letters, font style bold

C. "Hello" unchanged, font style italic

D. Error because UPPER changes font style

Solution

1. Step 1: Understand the UPPER function effect on text

   The UPPER function converts all letters to uppercase but does not change font style.

2. Step 2: Check the output in cell B1

   Cell B1 will show "HELLO" in uppercase with the default font style (no bold or italic applied).

3. Final Answer:

   "HELLO" in uppercase letters, font style unchanged -> Option A

4. Quick Check:

   UPPER changes text case only [OK]

Hint: UPPER changes text case, not font style [OK]

Common Mistakes:

• Thinking UPPER makes text bold or italic
• Confusing UPPER with formatting tools
• Expecting an error from UPPER function

4. You want to automatically make text red in cells with values less than 50. Which step fixes this conditional formatting rule that is not working?

Rule set: Format cells if "Less than" 50, but text color stays black.

medium

A. Change the rule to "Greater than" 50

B. Set the text color in formatting style to red

C. Remove the conditional formatting rule

D. Change the cell format to bold instead of color

Solution

1. Step 1: Check the conditional formatting rule setup

   The rule "Less than 50" is correct to target cells with values under 50.

2. Step 2: Verify the formatting style applied

   If text color stays black, the formatting style likely does not have text color set to red.

3. Final Answer:

   Set the text color in formatting style to red -> Option B

4. Quick Check:

   Conditional formatting needs style set [OK]

Hint: Set text color in conditional formatting style [OK]

Common Mistakes:

• Changing rule condition instead of style
• Removing rule instead of fixing style
• Changing format to bold does not fix color issue

5. You want to highlight all cells in column B that contain the word "urgent" (case insensitive) by making the text bold and red automatically. Which conditional formatting custom formula should you use?

hard

A. =SEARCH("urgent", B1)

B. =EXACT(B1, "urgent")

C. =REGEXMATCH(B1, "urgent")

D. =LOWER(B1) = "urgent"

Solution

1. Step 1: Understand the need for case-insensitive substring matching

   SEARCH("urgent", B1) finds "urgent" anywhere in the cell ignoring case, returns a number if found (truthy in conditional formatting), or #VALUE! if not (falsy).

2. Step 2: Compare other formulas

   REGEXMATCH is case-sensitive by default; EXACT is case-sensitive exact match; LOWER(B1) = "urgent" requires the entire cell to match exactly after lowering.

3. Final Answer:

   =SEARCH("urgent", B1) -> Option A

4. Quick Check:

   SEARCH for case-insensitive substring [OK]

Hint: SEARCH for case-insensitive substring search [OK]

Common Mistakes:

• Using REGEXMATCH which is case-sensitive
• Using EXACT which is case-sensitive exact match
• Using LOWER(B1) = "urgent" which matches exact cell content only