Column transformations in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the sum of values in column A excluding blanks

Given column A with some numeric values and some blank cells, which formula correctly sums only the numeric values ignoring blanks?

A =SUMIF(A:A, "<>")

B =SUMIF(A:A, ">0")

C =SUMPRODUCT(--(A:A<>""), A:A)

D =SUM(A:A)

💡 Hint

Think about how to multiply a condition by the values to exclude blanks.

Option C uses SUMPRODUCT with a condition that checks if cells are not blank, then multiplies by the values, summing only non-blank cells. Option C sums all including zeros and blanks treated as zero. Option C syntax is incorrect. Option C sums only values greater than zero, excluding zeros and negatives.

❓ Function Choice

intermediate

Choose the formula to convert text in column B to uppercase

You want to transform all text entries in column B to uppercase in column C. Which formula should you enter in C1 and copy down?

A =PROPER(B1)

B =UPPER(B1)

C =LOWER(B1)

D =TEXT(B1, "@")

💡 Hint

UPPER changes text to all capital letters.

Option B correctly converts text to uppercase. Option B converts to lowercase. Option B capitalizes first letters of words. Option B formats text but does not change case.

📊 Formula Result

advanced

Extract the year from dates in column D

Column D contains dates. Which formula in E1 extracts the year as a four-digit number?

A =DATEVALUE(D1)

B =TEXT(D1, "yyyy")

C =RIGHT(D1,4)

D =YEAR(D1)

💡 Hint

Use a function that returns the year number from a date.

Option D uses YEAR function to extract the year number. Option D returns text but "YYYY" is not a valid format code in Excel (should be "yyyy"). Option D tries to get last 4 characters but dates are numbers, so it returns wrong result. Option D converts text to date serial number, not year.

🎯 Scenario

advanced

Combine first and last names from two columns with a space

You have first names in column F and last names in column G. You want to create full names in column H with a space between first and last names. Which formula in H1 achieves this?

A =CONCAT(F1, " ", G1)

B =TEXTJOIN(" ", TRUE, F1, G1)

C =F1+" "+G1

D =F1&G1

💡 Hint

Use a function that joins text with a separator.

Option A uses CONCAT with a space string between first and last names, correctly combining them. Option A joins without space. Option A uses + operator which causes error with text. Option A uses TEXTJOIN which also works but is less common and not supported in older Excel versions.

❓ data_analysis

expert

Count unique values in column J ignoring blanks

Column J contains text values with some duplicates and blanks. Which formula correctly counts how many unique non-blank values are in column J?

A =COUNTA(UNIQUE(FILTER(J:J, J:J<>"")))

B =SUM(IF(FREQUENCY(MATCH(J:J, J:J, 0), MATCH(J:J, J:J, 0))>0,1))

C =COUNTIF(J:J, "<>")

D =SUMPRODUCT(1/COUNTIF(J:J, J:J))

💡 Hint

Use UNIQUE and FILTER to get unique non-blank values, then count them.

Option A uses FILTER to exclude blanks, UNIQUE to get unique values, and COUNTA to count them, which is correct. Option A counts unique but includes blanks causing division errors. Option A is an array formula that may error or count blanks. Option A counts non-blank cells but not unique values.

Practice

1. What does the formula =A2 & " " & B2 do when applied in a new column?

easy

A. Adds the numbers in cells A2 and B2

B. Combines the text from cells A2 and B2 with a space between

C. Subtracts the value in B2 from A2

D. Multiplies the values in A2 and B2

Solution

1. Step 1: Understand the & operator in Excel

   The & operator joins (concatenates) text strings together.

2. Step 2: Analyze the formula =A2 & " " & B2

   This formula joins the content of A2, a space " ", and the content of B2 into one text string.

3. Final Answer:

   Combines the text from cells A2 and B2 with a space between -> Option B

4. Quick Check:

   Concatenate text with & = combined text [OK]

Hint: Use & to join text with spaces easily [OK]

Common Mistakes:

• Thinking & adds numbers instead of joining text
• Forgetting to add space between texts
• Using + instead of & for text

2. Which of these formulas correctly converts the text in cell A2 to uppercase in a new column?

easy

A. =UPPER(A2)

B. =PROPER(A2)

C. =LOWER(A2)

D. =TEXT(A2, "UPPER")

Solution

1. Step 1: Identify the function to convert text to uppercase

   The UPPER() function changes all letters in a text string to uppercase.

2. Step 2: Check each option

   LOWER() makes text lowercase, PROPER() capitalizes first letters, TEXT() formats numbers, so only UPPER(A2) is correct.

3. Final Answer:

   =UPPER(A2) -> Option A

4. Quick Check:

   UPPER() = uppercase text [OK]

Hint: Use UPPER() to change text to all caps [OK]

Common Mistakes:

• Using LOWER() instead of UPPER()
• Confusing PROPER() with uppercase
• Trying to use TEXT() for text case

3. Given the data in columns A and B: A2=10, B2=5, what is the result of the formula =A2*B2+2 in a new column?

medium

A. 52

B. 50

C. 12

D. 15

Solution

1. Step 1: Calculate the multiplication part

   Multiply A2 (10) by B2 (5) to get 50.

2. Step 2: Add 2 to the product

   Add 2 to 50, resulting in 52.

3. Final Answer:

   52 -> Option A

4. Quick Check:

   10*5+2 = 52 [OK]

Hint: Multiply first, then add according to order of operations [OK]

Common Mistakes:

• Adding before multiplying (wrong order)
• Confusing cell references
• Ignoring the +2 part

4. You want to create a new column that shows the full name by combining first name in A2 and last name in B2 with a space. Which formula has an error?

medium

A. =A2 & " " & B2

B. =CONCATENATE(A2, " ", B2)

C. =A2 + " " + B2

D. =TEXTJOIN(" ", TRUE, A2, B2)

Solution

1. Step 1: Understand text joining in Excel

   Text strings are joined with & or CONCATENATE or TEXTJOIN functions, not with +.

2. Step 2: Identify the incorrect formula

   Formula D uses + to join text, which causes an error because + is for numbers.

3. Final Answer:

   =A2 + " " + B2 -> Option C

4. Quick Check:

   Use & or CONCATENATE for text, not + [OK]

Hint: Use & or CONCATENATE, never + for text join [OK]

Common Mistakes:

• Using + to join text strings
• Forgetting quotes around space
• Mixing number and text operators

5. You have a column with dates in A2:A10. You want a new column that shows the month name (like "January") for each date. Which formula should you use in B2 and drag down?

hard

A. =MONTH(A2)

B. =YEAR(A2)

C. =DAY(A2)

D. =TEXT(A2, "mmmm")

Solution

1. Step 1: Understand the goal

   You want the full month name, not just the number.

2. Step 2: Check each formula's output

   MONTH() returns month number, DAY() returns day number, YEAR() returns year number. TEXT(A2, "mmmm") converts date to full month name text.

3. Final Answer:

   =TEXT(A2, "mmmm") -> Option D

4. Quick Check:

   TEXT(date, "mmmm") = month name [OK]

Hint: Use TEXT(date, "mmmm") for full month name [OK]

Common Mistakes:

• Using MONTH() which returns number, not name
• Confusing DAY() or YEAR() with month
• Not using quotes correctly in TEXT format