In Excel, there are specific use cases where an exact row (column) number, or an exact number of rows (columns), are necessary for calculations.
The ROW function returns the row number of a reference.
The ROW function syntax is as follows:
= ROW ( reference )
If reference is omitted, the number of the row in which the formula appears is returned.
The COLUMN function returns the column number of a reference.
The COLUMN function syntax is as follows:
= COLUMN ( reference )
If reference is omitted, the number of the column in which the formula appears is returned.
Typical returns of the ROW and COLUMN functions look like this:
ROW and COLUMN functions are often combined with the INDIRECT and/or ADDRESS functions.
Here, ROW and COLUMN functions are nested inside the ADDRESS function in order to return a cell’s address:
ROW function also provides a convenient way of generating an array of consecutive numbers.
Here, we will count the number of rows using such an array:
For more practical applications, see Top 10 lists in Excel with formulas.
As for this concrete example, the same result could be achieved easier by using the ROWS function.
The ROWS function returns the number of rows in a reference or array.
The ROWS function syntax is as follows:
= ROWS ( array )
The COLUMNS function returns the number of columns in a reference or array.
The COLUMNS function syntax is as follows:
= COLUMNS ( array )
Typical returns from the ROWS and COLUMNS functions look like this:
In our final example, we will demonstrate how to calculate the average using the ROWS function:
Here, we are calculating the average refund per complaint.
The AVERAGE function, the COUNT function, or the COUNTA function cannot be used to correctly calculate the average in this example given the gaps in our data.
Only using the ROWS function allowed the correct denominator to be calculated, and with it, the correct average.
Dig deeper:
4 thoughts on “ROW & COLUMN functions”