Understanding Excel Cell Reference

We may have known that Excel spreadsheet is a grid of cells with position name formed by column and row label. Column label is in alphabet combination: A, B, .. Z, AA, AB,..AZ, BA… And row label are number: 1, 2, 3… Cell position name is also called cell reference.

Cell reference is used in calculating formulas, for example: in cell C1 we have =A1+B1, this mean value of C1 is calculated by value of A1 added to value of B1.

However, there’re a few types of cell reference, let’s go through them.

Relative Cell References

Let’s consider a formula in C1, =A1+B1. Obviously, we understand that value in C1 is: the value in A1 added to the value in B1. However, Excel reads the formula from the stand point of C1 like this: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.
So, if you copy the formula =A1+B1 from cell C1 and paste it into cell D1, the formula in D1 may sound different to you, which is =B1+C1. But to Excel, the formula is exactly the same: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.

Relative cell reference is the the “distance” of a cell to the standpoint cell: how many cell to the left (right, top, bottom), like above sample. And by default, Excel considers every cell reference used in a formula as a relative reference. That is, it takes no heed of actual column row coordinates.

This behavior is by design and works in situations in which you need the cell references to be adjusted when you copy the formula and paste it to other cells. For instance, the formula shown in cell C1 was copied and pasted down to the rows below. Note how Excel helps by automatically adjusting the cell references to match each row.

Absolute Cell Reference

What if you don’t want the cell reference to be auto totally adjusted in C2, let’s say you want it to be =A1+B2. This is where you need absolute reference. Then, in C1, the formula should be =A$1+B2, whereby $1 in A$1 means you tell Excel not to auto adjust the row coordinate when the formula is copied to another cell.

Excel make you flexible to tell it whether to make the column unchanged, or row unchanged, or both unchanged when copied by adding $ sign before the wanted column or row coordinate. We have below types of absolute references:

• (Fully) Absolute: $A$1 –> cell reference isn’t adjusted at all when the formula is copied.
• Column Absolute: $A1 –> only row is adjusted when the formula is copied.
• Row Absolute: A$1 –> only column adjusted when the formula is copied.

Cross-sheet reference

What if you have 2 spreadsheets on 1 workbook, then in one sheet you want to refer to a cell in another sheet. This is called cross-sheet reference, and Excel does support it. The reference is formed by: Sheet-Name!Cell.

For example I have 2 sheet: Sheet1 & Sheet2, Sheet1 has some cells with value. In Sheet2, at cell A1, I can refer to cell A1 of Sheet1 by the reference: Sheet1!A1. Let’s see the screenshots:

                       

External Cell Reference

There maybe cases when you have data in one workbook that you want to reference in a formula within another workbook. In such a situation, you can create a link between the workbooks using an external cell reference. An external cell reference is nothing more than a cell reference that resides in an outside workbook.

Creating an external cell reference is relatively easy: Open both workbooks (the workbook that you’re currently working in and the outside workbook). While entering a formula in the workbook you’re currently working in, click the cell that you want to reference in the outside workbook.

As you can see in below screenshot, you’ll immediately be able to tell that the cell reference is an external reference due to the full file path and sheet name prefixing the cell reference.

Looking at the reference, you can notice that it similar to Cross-sheet reference with the workbook file path added at the beginning. There’re 4 parts totally in this reference. Let’s break them down:

• File Path: This part of the cell reference points to the drive and directory in which the workbook is located.
• Workbook Name: This part of the cell reference points to the name of the workbook. This part is always enclosed in brackets ([ ]) and always includes the file extension (.xlsx, .xls, .xslm, and so on).
• Sheet Name: This part of the cell reference points to the name of the sheet in which the referenced cell resides.
• Cell Reference: This part of the cell reference points to the actual cell that is being referenced.

So, that’s all about Excel cell reference. Hope that it help you have a clear picture of how it works.