03 Types of Excel Cell Reference: Relative, Absolute & Mixed

03 Types of Excel Cell Reference: Relative, Absolute & Mixed

By

An Excel Cell Reference is a cell address that identifies a cell’s location in the worksheet, based on its column letter and row number.

For example, A1 refers to the cell at the intersection of column A and row 1; A2 refers to the second cell in column A and row 2

Using Excel cell references is advantageous because if the values in the referenced cells are changed, the formula result automatically updates using the new values. Thus, using a cell reference rather than values in a cell gives more flexibility in a worksheet.

For example, the formula =A1+A2 adds the values in cells A1 and A2. If cell A1 contains the number 10 but is later changed to 20, the formula that references cell A1 updates automatically. 

The same principle applies to a cell that contains a formula and is referenced in another formula.     

A1 reference style  R1C1 reference style

Excel Cell reference is broadly used in formulas (a user-defined calculation), functions (pre-defined formulas in Excel such as SUM, VLOOKUP, HLOOKUP, COUNTIFS, SUMIFS, etc.), charts, and other Excel commands.

For example, =A1+B1 is just a formula, but =SUM(A1:B1) is a formula containing a function (i.e., SUM).

There are basically three types of cell references in Excel:

(01) Relative cell reference

(02) Absolute cell reference 

(03) Mixed cell reference


I. What is a Range Reference in Excel?

 

A range reference is a continuous block of two or more cells to make a range. It is represented by the address of the upper-left cell and the lower right cell separated with a colon.  

For example, the range A1:C3 having 9 cells from A1 to C3.

 

Range Reference in Excel

II. What are Reference Styles in Excel?

 

There are two Excel cell reference styles:

(01) A1 Reference Style in Excel

The A1 reference style is the default style in Excel. In this style, columns are defined by letters and rows by numbers, i.e. A1 defines a cell locates in column A and row 1.

A1 reference style in Excel

(02) R1C1 Reference Style in Excel

In R1C1 reference style, both rows and columns are identified by numbers, i.e. R1C1 designates a cell in row 1, column 1. Here “R” defines “Row” and “C” defines “Column”. At a glance, below screenshots illustrate both the A1 and R1C1 reference styles:

R1C1 reference style in Excel

 

Question: A very common problem found in excel that how to switch from default A1 reference style to R1C1 reference style or vice versa? 

To switch from the default A1 reference style to R1C1 reference style we go to the top left side corner of excel and click on File ➪ Options ➪ Formulas (at the left sidebar) ➪ check the R1C1 reference style box (for switching from A1 reference style to R1C1 reference style) or uncheck the box (for switching from R1C1 reference style to A1 style) ➪ then press “OK” button. 

A1 to R1C1 reference style or vice versa

III. Overview of Values, Formulas, and Excel Cell Reference 

 

This tutorial introduces values, formulas and the cell references required to write a formula. 

• Values

Values are any numerical data entered in a worksheet. Once values are entered in the worksheet, they can be used in formulas. 

• Formulas

Formulas are values, but unlike normal values, formulas contain information to perform a numerical calculation, such as adding, subtracting, multiplying or dividing. For example, a cell with the formula = 10+20 will display the result of the calculation: 30. 

All formulas must start with an equal sign (=). Then need to specify more information for calculation: (1) the values to calculate and (2) the function name(s) or the arithmetic operator(s).

• Functions are frequently used in advanced Excel. Functions are pre-made specialized formulas built into Excel that are used as shortcuts or to perform a wide range of calculations. For example, Excel provides dedicated functions that calculate sums and averages, standard deviations, yields, cosines and tangents, and much more.  

• Operators are the basic symbols used in mathematics: + (plus), – (minus), * (multiply) and / (divide). In Excel, use these in the same way as writing out a math problem. These ingredients are also known as arithmetic operators

Table 1: Excel’s arithmetic operators

OperatorNameExampleResult
+Addition5+510
-Subtraction5-50
*Multiplication5*525
/Division5/51
^Exponentiation5^5
(5*5*5*5*5)
3125
%Percent20%0.2

Note: The percentage (%) operator divides a number by 100

There’s another category of operators that are useful when creating formulas: logical operators. Logical operators help to build conditions into the formulas so the formulas produce different values depending on the value of the data they encounter. 

Table 2: Logical operators

OperatorNameExampleResult
=Equal to5=5TRUE
>Greater than5>10FALSE
<Less than5<10TRUE
>=Greater than or equal to5>=5

