How do you enter a relative formula in Excel?

Answer 1

A relative formula uses relative addresses. These will change when the formula is copied. Say you had columns of numbers, in columns A to G, and from row 2 to 25 in each column. Say you wanted to add up the total for each column. You could do a separate formula for each one, but using a relative formula it can be done with one formula which would then be copied. In cell A26 you could put:

=SUM(A2:A25)

That would total up the column A cells. When you copy it to B26 the references would change and the formula would now be:

=SUM(B2:B25)

You could copy it on across through all the cells to G26 and you would have totals for each column. The formula is relative. What it does is think of the cells mentioned, relative to where the formula has been entered. So the first formula doesn’t really think of A2 to A25, but the 24 cells above A26. So when it is copied to B26, the 24 cells above it are B2 to B25. This change continues as it is copied to the other cells, so that each formula adds its own set of cells.

If the formula was absolute it would specifically think of A2 to A25 and when copied across, it would still just add those cells. The absolute version would be like:

=SUM($A$2:$A$25)

The dollar signs have the effect of locking in the cell references so that they don’t change when the formula is copied. Absolute references also have their uses in other situations.