MULTI IN ONE: Absolute Cell References

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
An important difference in Excel spreadsheets is between absolute cell references and relative cell references. To see what this is all about, we'll create a simple spreadsheet. This will illustrate relative cell references, which is what we've been using so far.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So open up Excel and enter the same values as in the image below:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="A Cell Reference in Excel 2007" border="1" height="124" src="http://www.homeandlearn.co.uk/excel2007/images/2CellRefs.gif" width="346" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In cell B2, you need the following formula:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= A1 + A2</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
What do you think would happen if we copied an pasted the formula from B2 to cell B3? Let's see:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Click inside cell B2 to highlight it</li>
<li>Click on cell B2 with your right mouse button, and select Copy from the menu that appears</li>
<li>Now click into cell B3</li>
<li>Again, right click the cell to get the menu. But this time click Paste</li>
<li>Your spreadsheet should now look like ours:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The formula pasted to cell B3" height="100" src="http://www.homeandlearn.co.uk/excel2007/images/2CellRefs2.gif" width="217" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Cell now says 25! We were trying to work out what 20 + 25 was, and have the wrong answer. So why did Excel put 25 into cell B3 and not 45?</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
With cell B3 still highlighted, look at the formula bar at the top of Excel. You should see this formula:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= A2 + A3</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click into B2, however, and the formula is this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= A1 + A2</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The problem is due to cell referencing. When you clicked Copy from the menu, Excel didn't only copy the formula. It took at look at where the cells were in the formula, relative to the B2 cell, and copied this as well. From B2, the first cell reference (A1) is up one row, and left 1 column (the red arrow below):</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The red arrow is pointing to cell A1" border="1" height="124" src="http://www.homeandlearn.co.uk/excel2007/images/2CellRefsArrow.gif" width="346" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The second cell reference (A2) is one column to the left of cell B2:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The red arrow is pointing to cell A2" border="1" height="124" src="http://www.homeandlearn.co.uk/excel2007/images/2CellRefsArrow2.gif" width="346" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you clicked into cell B3 and selected Paste from the menu, Excel was not only pasting the formula, it was pasting this "up 1, left 1". Take a look at the two images below. We're now starting at cell B3. Have a look at where the two red arrows are pointing now.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The first cell reference:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The red arrow is pointing to cell A2" height="100" src="http://www.homeandlearn.co.uk/excel2007/images/2CellRefsArrow3.gif" width="217" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The second cell reference:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The red arrow is pointing to cell A3" height="100" src="http://www.homeandlearn.co.uk/excel2007/images/2CellRefsArrow4.gif" width="217" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So the first red arrow is pointing to cell A2, and the second red arrow is point to cell A3. This is what was copied. Excel then took the formula to mean this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= A2 + A3</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
But it should have been this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= A1 + A2</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If you want the correct answer in cell B3, you have to stop Excel from using this Relative Cell Referencing that it's currently doing. What you need is Absolute Cell Referencing.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Absolute cell referencing involves nothing more than placing a dollar symbol ( $ ) before each letter and number.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click inside of cell B2 on your spreadsheet, and change the formula to this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= <span style="color: red;">$</span>A<span style="color: red;">$</span>1 + <span style="color: red;">$</span>A<span style="color: red;">$</span>2</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now copy and paste it over to cell B3 again. You should have the correct answer, this time:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Absolute Cell References in Excel 2007" border="1" height="125" src="http://www.homeandlearn.co.uk/excel2007/images/2CellRefs3.gif" width="346" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br />Excel will use Absolute Formula in its own calculation, so it's worth getting used to them. But to recap:</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>If you need to copy and paste formulas, use Absolute cell references</li>
<li>Absolute referencing means typing a dollar symbol before the numbers and letters of each cell reference (You can mix absolute and relative cell references, though).</li>
</ul>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next part, we'll take a look at Named Ranges in Excel.</div>
</div>

Absolute Cell References

An important difference in Excel spreadsheets is between absolute cell references and relative cell references. To see what this is all ab...