5>=10
TRUE

FALSE
<=Less than or equal to5<=5

5<=10
TRUE

TRUE
<>Not equal to5<>5

5<>10
FALSE

TRUE

 

• Excel Cell Reference 

An Excel cell reference is a cell address and most formulas are created with references to cells or ranges. These references enable the formulas to work dynamically with the data contained in those cells or ranges. 

For example, if any formula refers to cell B1 and the value in B1 is changed, the formula result changes to reflect the new value. 

If we don’t use the Excel cell references in the formulas, then we must edit the formulas manually every time in order to change the values used in the formulas.  


IV. Types of Excel Cell Reference: Relative, Absolute & Mixed

 

A key element of a formula is the Excel cell reference. There are three types of Excel cell references: relative cell reference, absolute cell reference, and mixed cell reference.

Each type of Excel cell reference in a formula plays an important role differently when the formula is copied and pasted to a new location.  

Table 3: Types of Excel Cell Reference

Reference Type

FormulaKeyboard Operation

ResultRemarks
Relative →VLOOKUP(I3,C2:F12,4,0)Default or Press F4 four times.C2:F12No dollar sign ($) is used to fix the column or row address.
Absolute →VLOOKUP(I3,$C$2:$F$12,4,0)Select ➪ Press F4 key once$C$2:$F$12The Range is getting fixed by using dollar signs ($) both before the column address and row address. As a result, when the copied formula dragged towards any direction row-wise or column-wise, the range does not change.
Mixed →VLOOKUP(I$3,$C$2:$F$12,4,0)Select ➪ Press F4 twiceI$3The Row address is getting fixed by the dollar sign ($). Thus, when the copied formula dragged towards column-wise or vertically, the row address doesn’t change, but the column address changes accordingly.
Mixed →VLOOKUP($I3,$C$2:$F$12,4,0)Select ➪ Press F4 three times$I3The Column is getting fixed by the dollar sign ($). Thus, when the copied formula dragged towards row-wise or horizontally, the column address doesn’t change, but the row address changes accordingly.

(01) Relative Cell Reference

A relative cell reference refers to cells in relation to the cell that contains the formula. When the formula is moved, it references new cells based on their location relative to the formula. Relative cell reference is the default type of Excel cell reference.  

A relative cell reference does not use any dollar sign ($) before the column address or the row address.  When a formula is copied or dragged to another cell or range, relative cell reference changes accordingly.

For example, A1

(02) Absolute Cell Reference

An absolute cell reference always refers to a specific cell or range of cells, regardless of where the formula is located in the worksheet, even when the formula is copied to any other cell in the worksheet.  The row and column references do not change after copying the formula because the reference is to an actual cell address.      

An absolute reference uses two dollar signs ($) in its address: one for the column letter and one for the row number.

For example, $A$1

(03) Mixed Cell Reference

A mixed cell reference is an Excel cell reference that uses an absolute column or row reference, but not both. Only one of the address parts is absolute.   

A mixed cell reference uses a dollar sign ($) either before the column address or before the row address. It is a combination of relative and absolute references (mixed reference type in excel).

For example, $A1 or A$1

• $A1 –  where the column address is absolute and the row address is relative. When copy and paste the cell to a new location, the column address A remains fixed, but the row number changes with the new location of the pasted cell e.g., $A2, $A3, $A4 so on. 

 

A$1where the column address is relative and the row address is absolute. Similarly, when copy and paste the cell to a new location, the row number 1 remains fixed, but the column address changes accordingly with the new location of the pasted cell e.g., B$1, C$1, D$1 so on.  


V. How to Change the Mode of Excel Cell References in Formula

 

We must remember that to accommodate any changes in the cell references, we may need to edit the formula and Excel must be in edit mode. 

The following are some of the ways to get into cell edit mode: 

  • Double-click the cell, which enables to edit the cell contents (vales, formulas, text) directly in the cell. 
  • Press F2, which enables to edit the cell contents directly in the cell. 
  • Select the cell that we want to edit, and then click on the Formula Bar and edit the cell contents. 

While we are editing cell contents, we can select multiple characters either by dragging the mouse cursor over them or by pressing Shift and navigation keys. 

We can quickly switch a cell reference from relative to absolute or mixed. Rather than retyping the formula in every new cell, select the cell reference or range and then repeatedly press the F4 key, Excel cycles through the different reference types – starting with the relative reference, followed by the absolute reference, then to a mixed reference with an absolute row address, followed by a mixed reference with an absolute column address.  

