How to use absolute references - example 1

One common situation where absolute references are useful is when you need to copy and paste a formula that must always reference a value in the same cell. If we try to copy and paste our formula without first converting this reference to an absolute reference, our copied formulas will not work properly.

Let's take a look.

Here we have a worksheet that tracks the number of hours worked and gross pay of a small group of people. Everyone is paid the same hourly wage, so we need to set up a formula that calculates gross pay based on hours worked and a fixed hourly wage.

In cell D9, let's enter the first formula we need:  =C9*C6

This works fine. John worked five hours at $11 dollars per hour, so his gross pay is $55.00.

Now let's copy and paste this formula down to the rest of the table.

We can see right away that we have some problems. When we check the copied formulas, we see that the reference to hours worked has correctly changed to point to the cell directly to the left—exactly what we want.

However, the reference to the hourly rate was also changed, which we definitely don't want. That's the source of the problems we see.

The solution is to convert the reference to hourly rate to an absolute reference, before we copy the formula. Let's do that, and try again.

We first undo our copied formulas. Then edit the original.

We need to make our reference to C6 an absolute reference. Let's use the F4 shortcut. With both dollar signs in place, we now have an absolute reference to the hourly rate, and we can try copying the formula again.

That looks a lot better. When we check the formulas, we see that each is correct, using the hours in the cell to the left, and the absolute reference to the hourly rate in cell C6.