[an error occurred while processing this directive]
Issue dated - 16th December 2002





 Network Sites
  IT People
  Network Magazine
  Business Traveller
  Exp. Hotelier & Caterer
  Exp. Travel & Tourism
  Exp. Backwaters
  Exp. Pharma Pulse
  Exp. Healthcare Mgmt.
  Express Textile
 Group Sites
  Indian Express
  Financial Express

Front Page > TechSpace > Story Print this Page|  Email this page

Cell referencing in Excel for everyone!

Tech Forum - Dr Nitin Paranjape

This week I was planning to write three or four snippets of information rather than a full article. That way, I reckoned, I would be able to cover more topics and more readers.

If the article has a single topic, then it is possible that particular types of readers find it useful but others don’t find it relevant at all. So one of the topics I thought I’d do was dynamic ranges in Microsoft Excel. But when I started writing about it and delved further into the cell referencing of Excel, I came across such a vast amount of useful information that I could not resist writing an entire article for it. As far as usefulness to specific types of readers is concerned, I don’t have to worry about it for this article. The reason is: Everyone uses Excel!

What’s more, even the title changed thrice while was writing the article. Initially the title was “Dynamic Cell Referencing in Excel Programming”, then it changed to “Cell referencing in Excel programming” and finally to “Cell referencing in Excel for everyone!”. The “for everyone” part is important. The information given here is useful to every reader—whether you are an end-user, administrator, analyst, project manager or developer!

An Excel worksheet is a basically grid of rows and columns. While creating applications using Excel, we need to refer to individual cells or range of cells. Detailed knowledge of various methods of referring to a cell or set of cells is important for efficient Excel usage and programming. There are many ways of cell referencing which are commonly not known. If used, you could achieve a much higher level of flexibility and creativity in the way you use Excel.

Cell referencing styles
There are two styles of cell referencing: A1 style and R1C1 style. Most of us use A1 style by default. However, you can change it to R1C1 styles from the Tools - Options - General tab. It’s important to note that you cannot use both styles together. It is either A1 or R1C1. Both these styles are used to refer to a single cell or a group of contiguous/non-contiguous cells (called a range).

Programmatically, you can change the referencing style by using the method:

Application.ReferenceStyle = xlA1 or xlR1C1

Absolute and relative numbering
There are two types of cell referencing styles. It’s key that you know that, when you reference a cell or range from another cell in a formula. If you copy the formula to another cell, the referencing can either change or not change. Referencing is called absolute when the change in location of your formula does not change the referenced cell or range. Referencing is called relative when changing the formula location changes the referenced cells appropriately. From that perspective the A1 type of referencing is by default Relative and R1C1 type referencing is by default Absolute. This means you have to take additional effort to make the A1 style referencing absolute. This is done by prefixing a $ before the row and/or column identifier. For example $A$1 is absolute reference which could simply have been described as R1C1.

It also means you have to take additional effort to make R1C1 style referencing relative. To make this type of reference relative you have to put some numbers in square brackets after the R and C identifier. For example, suppose you are in the cell which is the fourth row and first column of the worksheet and want to refer to the first row and column intersection cell. The reference then becomes R[-3],C1. Believe me; this fact of life is completely fuzzy in the minds of most users of Excel.

Named reference
Whenever you want to refer to a particular value or values in many formulas, you need to use absolute reference. For example, you have a conversion factor from US$ to Rupees in cell A13. You want to perform this conversion at many places within your worksheet. At all times you will have to refer to it as $A$13. This will prevent losing the context even if the formulas are moved to another location. This is common knowledge. However, the disadvantage of this way of naming is that it becomes confusing to understand, modify and debug formulas when you have many such values in a complex sheet. You have to remember that $A$13 means dollar conversion rate and $F$235 means octroi rate! Would it not be nice if we could call the reference $A$13 “dtor” (short for dollar to rupees) and $F$235 as “octroi”? This is possible. To name a cell follow these instructions:

  • Select the cell (just click on it)
  • From Insert menu choose Name - Define
  • Type a name you would understand easily (say “dtor” in this example)
  • Choose Ok

    That’s it! Now you can refer to this cell in formulas like this:

    = A1*dtor

    To define names there is even a shortcut method available.

  • Just click on the cell
  • Now click on the name box. Type the name you want to assign to it in the name box
  • Press Enter. That’s it.

