How to Provide proper cell reference in Excel?

image_pdf

Use absolute references to refer to cells that you don't want to change as the formula is copied. Relative reference is used to change the cell reference when copied into different cells. Mixed cell reference is a combination of relative and absolute cell reference. 

How to use relative cell reference in excel?

Relative reference is used when we need to use the same formula on different cells. For example if the formula A2+C2 is copied in row 2 then it changes to A3+C3.

Example:

relative

relative1

How to use absolute cell reference in excel?

Absolute reference is used when we don’t want the cell reference to change when filling cells. Here the absolute references don’t change when copied which is opposite to relative reference.e.g. $A$1 which means row number and column number are constant and does not change while copied to other cell.

In this example, we want to keep 10% discount(cell $B$7) as constant. but Price (Cell A2 to A5) will change. So we have used Relative reference.

absolute

absolute1

How to use mixed cell reference in excel?

Mixed cell reference is a combination of relative and absolute cell reference. This means that it is partly relative and partly absolute like $A1 (Column number is fixed) or A$1 (Row number is fixed).

Example: In the following example row number never changes.

mixed

mixed1

Example: In the following example the column no remains constant. As you copy the formula in different columns discount which in cell $B3 remains constant.

mixed2 mixed3

For any kind of queries on MS Excel, please feel free to Contact us. 

Stay tuned with us on www.facebook.com/amazingxcel for more amazing tips on MS Excel.

Explore more, It's Amazing…

How to convert data from text to columns in excel?

How to change the case of the text using UPPER, LOWER and PROPER function?

3,486 total views, 4 views today

2 thoughts on “How to Provide proper cell reference in Excel?

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> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>