Each cell in a spreadsheet is represented by a label which is derived from the row column cross section at the cell. For example, the cell highlighted in the image has label B3. When you make a reference of B3 in any other cell or any formula, it is called cell reference.
Relative and Absolute References
When you copy a formula and paste it to some other cell you have to be sure of what type of reference you need. Let us understand with an example. In the above image we have written the formula =A2 in cell B2. Now, when I drag it over till B4 here is what the resulting reference will be like. This is relative reference. And, the result will have 10, 20 and 30 in B2, B3 and B4 from A2, A3 and A4 respectively. What if you need to refer to A2 each time? When you drag over for autofill you do not want the cell references to change. Append $ symbol to the label elements. So, if you want an absolute reference of A2 you should write $A$2 instead. Then an autofill on drag will not change the row column label values. This is called absolute reference. And, as you see the result in D2, D3 and D4 will have the value 10 as a reference of A2 throughout. Remember: Using $ symbol changes the reference to absolute. Any label without the $ symbol remains relative.
Mixed References
It is possible to have a mix of absolute and relative references. That means you can keep either of row or column references as absolute or relative. So, when you drag down or drag across only the index of the relative label will change. In the first section of the above image you would see that A is constant throughout i.e. the column reference is absolute and row reference is relative. In the second section you would see that 2 is constant throughout i.e. the row reference is absolute and the column reference is relative.
Summary of References
Circular References
Circular reference is prohibited in MS Excel. Meaning when you are on a cell, you cannot refer to itself. For example B4 = B4 + A2 is not allowed. Such references will be shown at the bottom of the sheet.
Inter-Sheet References
In Excel you can refer to any cell from any other cell, even if they belong to different sheets. For example, on Sheet2 I want to refer to A2 that is on Sheet1. My reference will now have the sheet name followed by an exclamation mark (!) prefixed to the reference. Here it is Sheet1!. Do not confuse the $ symbol. It is a mixed reference.
Conclusion
That is all about MS Excel references. I hope this helps you understand the basics and makes it easier for you to use formulas. If you have any doubts about any of these, do write to us. We will be more than glad to explain. The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.
