Selecting cells, rows, and columns in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Selecting a range of cells for sum

You want to sum the values in cells from B2 to B6 in Excel. Which formula will correctly calculate this sum?

A =SUM(B2-B6)

B =SUM(B2:B6)

C =SUM(B2,B6)

D =SUM(B2;B6)

💡 Hint

Think about how Excel defines a continuous range of cells.

The colon (:) is used to select a range of cells from the first to the last. So B2:B6 means all cells from B2 down to B6. The other options either use wrong separators or operators.

❓ Function Choice

intermediate

Selecting entire row for average

You want to calculate the average of all values in row 4. Which formula correctly selects the entire row 4 in Excel?

A =AVERAGE(A4:A1048576)

B =AVERAGE(A4:Z4)

C =AVERAGE(4)

D =AVERAGE(4:4)

💡 Hint

Excel allows selecting entire rows by using row numbers with a colon.

Using 4:4 selects the entire row 4. Option D selects only columns A to Z in row 4, which may miss data beyond Z. Option D is invalid syntax. Option D selects a column range, not a row.

🎯 Scenario

advanced

Selecting columns with mixed data

You have data in columns A to D. Columns A and B contain numbers, C contains text, and D contains dates. You want to sum all numbers in columns A and B for rows 2 to 10. Which formula correctly selects these cells?

A =SUM(A2:B)

B =SUM(A:B)

C =SUM(A2:B10)

D =SUM(A2:D10)

💡 Hint

Focus on selecting only the numeric columns and the correct rows.

Option C selects columns A and B from rows 2 to 10, which are numeric. Option C includes columns C and D which have text and dates, causing errors or wrong sums. Option C sums entire columns A and B, including unwanted rows. Option C is invalid syntax.

📊 Formula Result

advanced

Selecting non-adjacent cells for sum

You want to sum values in cells A1, C1, and E1 only. Which formula correctly sums these non-adjacent cells in Excel?

A =SUM(A1,C1,E1)

B =SUM(A1:C1:E1)

C =SUM(A1:C1,E1)

D =SUM(A1-E1)

💡 Hint

Think about how to list multiple separate cells in a formula.

Option A correctly lists the cells separated by commas. Option A uses invalid colon separators multiple times. Option A tries to mix range and cell but is invalid. Option A uses subtraction operator which is wrong.

❓ data_analysis

expert

Counting non-empty cells in a column

You want to count how many cells in column B (from B2 to B100) are not empty. Which formula will give the correct count?

A =COUNTA(B2:B100)

B =COUNT(B2:B100)

C =COUNTBLANK(B2:B100)

D =COUNTIF(B2:B100,"")

💡 Hint

Remember that COUNTA counts non-empty cells, while COUNT counts only numbers.

COUNTA counts all non-empty cells regardless of data type. COUNT counts only numeric cells. COUNTBLANK counts empty cells, and COUNTIF with empty string counts empty cells too.