Cells referencing or addressing

What is cells referencing?

In Excel, a cell reference identifies the location a cell or group of cells in the worksheet. Sometimes referred to as a cell address, a cell reference consists of the column letter and row number that intersect at the cell’s location. For an example when listing a cell reference, the column letter is always listed first – such as A1 or D3. Cell references are used in formulas, functions, charts, and other Excel commands. While references usually refer to individual cells – such as A1, they can also refer to a group or range of cells. Ranges are identified by the cell references of the cells in the upper left and lower right corners of the range. The two cell references used for a range are separated by a colon ( : ) which tells Excel to include all the cells between these start and end points. An example of a range of adjacent cells would be B5:D10. Cells referencing is also know as addressing. By default, a cell reference is relative. There are three types of cell referencing or addressing

  1. Relative Referencing: A Relative Reference is one that when copied from one position to another will adjust the formula cell address to suit the position it is in.
  2. Absolute Referencing: Situations arise in which the cell reference must remain the same when copied or when using AutoFill.  Dollar signs are used to hold a column and/or row reference constant.
  3. Mixed Referencing: In Microsoft Excel there is also a reference called a mixed reference. Essentially what this means is that only either the Column or the Row has the dollar symbol, for example $C3. What this is telling us is that in the formula you must absolutely refer to column C but the value in the row is relative to the position of the formula.

 

Cells Reference Styles

Excel understands two different styles of referencing for cells and ranges. These are described in the table below :

A1-Style References

The A1-style is the most common form of Excel referencing, and is the default style.This style of referencing is made up of a letter and a number, which represent the column reference and the row number, respectively.

R1C1-Style ReferencesThe R1C1-style of referencing is made up the letter R followed by a row number and the letter C followed by a column number.The row or column number of the R1C1 style reference can be encased in square brackets, if you want the row or column number to be measured as a shifted number of rows or columns from the current cell location.If the row or column number is omitted, this tells Excel to look in the current row or column

There are a number of ways that you can enter Relative and Absolute values into a formula. One technique is that you can simply type the $ symbols next to the Row or the Column. However there is an alternative. Once you have typed in the Cell address like C4 you can move back into the cell address and then use the F4 key to toggle the cell reference from Relative to Absolute to a Mixed References.

 

1. Relative Referencing or addressing

In Excel and other spreadsheets, a relative cell reference identifies the location of a cell or group of cells. Cell references are used in a variety of features such as formulas, functions, and charts .

Copying Formulas and Relative Cell References

By default, a spreadsheet cell reference is relative. What this means is that as a formula or function is copied and pasted to other cells, the cell references in the formula or function change to reflect the function’s new location. In contrast, an absolute cell reference does not change when it a formula is copied and pasted to other cells. A relative cell reference consists of the column letter and row number that intersect at the cell’s location. An example of a relative cell reference would be C4, G15, or Z2345.

Data Layout and Copying Formulas

Another requirement for relative cell references to work when copying formulas is that the data used in the formula must be laid out in a regular pattern. Each row has the same field of data in the same location. If the data is not arranged in this fashion, copying the formulas would result in errors in the worksheet.

Copying Formulas Containing Relative Cell References

When copying formulas with the fill handle, if the formula contains relative cell references, the formulas will update the cell references with each iteration to reflect the new location of the formula. For example, in the image above, the Net Salary formula in cell D3 is copied using the fill handle to cells D4, D5, and D6.
The original formula in D3 is : =B3 – C3
Because the formula contains relative cell references, it changes each time it is copied to a new cell with the fill handle to reflect the new row. The resulting formulas are:
Cell    Formula
D4    =B4 – C4
D5    =B5 – C5

 

2. Absolute Referencing or addressing

In Excel and Google Spreadsheets, an absolute cell reference, like other cell references, identifies the location a cell or group of cells and are used in such things as formulas, functions, and charts. An absolute cell reference consists of the column letter and row number used in a regular cell reference but both letter and number are preceded by dollar signs ( $ ). Examples of absolute cell references would be $C$4, $G$15, or $A$345.

Using Absolute Cell References in Formulas

One of the main uses for absolute cell references is in a formula when you want a cell reference stay fixed on a specific cell. As a result, if the formula is copied and pasted to other cells, the absolute cell references in the formula or function do not change. By contrast, most cell references in a spreadsheet are relative cell references, which change when copied and pasted to other cells. In the image above, a tax rate is located in a single cell – D1 – above a table of data. The formula in the Taxes column of the table multiplies the cost of an item by this tax rate. If only relative cell references were used in the original formula in cell D3 and this formula then copied to cells D4 and D5, the results would be:
cell D3: = C3 * D1 – returns a result of $0.08 for taxes
cell D4: = C4 * D2 – returns the #VALUE! error value because cell D2 contains the text label Taxes
cell D5: = C5 * D3 – returns a result of $0.07 for taxes because it is multiplying the cost in C5 times the tax amount of $0.08 in cell D3 rather than the tax rate of 6% in cell D1.
As a result of using only relative references in the original formula, copying the formula resulted in errors in two of the three calculations.

Changing Regular Cell References to Absolute Cell References

By changing the tax rate reference from relative to absolute, the formula can be copied without producing errors. An easy way to add the dollar signs to a cell reference while creating a formula:
Click once on a cell to enter that cell reference into a formula’
Press the F4 key on the keyboard – dollar signs will be added before both letter and number in the reference.

To make a reference absolute in an existing formula:

Click once on the cell containing the formula to make it the active cell
Press the F2 key on the keyboard to put Excel into edit mode
In either the cell containing the formula or in the formula bar above the worksheet, click once on the cell reference being changed
Press the F4 key on the keyboard – dollar signs will be added before both letter and number in the reference.
Press the Enter key on the keyboard to leave edit mode

Copying Formulas Containing Absolute Cell References

An important point about using absolute versus relative cell references in formulas is that the original formula is never affected by the use or absence of absolute references. It is only when the formula is copied that the presence or absence of the absolute references come into play. In the taxes example above, by using an absolute cell reference in the taxes formula in cell D3 and then copying it to cells D4 and D5, the formula and results become:
cell D3: = C3 * $D$1 – returns the same result of $0.08 for taxes
cell D4: = C4 * $D$1 – returns a result of $0.15 for taxes
cell D5: = C5 * $D$1 – returns a result of $0.06 for taxes
As a result of using an absolute reference for the tax rate, the formula can be copied without error saving a great deal of time and effort.

 

3. Mixed Referencing or addressing

In Excel cell references are used in formulas, functions, charts, and other Excel commands. Cell references consist of a column letter and the row number that intersect at the cell’s location in a spreadsheet such as D2 or AZ234. By default, a spreadsheet cell reference is relative. What this means is that as a formula or function is copied and pasted to other cells, the cell references in the formula or function change to reflect the function’s new location. Alternatively, an absolute cell reference, such as $F$34 or $G$67, does not change when a formula or function is copied to other cells.

A mixed cell reference then, is a combination of relative and absolute cell references. As with absolute cell references, the dollar sign ( $ ) is used in mixed cell references to indicate that a column letter or row number is to remain fixed when a copied from one cell to another. Examples of a mixed cell reference would be $E4 or F$6. For $E4, the column letter is fixed while the row number is allowed to change when copied to other cells. For F$6, the row number is fixed while the column letter changes.

Note: when listing any cell reference – relative, absolute, or mixed, the column letter is always listed first and row number is always listed second.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment