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.

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