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.

Practice

1. Which action selects an entire column in Excel?

easy

A. Click the row number on the left

B. Click the column letter at the top

C. Drag across multiple cells in a row

D. Double-click a cell to edit it

Solution

1. Step 1: Understand column selection

   Clicking the column letter at the top highlights the entire column.

2. Step 2: Differentiate from row selection

   Clicking the row number selects a row, not a column.

3. Final Answer:

   Click the column letter at the top -> Option B

4. Quick Check:

   Select column = Click column letter [OK]

Hint: Click column letter to select whole column fast [OK]

Common Mistakes:

• Clicking row number instead of column letter
• Dragging cells instead of clicking header
• Double-clicking cell to select column

2. Which of these is the correct way to select multiple adjacent rows in Excel?

easy

A. Click the first row number, then hold Shift and click the last row number

B. Click the first cell, then hold Ctrl and click the last cell

C. Click the first column letter, then drag across columns

D. Double-click a row number

Solution

1. Step 1: Select first row

   Click the first row number to start selection.

2. Step 2: Extend selection with Shift

   Hold Shift and click the last row number to select all rows in between.

3. Final Answer:

   Click the first row number, then hold Shift and click the last row number -> Option A

4. Quick Check:

   Select multiple rows = Shift + click row numbers [OK]

Hint: Use Shift + click row numbers to select many rows [OK]

Common Mistakes:

• Using Ctrl instead of Shift for adjacent rows
• Clicking cells instead of row numbers
• Dragging columns instead of rows

3. If you want to select cells from B2 to D4 by dragging, which cells will be included?

medium

A. Cells B2, C3, D4 only

B. Cells B2, B3, B4 only

C. Cells B2, C2, D2, B3, C3, D3, B4, C4, D4

D. Cells D2, D3, D4 only

Solution

1. Step 1: Understand the range B2:D4

   This range covers columns B to D and rows 2 to 4.

2. Step 2: List all cells in the rectangle

   All cells in columns B, C, D and rows 2, 3, 4 are included.

3. Final Answer:

   Cells B2, C2, D2, B3, C3, D3, B4, C4, D4 -> Option C

4. Quick Check:

   Range B2:D4 includes all cells in rectangle [OK]

Hint: Dragging selects all cells in the rectangle between start and end [OK]

Common Mistakes:

• Selecting only one column or row
• Selecting diagonal cells only
• Confusing rows and columns in range

4. You want to select non-adjacent columns A and C. Which method works correctly?

medium

A. Click column A letter, hold Ctrl, then click column C letter

B. Click column A letter, hold Shift, then click column C letter

C. Drag from column A to column C

D. Double-click column B letter

Solution

1. Step 1: Select first column

   Click column A letter to select it.

2. Step 2: Add non-adjacent column

   Hold Ctrl and click column C letter to add it to selection.

3. Final Answer:

   Click column A letter, hold Ctrl, then click column C letter -> Option A

4. Quick Check:

   Select non-adjacent columns = Ctrl + click column letters [OK]

Hint: Use Ctrl + click to select columns not next to each other [OK]

Common Mistakes:

• Using Shift instead of Ctrl for non-adjacent selection
• Dragging selects all columns in between
• Double-clicking selects entire column only

5. You want to select all cells in rows 3 to 5 and columns B to D, but skip row 4. How can you do this?

hard

A. Select rows 3 to 5, then hold Ctrl and click a cell in row 4 to deselect it

B. Select rows 3 to 5, then hold Shift and click row 4

C. Select columns B to D, then drag over rows 3 to 5

D. Select rows 3 and 5 only by holding Ctrl and clicking their numbers

Solution

1. Step 1: Understand the goal

   You want rows 3 and 5 only, skipping row 4, for columns B to D.

2. Step 2: Select non-adjacent rows

   Hold Ctrl and click row numbers 3 and 5 to select them without row 4.

3. Final Answer:

   Select rows 3 and 5 only by holding Ctrl and clicking their numbers -> Option D

4. Quick Check:

   Non-adjacent rows = Ctrl + click row numbers [OK]

Hint: Use Ctrl + click to select specific rows skipping others [OK]

Common Mistakes:

• Trying to deselect with Ctrl after selecting a range
• Using Shift which selects all rows in between
• Selecting columns only without rows