Excel cell reference switches in this order: C2:F12 ➪ $C$2:$F$12 ➪ C$2:F$12 ➪ $C2:$F12

Changing the Mode of Excel Cell References in a Formula

However, we can manually insert dollar signs ($) in the appropriate positions of the cell address. 


VI. How to Use Excel Cell References in Formulas

 

Most of the formulas work with the help of references to cells or ranges. These references enable the formulas to work dynamically with the data contained in those cells or ranges. 

In the given figure, using the COUNTIFS function in cell F3 to get the count value based on two criteria – (i) Country name and (ii) Priority scope.

The Syntax of the COUNTIFS function is:

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2]and so on)

The formula is written based on the above syntax

=COUNTIFS(C3:C20,E3,B3:B20,F2)

This formula uses relative cell references.

Using Excel Cell References in Formulas_1

Copy the formula in the entire range selected by using ‘Formulas and number formats’ under Paste Special wizard.

Using Excel Cell References in Formulas_2

 

As the formula uses relative cell reference, therefore when the formula is copied in the cells or ranges, the references adjust in a relative manner. As a result, formula criteria are not matched to the criteria range and produce incorrect results. So, we didn’t get any value.  

Using Excel Cell References in Formulas_3

Using Excel Cell References in Formulas_4

Figure-1: After analyzing the Formula

(01) Fixing the Cell References

A. Fixing the Criteria_range

After analyzing the formula (Figure-1), we can find out the actual reason behind this:

• When formula copied to the right-side from F3 to H3, we find that the range C3:C20 has been changed to E3:E20. That means column C has been changed to Column E, but the row number has not been changed.

• When formula copied downwards from F3 to F11, similarly we find that the range C3:C20 has been changed to C11:C28. In this case, Column C is fixed, but the row number has been changed.

(i) After considering the above two cases, we make the criteria_range1 absolute by pressing once the F4 key (Figure-2) and it looks like $C$3:$C$20. After applying an absolute reference, the cell range is fixed which means the column address and row number didn’t change even the formula is copied to a new cell. Absolute references include $ (a dollar sign) before each column and row address.  

Using Excel Cell References in Formulas_5

Figure-2: Range makes absolute by pressing single time F4 key

(ii) Similarly, we absolute the criteria_range2 (B3:B20) by pressing once the F4 key and it looks like $B$3:$B$20 (Figure-3).

Using Excel Cell References in Formulas_7

Figure-3: Similarly, the second range makes absolute by pressing single time F4 key

B. Fixing the Criteria:

(i) In the case of Criteria1, i.e., E3, the row number should be changed while the formula coping vertically or row-wise or downwards (likes E4, E5, E6, etc.) because the ‘Country’ names are plotted row-wise (which means arranged in a single column) in the summary table. So we didn’t fix the row address.

Similarly, column E is the address of Country names and if the formula is copied to the horizontally or column-wise or right-side, the column address has been changed to column F, G, H like this. So in this case, we only fix the column address.

Using Excel Cell References in Formulas_6

Figure 4: Use of mixed cell reference where the column address is absolute and the row address is relative

Finally, select the cell and fix it by pressing three times of  F4 key; it looks like $E3. This is a mixed cell reference where the column address is absolute and the row address is relative (Figure-4).

After analysis, we cay say that while the criteria are arranged in a single column, we only fix the column address but not the row number. 

(ii) Similarly, in the case of Criteria2, i.e., F2, the column address should be changed while the formula coping horizontally or right-side (likes G2, H2, etc.,). Because the ‘Priority’ scopes are plotted column-wise (which means it is arranged in a single row) in the summary table. So we didn’t fix the column address.

Similarly, row number has been changed while the formula is copied to the vertically or downwards (likes 3,4,5, etc.,). Row number 2 is the address of ‘Priority’ scope. As a result, Excel cell reference could not trace the criteria while row numbers changed.    

Using Excel Cell References in Formulas_8

Figure 5: Use of mixed cell reference where the row address is absolute and the column address is relative

So in this case, we should only fix the row address, but do not fix the column address by pressing twice the F4 key and it looks like F$2. This is a mixed cell reference where the row address is absolute and the column address is relative (Figure-5).

After analysis, we cay say that while the criteria are arranged in a single row, we only fix the row number but not the column address. 

 

(02) The formula is copied throughout the range

