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.