Column numbering - lettering

J

JohnnyO

I am so used to the A1 formatting of my excel sheets, somehow the formatting
has been changed to R1C1. How can I change this back?

Is there a reason that I would want to use the R1C1 formatting over the A1
formatting?

Thnaks
 
R

RichardSchollar

Hi Johnny

You can change formula style thru Tools>Options>General>uncheck R1C1
reference style.

Like you, I am so used to A1 style that I find it very difficult to
operate using R1C1 (except when writing formulas to Excel cells thru
VBA, when it is a definite advantage - because you refer to columns
numerically rather than by letter(s)).

Richard
 
B

Bob Phillips

Regarding the second part of the question, I think that few people would
argue in favour of working in R1C1 style, but it can be quite interesting at
times, to better understand what is happening.

For instance, put some numbers in A1:A10, then add this formula in B1: =A1,
and then in B2: = B1+A2. Copy this formula down to A10, that is create a
running total.
You will notice that the formula in B3 becomes =B2+A3, B4 becomes =B3+A4,
etc.
But now switch to R1C1 notation style (reverse the details Richard gave
you), and you will see that B2 says =R[-1]C+RC[-1], B3 says =R[-1]C+RC[-1],
B4 says ... well you get the idea, they are all the same.
From this you can see two things,
firstly the formula is the same formula, Excel is adjusting the A1 notation
to show its relative position, R1C1 doesn't need to it is already showing
that you add the cell 1 column left to the cell 1 row above, and
secondly it shows you more clearly how the formula is really being
evaluated.

Understanding how the R1C1 notation is working, and the syntax, can be very
helpful within VBA, where it is often simpler to construct formulae using
R1C1 notation.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Top