Get the Row or Column Number of a Cell in Excel

Add to Favorites

How to get the row or column number of the current cell or any other cell in Excel.

This tutorial covers important functions that allow you to do everything from alternate row and column shading to incrementing values at specified intervals and much more.

We will use the ROW and COLUMN function for this.  Here is an example of the output from these functions.

Though this doesn't look like much, these functions allow for the creation of powerful formulas when combined with other functions.  Now, let's look at how to create them.

Get a Cell's Row Number

Syntax

=ROW([reference])

This function returns the number of the row that a particular cell is in.

If you leave the function empty, it will return the row number for the current cell in which this function has been placed.

=ROW()

If you put a cell reference within this function, it will return the row number for that cell reference.

=ROW(A1)

This example would return 1 since cell A1 is in row 1.  If it was =ROW(C24) the function would return the number 24 because cell C24 is in row 24.

Examples

Now that you know how this function works, it may seem rather useless.  Here are links to two examples where this function is key.

Increment a Value Every X Number of Rows in Excel

Shade Every Other Row in Excel Quickly

Get a Cell's Column Number

Syntax

=COLUMN([reference])

This function returns the number of the column that a particular cell is in.  It counts from left to right, where A is 1 and B is 2 and so on.

If you leave the function empty, it will return the column number for the current cell in which this function has been placed.

= COLUMN ()

If you put a cell reference within this function, it will return the column number for that cell reference.

= COLUMN (A1)

This example would return 1 since cell A1 is in row 1.  If it was =COLUMN (C24) the function would return the number 3 because cell C24 is in column number 3.

Examples

The COLUMN function works just like the ROW function does except that it works on columns, going left to right, whereas the ROW function works on rows, going up and down.

As such, almost every example where ROW is used could be converted to use COLUMN based on your needs.

Notes

The ROW and COLUMN functions are building blocks in that they help you create more complex formulas in Excel.  Alone, these functions are pretty much worthless, but, if you can memorize them and keep them for later, you will start to find more and more uses for them when working in large data sets.  The examples provided above in the ROW section cover only two of many different ways you can use these functions to create more powerful and helpful spreadsheets.


Excel Function: COLUMN(), ROW()
Downloadable Files: Excel File

Similar Content on TeachExcel
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the for...
Reverse the Contents of a Cell in Excel - UDF
Macro: Reverse cell contents with this free Excel UDF (user defined function). This will mir...
Excel Function to Remove All Text OR All Numbers from a Cell
Tutorial: How to create and use a function that removes all text or all numbers from a cell, whichev...
Return the ISO Week Number from a Date in Excel - UDF
Macro: Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defin...
Display the AutoFilter Criteria Applied to a Data Set in a Cell in Excel - UDF
Macro: This free Excel UDF (user defined function) allows you display the filter criteria tha...