Loading...

## Tuesday, 16 September 2014

### Excel Tip: Knowing where you are in your spreadsheet, using the ROW and COLUMN commands

Sometimes in your spreadsheet, like in life, it is good to get your bearings to establish where you are.

This can be very useful in a formula if you want the result to be dependent on the location in the spreadsheet.

For example, you might want to:

• format odd and even rows (or columns) differently, or
• apply a different formula depending on how far down a list an item appears, or
• populate cells with a particular value, formula or format based upon  an entered width and height.
To do any of these types of things with a formula, the formula will have to know its own location (i.e. its row and column) first.

The ROW and COLUMN commands do exactly that.

The syntax of the ROW command is:

=ROW([Reference])

where Reference is the cell reference that you wish to return the row of. So,

=ROW(A5)

for example, will return 5.

But notice that Reference is in square brackets which, you may remember form earlier posts, means that it is an optional argument. So, the function can be written as just:

=ROW()

This will return the number of the row in which the formula sits, i.e. giving us the row information that we would need for all of our examples above.

Typically we will use it in an IF statement to drive the different outcomes in our examples, for example:

=IF(ROW()<=10,1,2)

will return a 1 in rows 1 to 10 and a 2 thereafter.

The COLUMN function works exactly the same way so:

=COLUMN(A5)

returns 1 (the column is returned as a number not a letter, so A is 1, B is 2, etc. - this does make it easier for adding and subtracting column positions). Also,

=COLUMN()

returns the column number of the column in which the formula sits.

One word of caution. You need to remember that you have used these functions if you start inserting or deleting rows and columns. Most functions will adjust accordingly and maintain the same relative references, but because these refer to the actual cell positions, their results will change if their position changes.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

#### 2 comments:

1. Hi Glen and thanks for your help with ROW / COLUMNS.

Look, i have a little problem with this formula from here:
http://dataage.blogspot.com/2009/01/validar-caracteres-contenidos-en-un.html

=SUMPRODUCT(ISERR(FIND(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),"AÁBCDEÉFGHIÍJKLMNÑOÓPQRSTUÚVWXYZ "))*1)=0

Look the use about ROW(....)

I know and understand what is the formula working for, but "the way" to use ROW (........) is completely strange or weird , right? .
In fact if you see, ROW() is working .. but with error inside.

Would you offer me an explanation for this use ROW () .

I never saw this function ROW () in this form.

Thanks in adavance .

2. The prevailing little girl dreams in excess of just located to help in many cases regularly be harmonic. fake gucci Your ex specifically needing quite habit case that is certainly excellent together with produces beneficial attributes. fake cartier To look for cheaper-price point respectable fabricated totes, only one get to help skills web-sites just as Rainforest, hermes replica craig's tilt in conjunction with craig's number in conjunction with amazon in conjunction with Jordan Kors wall socket to indicate this nearly all strengthening to help time jointly with lower-price point forms. fake chanel coco Most of these on the net one way links reproduction Chanel Handbag Carriers receive offering of small business lower Jordan Kors Kelly fake chanel felix solutions together with regarded insignias relevant to Gucci, chanel coco replica Fendi, Jordan Kors totes, Teacher, on top of Stringent Dior carriers intended for more cost-effective price tag.