What is all this name box business now, you ask? Nothing complicated. You have always seen it but did not know that it had other uses! Name box is a small area next to the formula bar in the toolbars. Normally this area shows the reference name of any cell or range you have currently selected. You can also use names for ranges of cells.

Cell referencing methods
Here is a list of various ways in which cells, ranges and special ranges (will be explained later) can be referenced. All these can also be used in direct formula entry while handling Excel in interactive mode as well. Useful examples of the usage of a particular referencing method are given below. These examples (See table) refer to the serial number of the referencing method for convenience.

Serial No Method What is referenced? Type Usage
1 A1 Single cell Relative A is column identifier, 1 is row identifier
2 A1:B3 Range Relative This references 6 cells
3 5:05 Range Relative All cells in row 5
4 B:B Range Relative All cells in column B
5 5:07 Range Relative All cells in rows from 5 to 7
6 B:E Range Relative All cells in columns from B to E
7 A$1$ Cell Absolute The cell A1. Cut-pasting formula to another location will not change the reference.
8 A$1 Cell Mixed The $ makes the column reference absolute, leaving the row reference relative
9 R1C1 Cell Absolute R1 is row 1, C1 is column 1
10 R1C1:R3C2 Range Absolute Same as A1:B3 as shown above
11 R Range Absolute Reference to current row
12 C Range Absolute Reference to current column
13 R[-2]C Cell Relative Cell two rows up and in the same column
14 R[-2]C1 Cell Mixed Row reference is relative, column reference is absolute
15 R[2]C[3] Cell Relative Cell two rows down of current row and three rows to right of current column
16 R[-1] Range Relative Cells in the row above the current row
17 <defined name> Range or Cell Absolute Named cells are always absolute
18 Sheet1!<reference> Either Either Prefixing the sheet number and exclamation mark before any of the above references indicates the source worksheet.
19 =‘C:\[sample.xls]Sheet1’!$A$1 Cell Absolute

Refers to an external file - a worksheet in the file and an absolute position within that worksheet.
Note the usage of various delimiters.

20 =‘C:\[sample.xls]Sheet1’!octroi Cell Absolute Same as above. Refers to a defined name “octroi”
21 Sheet1:Sheet3!A1:B3 Cell Relative

Refers to all cells of A1 to B3 in all worksheets from Sheet 1 to sheet 3. “Sheet1” and “sheet3” are actual worksheet names.
This type of reference is called 3D reference because it cuts across worksheets.

22 “rupee price” Range Absolute This is the column name of the picture shown above. “Rupee Price” just a label typed by the user. This is a very useful feature. More on it later.
23 Getpivotdata function Range Absolute This is a special function which is used to refer to a range of rows/columns in a pivot table. Very useful. Will be covered later

To be continued...

As you can see, what started as a snippet has turned out to be a two-part article. Due to space constraints, I will continue it next week. Till then, try using Excel in a better way with whatever information you find useful from this article itself.

About the Author Dr Nitin Paranjape is the Chairman and MD of Maestros (Mediline). He is a consultant with many organisations, covering appropriate technology utilisation, business application of relevant technology, application architecture and audit as well as knowledge transfer. He has authored more than 650 articles on various technology-related subjects. He can be contacted at nitin@mediline.co.in
<Back to top>

© Copyright 2000: Indian Express Group (Mumbai, India). All rights reserved throughout the world. This entire site is compiled in
Mumbai by The Business Publications Division of the Indian Express Group of Newspapers.
Please contact our Webmaster for any queries on this site.