Row and column fields in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the row number of a cell

What is the output of the formula =ROW(B5) in Excel?

A 5

B 2

C B

D Error

💡 Hint

The ROW function returns the row number of the referenced cell.

The formula =ROW(B5) returns 5 because B5 is in the 5th row.

📊 Formula Result

intermediate

Find the column number of a cell

What is the output of the formula =COLUMN(D10) in Excel?

A Error

B D

C 10

D 4

💡 Hint

The COLUMN function returns the column number of the referenced cell, where A=1, B=2, etc.

The formula =COLUMN(D10) returns 4 because column D is the 4th column.

❓ Function Choice

advanced

Choose the correct formula to get the row number of the active cell

Which formula returns the row number of the cell where the formula is entered?

A =ROW()

B =COLUMN()

C =ROW(A1)

D =ADDRESS(ROW(),COLUMN())

💡 Hint

When no argument is given, ROW() returns the row of the current cell.

The formula =ROW() returns the row number of the cell where it is entered.

🎯 Scenario

advanced

Using ROW and COLUMN to create a dynamic range

You want to create a formula that sums a 3x3 block of cells starting from the cell where the formula is entered. Which formula correctly sums the 3 rows and 3 columns starting at the current cell?

A =SUM(OFFSET(ADDRESS(ROW(),COLUMN()),0,0,3,3))

B =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,0,3,3))

C =SUM(OFFSET(A1,ROW(),COLUMN(),3,3))

D =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),1,1,3,3))

💡 Hint

Use ADDRESS to get the current cell address, INDIRECT to convert text to reference, and OFFSET to define the range.

Option B correctly uses ADDRESS(ROW(),COLUMN()) to get the current cell, INDIRECT to convert it to a reference, and OFFSET to select a 3x3 block starting at that cell.

❓ data_analysis

expert

Count how many cells in a range are in even rows

Given the range A1:C10, which formula correctly counts how many cells are in even-numbered rows?

A =COUNTIF(ROW(A1:C10),"=2")

B =COUNTIF(A1:C10,"even")

C =SUMPRODUCT(--(MOD(ROW(A1:C10),2)=0))

D =SUMPRODUCT(--(MOD(COLUMN(A1:C10),2)=0))

💡 Hint

Use ROW to get row numbers, MOD to check evenness, and SUMPRODUCT to count TRUE values.

Option C uses ROW(A1:C10) to get all row numbers in the range, MOD(...,2)=0 to check even rows, and SUMPRODUCT to count them.