EXCEL CELL REFERENCES
More often than not when we create a formula using one of Excels built-in functions we will be referring it to a cell or range of cells. An example of a single cell reference would be A1. An example of a range of cells reference would be A1:A10. For the first example we are referring to the content of cell A1 only, while in the second example we would be referring to the contents of cells A1, A2, A3, A4, A5, A6, A7, A8, A9, A10. Using the reference A1:A10 is just a simple method that Excel will automatically recognize. If we use the reference A1:C5 we are telling Excel to refer to the contents of cells A1, A2, A3, A4, A5, B1, B2, B3, B4, B5, C1, C2, C3 C4, C5. Relative and Absolute Cell References
In Excel there are two types of cell references, these are Relative and Absolute. We will first look at relative cell references. All the examples in the Cell References paragraph are what are known as relative cell references.
As you may remember in lesson 1 we discussed that each cell on an Excel Worksheet has it’s own unique address, e. g. A1 is the relative address of the first cell on all Worksheets, while IV65536 is the relative address of the last cell on all Worksheets. The reason why they are called relative is because they are relative to the cell they are used in. This will be best explained by a simple example.
You should now have the numbers 1 to 10 in both A1:A10 and B1:B10. This because we typed a relative cell reference in cell B1 (=A1), which is telling Excel to make cell B1 equal the value of the cell one column to the left on the same row i. e. A1. So when we copy the reference in B1 i. e. =A1 and paste it into cell B2 Excel is still going to reference the cell one column to the left on the same row i. e. A2. Copying the same cell (B1) and pasting it into cell B3 again tells Excel to reference the cell one column to the left on the same row i. e. A3.
Lets now copy the content of cell B1 and paste it into cell D1, this time we should get the result 0 (zero). If you click in cell D1 and look in the Formula bar you will see the relativecell reference: =C1. The reason we get the result of 0 (zero) is because the value of an empty cell is 0 (zero).
Ok, let’s now look at what an Absolute cell reference is. Basically an absolute cell reference is a reference to a cell that does not change no matter where it is copied. Again this will be easier to see by using an example.
This time you should have the number 1 in cells B1:B10 and if you select any cell in B1:B10 and look in the Formula bar, they will all have the absolute cell reference =$A$1. This is because by using the $ (dollar sign) we are telling Excel to always refer to the same cell no matter where we copy this reference to. The $ in front of the A ($A) is telling Excel to make the column reference absolute, while the $ in front of the 1 ($1) is telling Excel to make the row reference absolute. So the reference in its entirety is what is known as an Absolute cell reference.
If you grasped this concept we can move on to what is known as a either an:
Again the best way to see this is by using a small example.
Relative Row Absolute Column Reference
You will have the numbers 1 to 10 in cells B1:B10. This is because the row portion of the reference (1) is relative. Now copy cell B10 to cell D1 and you should get the result 1. This is because the column portion of the reference ($A) is absolute. If you click in any cell in the range B1:B10 or D1 and look in the Formula bar you will see that the row portion is always relative to the row the reference resides in, while the column reference is always absolute.
The same principle also applies to any reference that has an absolute row relative column reference. Again this can be best seen via the use of a small example.
Absolute Row Relative Column Reference
You will have the number 1 in cells B1:B10. This is because the row portion of the reference ($1) is absolute. Now copy cell B10 to cell D1 and you should get the result 0, this is because the column portion of the reference (A) is always relative to the column the reference resides in. If you click in any cell in the range B1:B10 and look in the Formula bar you will see that the row portion is always absolute. If you click in cell D1and look in the Formula bar you should see =C$1
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Go back to:
|Lesson 1 - Excel Fundamentals|
|Lesson 2 - Starting Excel and Excel Workbooks|
|Lesson 3 - Excel Toolbars and Task Panes|
|Lesson 4 - Excel Worksheets|
|Lesson 5 - Excel Cells and Navigating a Worksheet|
|Lesson 6 - Excel Cut/Copying and Pasting Data|
|Lesson 7 - Excel Copying with the Fill Handle|
|Lesson 8 - Excel Paste Special|
|Lesson 9 - Excel Insert Command|
|Lesson 10 - Excel's default options|
|Lesson 11 - Excel's Undo and Redo|
|Lesson 12 - Excel's Format Painter|
|Lesson 13 - Excel's Dates and Times|
|Lesson 14 - Excel's Custom Formats|
|Lesson 15 - Excel Formulas|
|Lesson 17 - Excel: Avoid Typing|
|Lesson 18 - Excel Formulae Arguments & Syntax|
|Lesson 19 - Excel Autosum Formula|
|Lesson 20 - Excel Auto Calculate|
|Lesson 21 - Excel's Insert Function|
|Lesson 22 - Excel's Useful Functions|
|Lesson 23 - Excel's Named Ranges|
|Lesson 24 - Excel's Constants and the Paste Name Dialog|
|Lesson 25 - Excel's Calculations|
|Lesson 26 - Excel Comments Cell|
|Lesson 27 - Excel Find and Replace|
|Lesson - 28 - Clear Excel Cell Contents|
|Lesson 29 - Effective Excel Printing 1|
|Lesson 30 - Effective Excel Printing 2|
|Lesson 31 - Sorting in Excel|
|Lesson 32 - Hide/Show Row/Columns in Excel|
|Lesson 33 - Auto-Formats in Excel|
|Lesson 34 - Creating a Basic Excel Spreadsheet|
|Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet|
|Lesson 36 - Excel Worksheet Protection|
|Lesson 37 - Excel IF Formula Nesting|
|Lesson 38 - Excel Function Now/Today Formulas|
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, slot sites not on gamstop or you can join the Forum and post your own questions.