Article



Jan
31
2012

Excel 2010 – Ch:01:F – R1C1 Cell Referencing

Babu
Posted 2 years 78 days ago ago by Babu     
  • Currently 1.60/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
  
 

Advanced Excel 2010 – Ch:01:F – R1C1 Cell Referencing

In the previous article I covered navigation using keyboard, where in I introduced you to the GoTo dialog which requires you to type in the Cell address or the name of the cell (or cell range). Not just to navigate, but understanding Cell referencing is very important to get all that is offered by excel.

We can refer to the cells either using the A1 reference style or R1C1 reference style.

In this article, I will just give you an introduction to Cell referencing, while I will get into the specifics later on in this tutorial.

A cell is an intersection of a row and a column. We have a total of 17,179,869,184 cells in Excel 2010 worksheet.

In Excel, by default the columns are identified by letters (which start with A and end at XFD, a total of 16,384 columns) and rows are identified with numbers, (starting with 1 and ending at 1048576). These letters and numbers are called row and column headings.

Most of us like to identify the rows & columns with letters & numbers respectively, since it makes it easy to say the difference between the two.

This type of cell referencing style is known as A1 reference style.

Also as I mentioned earlier, this is also the default style. In other words, this is how it works when we install Excel and use it for the first time.

It allows you to use short cell addresses like A1, E20, Z55, and so on. Excel uses the A1 referencing style for cell addressing, when you install it.

While the A1 referencing style is the default and the most commonly used, this does not mean that we have to stick to this style of referencing even if we are not comfortable with it.

For those who would want to try a different type referencing system, Excel provides us with R1C1 style addressing system. In R1C1 style, both the columns and rows are identified with numbers.

In this style, the cell address A1 will be referred to as R1C1 (read as Row 1, Column 1). In this case the letters R stands for Row and C stands for Column.

To change the referencing style to R1C1 style for a worksheet, select File and then Options. This shows the Excel Options dialogue box, which you have already seen in our previous article. From the list of options on the left hand side, select Formulas. Then under the “Working with formulas” section, click on the Check box next to the words “R1C1 reference style” and then press OK.

Excel006

This will change the column headings from letters to numbers. Also if you notice the name box, it will be displaying the current cell address in R1C1 address. Try the GoTo box and even there it will only accept the values in R1C1 values.

However, R1C1 addressing is something that is not commonly used. This style of referencing will come in handy when the worksheet we are working on has more than 26 columns. In other words, when Excel runs out of letters after column 26 (which is Z), it starts using two letters where in column 27 will be AA and the next will be AB and so on. While this method is not something very difficult to remember, it’s also not as easy as working with numbers.

For example, can you tell me which column is CV100? It’s not that simple isn’t it? But how would it be if I say R100C100, it now becomes easy for you to remember isn’t it. CV is the equivalent to C100 in A1 style.

Before ending this article, I just want to remind you that irrespective of whatever type of style of referencing you use, it remains a file-specific setting. This means that, even if you have set your style of referencing to A1 style, if you open a file that was created using R1C1 style, then you will see the column headers in R1C1 style only. And the same applies to files that were created using A1 referencing style.

However, this does not mean that you will have to work in whatever format they were created. You can always go to the Excel Options window and change the settings as you like to work with.

While in this article, I just gave an introduction to the two different styles of referencing or cell addressing, I will get into the details of cell referencing when we start with formulas.


Leave Your Comments