# Which reference style is best?  R1C1 or A1?



## Jon von der Heyden (Jun 7, 2011)

I'd say about 99% of formulas posted on this board use A1 reference style.  But the more I use R1C1 the more I like it.

So, which reference style do you find better, and why?


----------



## Jon von der Heyden (Jun 7, 2011)

Just to be clear I'm referring to benefits using the reference style in your workbook, not what you use in VBA.

I have gone for R1C1.  Although at the moment my default is on A1 I think I'm going to move to R1C1.  The way I see it, if I want to refer to a cell by it's position on the grid then I should use a absolute reference.  Otherwise generally I find it better to refer to a cell by it's relative position...


----------



## RoryA (Jun 7, 2011)

Statistically speaking, only sociopaths use R1C1 style in the main UI.


----------



## shg (Jun 7, 2011)

Too many brackets to type.


----------



## T. Valko (Jun 7, 2011)

A1 all the time.

This just doesn't look cool:

=SUM(R[660320]C[14941]:R[660329]C[14941])


----------



## SydneyGeek (Jun 7, 2011)

A1 for the worksheet. But definitely R1C1 for VBA. 

Denis


----------



## Richard Schollar (Jun 8, 2011)

I agree with all the above.  A1 all the way in the worksheet


----------



## Sandeep Warrier (Jun 8, 2011)

A1 for me too  ... I find that at times R1C1 gets too confusing!


----------



## Domski (Jun 8, 2011)

A1 all the way you nutter 

Dom


----------



## yytsunamiyy (Jun 8, 2011)

unless somebody stands behind me with a very big stick - A1


----------



## TinaP (Jun 8, 2011)

R1C1 confuses me so I avoid it in the workbook.  I like it in VBA, though, mostly because of the flexibility and the fact that I can record the formula.


----------



## diddi (Jun 8, 2011)

since i use vba the most i go for r1c1, but not on sheets


----------



## Jon von der Heyden (Jun 8, 2011)

Right, well almost everyone is for A1.  So I see one other voted R1C1.  Who was that?  Bit of support here please! 

Despite the responses I'm still going to wean myself off the A1 and get used to R1C1.  I disagree with you Tony, R1C1 looks waaaaay cooler. 



> Statistically speaking, only sociopaths use R1C1 style in the main UI.


Right!


----------



## shg (Jun 8, 2011)

It's better for seeing the 'identicality' of formulas, for sure.


----------



## SydneyGeek (Jun 8, 2011)

I prefer to use partial references if I'm going to use R1C1 for creating formulas in VBA; much easier to have something always refer to (say) RC3, instead of RC[-2] which, after some rearranging of the worksheet, needs to be rewritten as RC[-5]. 

Denis


----------



## xenou (Jun 9, 2011)

You have support:

_Excel Best Practices for Business_ by  Loren Abdulezer begins with  chapter 1 and a big endorsement of R1C1 as the right way to go.
I wasn't convinced enough to switch, but I did figure out that switching  on the fly to R1C1 was useful for validating formulas as you can tell  at a glance if formulas are identical or not as you scan a column/row.

I think I agree with Biff - not cool!


----------



## Darren Bartrup (Jun 10, 2011)

Definitely A1 all the way, though I do switch to R1C1 when I'm counting lots of columns for a VLOOKUP or OFFSET type formula.


----------



## Cbrine (Jun 13, 2011)

yytsunamiyy said:


> unless somebody stands behind me with a very big stick - A1



Funny...When I worked in IT, this was my solution to stop the users from doing "STUPID" stuff.  

A1 for me, in the sheets and in VBA.


----------



## u76200 (Oct 24, 2016)

I am only five years late on this thread, but also use R1C1 for everything. It is just so much more powerful and more intuitive - I mean it is a coordinate system after all. A1 is ok for small uncomplicated things, but if you have a lot of data columns and rows, R1C1 just shines. Firstly, the formulas never change in a column. It its just so much easier to debug complex spreadsheets. Secondly referring to column CF is totally counter intuitive, C84 if much more intuitive at face value. I also often use absolute references unless I specifically need to have a relative reference. This keeps formulas short and easy to read. I think it is simply a much, much better and more sustainable practice to use R1C1 all the way. I have been programming with Excel from pre VBA days.


----------



## jbenfleming (Dec 12, 2017)

SydneyGeek said:


> A1 for the worksheet. But definitely R1C1 for VBA.
> 
> Denis




Why exactly do you prefer r1c1 in VBA?


----------

