Saturday, July 13, 2013

Cell Reference in Excel

                      Cell Reference in Excel

A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate.


 

Knowing the cell reference will make our task of writing complex formulas much simpler.Excel uses two types of cell references to create formulas.

Relative and Absolute Reference

 Relative references: are basic cell references that automatically adjust to new locations when the formula is pasted into different cells.


Absolute Reference: Sometimes, when you copy and paste a formula, you don't want one or more cell references to change. Absolute reference solves this problem. Absolute cell references in a formula always refer to the same cell or cell range in a formula. If a formula is copied to a different location, the absolute reference remains the same.

An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference or the row reference, or both. Examples of absolute referencing include:
 

To Create an Absolute Reference:
Select the cell where you wish to write the formula (in this example, H2)

Type the equal sign (=) to let Excel know a formula is being defined.
Click on the first cell to be included in the formula (F2, for example).
Enter a mathematical operator (use the multiplication symbol for this example).
Click on the second cell in the formula (C2, for example).
Add a $ sign before the C and a $ sign before the 2 to create an absolute reference 

Shortcut tips
When writing a formula, use the function key F4 to change the reference from relative to absolute and vice versa.
Happy Learning - Excel Mania