Excel Cell References
A tip – I usually tell my students that in every name there is always a hidden meaning, communicating some functions of the bearer.
Most words used in Excel performs same function as the literal meaning of the word(s), so should you see anything, anything at all on Excel, start relating it to something and you will find it very interesting that you already knows everything about Excel.
The Cell as used in Biology (from Latin word cella, meaning "small room") is the basic structural, functional, and biological unit of all known living organisms. Also an Excel cell, just as the name implies is the simplest unit of an excel spreadsheet that is used to define and create all activities to be performed in it.
An example of a single cell would be “A1”
when we refer to A1, it means content of or in Column A Row 1.
(A column is the left to right alphabetical order ranging from A through XFD depending on the version of the Excel)
More than one cell is called Range. An example of a range of cells reference would be D3:I3. When we use the reference D3:I3 we are telling Excel to refer to the contents of cells D3, E3, F3, G3, H3, I3
Relative and Absolute Cell References
There are two types of cell references in Excel, these are Relative and Absolute Cell References.
Relative cell references.
One of the important function of Excel is its automatic features – ability to apply same idea you have put together in making a particular formula or function to make more of it without you having to start afresh. It follows the same pattern and applies some basic rules or automatic completion of same pattern as you have initially used. for example to type number 1 to 40 in cell A1 to A40, you can just type number 1 in the first cell and number 2 in the next cell, then select the two cells at the same time and drag down all across or use the fill handle, the rest of the letters would be automatically appended to subsequent cells, using same standard you have built such as a single step interval. This can also be done for a multiple case for example to make a multiplication of 2 in Cell A1 to A10, on cell A1 input 2 and in cell A2 insert 4, perform same action as described above to drag down or use the fill handle. Excel will automatically make the next cell 6, 8, 10, 12 up till the last cell. So as much as possible you can avoid manual inputs and save your energy and time by using this special features Excel offers.
During the period of dragging across other cells, Excel also imitate the references of the applied formula, at times it would be required of you that you want the dragging down to change across the Row and remain Stagnant on one cell or pick a Row and Columns at an independent timing for example we already discussed that cell A1 means referencing to content in the first cell of the worksheet of an excel (Column A, Row 1), Cell XFD1048576 is the last cell on version 2013 of Excel Worksheets meaning Column XFD, Row 1048576 this are also referred to as cell Identity.
Relative Cell referencing just as the word being relative to the other it means to make a particular cell change in relative to the other column or row of the referenced cell(s). This is usually done by appending a dollar sign “$” either at the front of the Cell identity’s alphabet ( $A1) or at the end of the cell identity alphabet (A$1) each positioning is having a different function.
$A1 mean to make the reference stagnant across the row and A$1 means to make it stagnant across the column.
Applying this dollar sign in the direction means locking both directions i.e $A$1 this is referred to as Absolute cell referencing (Just the referred cell or ranges alone)
Try this: In cell A1 to A10 type number 1 to 10, In Cell B1 Type The Relative Row Absolute Column Reference: =$A1 And Press Enter. Copy And Then Select B2:B10 and Paste or drag down across through B10
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 E1 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 E1 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.
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 D1 and look in the Formula bar you should see =C$1
Hint:
Pressing of F4 after highlighting the cell identity is an easy way of appending the dollar sign, a single press append at the front, the next press append at the back while the third press append on both sides.
Click here to go to the lesson continuation....
Post a Comment