Free Online Tutorials on Spreadsheets

Relative, absolute, and mixed references in Excel

Relative reference

By default, a cell reference is a relative reference in Excel. The term ‘relative’ is used because the reference is relative to the location of the cell.

For example, cell C2 below contains two references i.e. in the same row (2), A2 is two column left and B2 is one column left to cell C2.

When you copy a formula that has the relative reference and paste in the same column. The reference in the formula will change with respect to the location, such as:

Absolute reference

When you maintain the original cell reference in a formula by making it absolute with the dollar sign ($), it becomes the Absolute reference.

For example, when we attach the dollar sign ($) like in C2 cell below (by selecting the formula and pressing F4), it will stop the row and column number changing when copied to other cells.

Cell references in C3 remain the same as in the C2 because of the absolute reference.

With the help of the absolute reference, the reference in the formula does not change when copied to other cells.

Mixed reference

Sometimes, you may need both relative and absolute references in your formula. For this, you will fix only one row or column. Like in the example below, only cell A2 is taken as absolute and B3 has the relative reference.

Now when you copy and paste the formula to other cells, the absolute reference in the formula will not change only.




Copyright © 2016 - 2019 Explainry.com | All Rights Reserved