R1C1 v A1 notation

Like many people I usually use A1 notation. There was an interesting thread on the Eusprig list a while ago about the benefits of using R1C1 instead (eg easier to see if a block contains similar relative formulas).

The challenge was to use R1C1 for a few days and then go back to A1 and see which you preferred.

I find I prefer A1 in general but every now and then drop into R1C1 for the odd thing. Certainly if you are using .cells(r,c) notation in VBA using R1C1 makes it easy to get the column numbers. But as I pretty much exclusively use named ranges to coordinate VBA and worksheets, its not that big of a win.

If I’m assigning a formula to a range I usually use .FormulaR1C1.

Funnily enough Dennis also emailed me today suggesting this as a topic. I’d be interested to hear what your preference is and why (and when), and what you don’t like about the other system.

I find that since making a conscious effort to try it more, I have found R1C1 to be better than A1 for some things. eg: I find formulas easier to understand in A1, but easier to compare relative ones in R1C1.



5 Responses to “R1C1 v A1 notation”

  1. Jon Peltier Says:

    In Excel I rarely use R1C1. A1 is just more familiar and easier, even with all the $’s to sort out absolute and relative.

    In VBA I very frequently use R1C1. It’s easy to construct the addresses, and you don’t have to adjust a whole table of formulas for the top left cell of the table. In fact, sometimes I store a table of R1C1 formulas as text in a hidden sheet (editing is easier in the sheet), then just copy the range and apply the formulas using FormulaR1C1.

  2. sam Says:

    Excel Formulas – A1
    VBA Formulas -R1C1

  3. Dennis Wallentin Says:

    For all my customized solutions the A1 notation is de facto standard in Excel UI while for VBA it’s R1C1. That’s I develop with R1C1 notation and before sending them to the customers I switch.

    But for all my own work I use R1C1 notation and I find it to be more suitable for me. The funny thing is that in my Swedish group of developers people who started out with Excel in the 80’s use R1C1 while other who started later use the A1 notation.

    Kind regards,

  4. Harlan Grove Says:

    Isn’t R1C1 what Multiplan used to use? A1 is what Visicalc and 123 used.

    This is like asking whether someone thinks of, say, Cuba being so many miles south of Florida or so many degrees of latitude. Both have their uses. A1 style is easier for me to keep a mental image of position, but R1C1 style is definitely more useful for formula debugging.

  5. Ross Says:

    I use A1 most of the time, I have a short cut so i can flick between A1 and “Numbers” for when i am using cells, and also for vlookups.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: