articles

Home / DeveloperSection / Articles / Absolute Cell Reference in Excel

Absolute Cell Reference in Excel

AVADHESH PATEL3724 25-Mar-2013

In Excel and other spreadsheets, an absolute cell reference identifies the location a cell or group of cells. Cell references are used in formulas, functions, charts, and other Excel commands. An absolute cell reference consists of the column letter and row number surrounded by dollar signs ($). 

An example of an absolute cell reference would be $C$4, $G$15, or $A$345. 

Note: An absolute cell reference is used when you want a cell reference to stay fixed on a specific cell. This means that as a formula or function is copied and pasted to other cells, the cell references in the formula or function do not change. 

By contrast, most cell references in a spreadsheet are relative cell references, which change when copied and pasted to other cells. 

$A$1 (absolute column and absolute row) -  $A$1 (the reference is absolute)

A$1 (relative column and absolute row) -   C$1 (the reference is mixed)

$A1 (absolute column and relative row) -   $A3 (the reference is mixed)

A1 (relative column and relative row) -   C3 (the reference is relative) 

For example see figure 1:

Figure 1:

Absolute Cell Reference in Excel

  In Figure 1, for copy data from cell A2 to D2, type formula =A2 and press enter. Data are copy successfully but when we try to copy this data to another cell it not copy, it set cell value as 0. See figure 2.

Figure 2:

Absolute Cell Reference in Excel

  Now, we copy data from cell A2 to D2 using absolute formula. Type formula =$A$2 into D2 cell (Figure 3) and press enter. Data are copy successfully and when we try to copy this data to another cell it copy successfully, See figure 4.

Figure 3:

Absolute Cell Reference in Excel

Figure 4:

Absolute Cell Reference in Excel


Updated 07-Sep-2019
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By