Work with relative, absolute, and mixed references Excel 2013

show more Working with relative, absolute, and mixed references provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Working with relative, absolute, and mixed references

In this worksheet called "Absolute", we're about to put a formula in column F. We're going to create New Salary simply by giving everybody a $2000 salary increase. Now, a formula like this involves a standard Excel technique. If I type = and click cell E3+2000, and after typing this entry, you would want to copy this down the column. If you work with Excel formulas a bit, you know Excel will do exactly the right thing here. In other words, if we complete the formula and drag from the lower right-hand corner--let's just check it out on a few cells-- Excel is not using E3 over and over and over again.

It's surely adjusting to using E4 and as I double-click on these, E5 and E6 and so on, for as far down as we might copy this. That's called a Relative Reference. It's the most common kind of reference in Excel--a cell reference that is relative. If we copy the formula into different rows, it adjusts the Row Reference of a formula. Let's take a different situation here where we want everybody's salary to go up by a certain percent, maybe this 2.1%. So a formula here--and there are certainly a few different ways to write this-- might be E3, times--using the asterisk--E3*, this percent.

Now, if we were to press Enter now, we would simply have the amount of the increase, so we need to add on to that initial Salary. The New Salary for this person is going to be 55,696. It's simply calculating the amount of the increase and adding that onto the existing salary. We would want to copy this down the column. Again, testing it out on a few cells makes sense. As we do this, we almost immediately recognize that only the very first person is getting the increase. What happens in the next entry here? The E4 references are correct, but the reference to H2 has now slipped down into H3.

You can probably guess what's happening down below here. Down here, it's referring to H5 and down here, as I double-click, it's referring to H6. In all of these examples, we always want the reference to be to cell H2, the percentage of increase for everybody. We don't want H2 to change. There are two ways to make a change here. Neither of them is really intuitive. We need to put dollar signs in front of the 2 and in front of the H. Why dollar sign? That's the rule in Excel. We need some symbol here to indicate, we do not want this to change.

To make it a little bit faster, you can click after the H2 or in front of it or between the two, it doesn't make any difference. Press the function key F4. Now, had we been doing this from the beginning, right after putting in the H2, we would have pressed the function key F4. Now, the dollar signs have nothing to do with salary. That's just a coincidence. The dollar signs mean, if we copy this formula, the reference to H2 stays the same, exactly. That's what we wanted to have happen.

As we complete the entry here and copy it down again--just a few cells--check it out, all are getting their increases. What does this formula say here, for example? It refers to H2, and so do the other ones. You quickly get used to this idea. When you see dollar signs in formula, you're not necessarily thinking salaries, although in this case it is. It's an indication that the cell, if copied, the reference to this cell will not change. Now, there are situations where you've got a dollar sign in front of the row or just the column--those are usually a bit more sophisticated we won't go into those-- those are sometimes called Mixed References.

In the example here, and we might have thousands of salaries, we want to make sure that every salary increase here is based on the Absolute Reference to cell H2. On the bottom one here, we can just double-click the bottom edge and copy that down to the end of the column. That might be thousands of rows deep here; all based on that single cell H2 and the cells over in column E, using what's called an Absolute Reference. It's an absolutely indispensable feature in Excel; almost everybody needs it at one time or another.

Working with relative, absolute, and mixed references
Video duration: 4m 7s 6h 32m Appropriate for all


Working with relative, absolute, and mixed references provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...