Reference in Excel

Reference in Excel

Excel offers three types of references which helps building formulas in a sheet. By default relative reference is set to Excel. In this article I am detailing each reference type with example:

Relative Reference:

By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell. When you copy a formula that contains a relative cell reference, that reference in the formula will change.

figure 1.0

If you drag the formula down in column “E” the reference for row auto shifts to B3 * C3, B4 * C4 and so on.

Mixed Absolute Reference:

A $ (Dollar) sign refers to absolute reference. In some cases you may want to build formula which requires some fixed values. If you refer formula below where I need to increment 100 sales increment in each sales it will look like:

figure 1.1

If you drag the formula down you will notice the first argument in the formula i.e. H1 remains constant but second argument C2 shifts with respect to row as C3, C4 etc.

Absolute Reference:

As mention above the absolute are constant referred by dollar ($) sign.

Leave a Reply

Your email address will not be published. Required fields are marked *