After applying Excel cell references, copy the cell F3 with formula select the range F3:H11 ➪ use Excel shortcut Alt+E+S+R or Alt+Ctrl+V+R to apply ‘Formula and number formats‘ under the Paste Special dialog box ➪ press Enter or click OK (Figure 6). 

Using Excel Cell References in Formulas_9

Figure 6: Formula is copied to the selected range using ‘Formulas and number formats’

Excel cell references in the formula perfectly address the criteria and make the dynamic formula (Figure 7).

Using Excel Cell References in Formulas_10

Figure 7: Excel cell references make the dynamic formula

(03) Summation of Count by the SUM() function or AutoSum

In this step, we use both SUM() and AutoSum function. Both are helpful and users can be habituated to apply any one of them.   

A. Using  the SUM() Function:

In cell I3, place the formula =SUM select the range wished to sum row-wise press Enter to copy the cell I3 with formula and paste downwards (Figure 8).  

Using Excel Cell References in Formulas_11

Figure 8: Using the SUM() function

B. Using  the AutoSum Function (Excel shortcut: Alt+=):

Similarly, we can apply the AutoSum (Alt+=) considering the single column (Figure 9) and the formula is copied to the end of the range.

Using Excel Cell References in Formulas_12

Figure 9: Using AutoSum (Alt+=) function in a single column and drag the formula end of the range.

Alternatively, select the blank range to want to apply the summation apply AutoSum by pressing Alt+=  press Enter.

Using Excel Cell References in Formulas_13

Figure 10: Using the AutoSum function in a range of cells

The Autosum function is applied in all the selected ranges at a glance (Figure 10 & Figure 11).  

Using Excel Cell References in Formulas_14

Figure 11: After applying the AutoSum function press Enter to get the result

 


How to Copy a Formula without Adjusting Relative References

A. Method 1: Using the Formula Bar

Using the Formula Bar_1

If we need to copy the formula without any changes in the relative references, then follow these steps: 

(01) Select the cell that contains the formula we want to copy.

(02) Click inside the formula bar to activate Edit mode. 

(03) Use the mouse or keyboard to select the entire formula. 

(04) Copy the selected formula by Ctrl+C.

(05) Press the ‘Esc‘ key to deactivate the formula bar. 

(06) Select the cell where we want a copy of the formula to appear. 

(07) Paste the formula by Ctrl+V.  

Using the Formula Bar_2

 

B. Method 2: Using the apostrophe (‘)

Using the apostrophe (')_1

 

(01) Activate the formula bar and type an apostrophe (‘) at the beginning of the formula (that is, to the left of the equal sign) to convert it to text. 

Using the apostrophe (')_2

 

(02) Press Enter to confirm the edit, copy the cell, and then paste it in the desired location. 

(03) After that, delete the apostrophe from both the source and destination cells to convert the text back to a formula. 

Using the apostrophe (')_3

 

C. Method 3: Using the ‘Find and Replace’ dialog box (Ctrl+H) 

If the formulas placed in a number of ranges, then it is difficult to copy them to the other location one by one – either by the using of formula bar; or by the use of the apostrophe. Then we use the third method – using the ‘Find and Replace’ dialog box.  

Using the 'Find and Replace' dialog box (Ctrl+H)_1 

 (01) Select the range with the formulas to want to copy.

(02) Press Ctrl+H to open the ‘Find and Replace’ dialog box.

(03)  Type equality sign (=) in front of the ‘Find what:’ box; and type any uncommon text, that does not relate to the topic, in front of the ‘Replace with:’ box.  For example, we use the text ‘functions‘.

(04) Then press ‘Replace All’.

Using the 'Find and Replace' dialog box (Ctrl+H)_2

 

(05) As a result, all the equality signs (=) have been replaced by the text ‘functions’. Now the formulas are converted to text. Copy the range and paste to the new locations.   

(06)  After the process is reversed. Press Ctrl+H to open the ‘Find and Replace’ dialog box. 

(07)  Type ‘functions‘ in front of the ‘Find what:’ box; and type equality sign (=) in front of the ‘Replace with:’ box.  

Using the 'Find and Replace' dialog box (Ctrl+H)_3

 

(08) Then press ‘Replace All’.

Using the 'Find and Replace' dialog box (Ctrl+H)_4


EXAMPLES


Sudipta Mondal
Follow AEF
Advance Excel Forum

03 Types of Excel Cell Reference: Relative, Absolute & Mixed

Editor's Rating:
5

Leave a Comment

You may also like