When you work in Excel, you may find yourself needing to apply the same formulas across a wide array of data. But retyping formulas is tedious, error-prone, and time-consuming. But by using relative references, you can easily copy formulas and have them automatically update to incorporate new data. Let’s learn how to use relative references in Microsoft Excel.
How to Use Relative References in Excel
Relative references are automatically applied in Excel, so you don’t have to perform any special steps to create them. A relative reference simply means that cell references are tied to the location of the cell. The opposite is an absolute reference, where a given cell is always referenced even if other elements of the formula are moving.
As an example, let’s look at two columns of data. There are five numbers in the range A1:A5, and five more in columns B1:B5. Imagine that you want to use column C to multiply each of the five pairs together. To illustrate, let’s place the first formula in cell C1:
=A1*B1
When you hit Enter on your keyboard, Excel will instantly calculate the solution and place it in cell C1. In this example, the solution is 50.
Remember, now you want to multiply the other four number pairs. One way to do it is to type in four additional formulas, one each in cells C2, C3, C4, and C5. But this is time-consuming, and equally unnecessary. That’s all thanks to the power of relative references.
Instead of typing formulas, simply click and drag the lower right corner of cell C1 downward. Your formula will copy across the other four number pairs. Again, because these cell references are relative, the formulas will automatically update to the new cell references:
=A2*B2
=A3*B3
=A4*B4
=A5*B5
Cells with absolute references have dollar signs in the formulas, and when you drag formulas, the absolute references will not change.
As you can see, it’s easy to use relative references to quickly copy formulas in Excel.