How To Use Absolute Relative and Mixed Cell References in Excel

Cell Reference in Excel can be considered as an address of the cell. To access the content of any cell during programming or writing formulas or creating charts, we need to know its address. Cell Reference in Excel is denoted by two letters, where first letter denotes the Column and second letter denotes the Row. For instance, A1 stands for first column and first row, A2 stands for first column and second row and so on.

Now what makes a cell reference absolute, relative or mixed is usage of the $ symbol. Let’s understand each one of these by taking example.

Relative Cell Reference

By Relative Cell Reference, it means that the cell address used in the formula is not fixed and it will change when the formula is copied to other cells. That is, either row or column or both may increment by 1. A1, A2 etc. are examples of relative cell references.

In the figure given below, first column contains numbers from 1 to 10. We wrote a formula “=A1” in cell B1. This formula copies the value from cell A1 to cell B1. Now when we copy this formula in cells B2 to B10, row number gets incremented automatically and values from cells A2 to A10 gets copied to B2 to B10.

If you check the formula in cell B2, it has changed to “=A2″. Similarly in cell B3, formula has changed to “=A3″. Thus, row number gets incremented by 1 relatively.

If we now copy this formula to cell C1, formula will get change to “=B1″, column gets incremented by 1 relatively.

Absolute Cell Reference

By Absolute Cell Reference, it means that the cell address used in the formula is fixed and it will not change when the formula is copied to other cells. That is, both row and column are fixed. To make a cell reference absolute, $ is used before row and column letters e.g. $A$1.

In the figure given below, first column contains numbers from 1 to 10. We wrote a formula “=$A$1” in B1. This formula copies the value from cell A1 to cell B1. Now when we copy this formula in cells B2 to B10, row or column does not get incremented and values from cells A1 gets copied to B2 to B10.

If you check the formula in cell B2 to B10, you will find out that it has not changed and neither row or column is incremented.

Absolute Cell Reference in ExcelThere is one exception to using $ for making a cell reference absolute – when a new row or column in inserted. To overcome this, we need to make use of INDIRECT function. “=INDIRECT(A1)” will always refer to first column and first row regardless of any changes made to the excel worksheet.

Mixed Cell Reference

By Mixed Cell Reference, it means that either row or column is absolute and will not change when formula is copied to other cells. To make a cell reference mixed, $ is used either before row or before column letter.

A$1 indicates that row is fixed (absolute) but column will vary

$A1 indicates that column is fixed (absolute) but row will vary

We hope that you find the information presented here useful. Please feel free to let us know if you have any queries in the comments section below.

 

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">