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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
