How to Use Cell References in Excel Formula (All Possible Ways)

Method 1 – Use of Relative Cell Reference in Formila in Excel 

Steps:

• In cell F5, write the following formula to calculate the Price as a product of the Unit Price and Quantity

• Hit Enter and the result is 21 (=10.5*2).
• Pick the fill handler for cell F5 at the bottom right corner of the cell, hold and drag it down to the cells F6:F9 of column F.

• The above steps copied the formula of cell F5 and then pasted it to F6:F9 with relative references.

Click any of the cells from F6:F9. We clicked on cell F8 which shows the formula as a product of cells D8 and E8, relative to the row number of cell F8.

Method 2 – Absolute Cell Reference in Excel Formula

Steps:

• Calculate the Price After Tax, write the following formula in cell G5–

To make cell C11 an absolute reference we put the $ sign before the column value C and also before the row value 11, which is $C$11. Here F5 is a relative reference that will change relative to the row number downwards.

• Hit Enter and the result is 22.58 (=21+21*.075).
• Copy and paste the formula to cells G6:G9 locate the fill handler, drag it down to the cells and finally, release the cursor.

• The above steps copied the formula of cell F5 and then pasted it to F6:F9 with absolute references.

We see each of the values of the Price After Tax column used the absolute cell reference $C$11.  Let’s click on cell G8. It shows the formula which contains the relative cell reference F8 according to the row number of G8 but the absolute cell reference $C$11 remains constant.

Method 3 – Write Formula Using Mixed Cell Reference in Excel

Steps:

• In cell G6, write the following formula-

We put the tax rate cell reference as C$13 which is mixed. We put the $ sign before the column value 13 to make it absolute column-wise and left the row value C without the $ sign as it’ll be relative row-wise.

There is another way to look at the formula. $F6 is also a mixed cell reference. It is relative through the column cells and absolute through the row cells while calculating the Price After Tax values.

• Hit Enter and the output is 58 (=21+21*0.075).
• Locate the fill handler and drag it to the right to calculate values for different tax rates of different time durations.

• The output shows three Price After Tax values for three different tax values.

Double click on cell I6. In the formula, we can see the tax rate cell reference is E$13 (C$13 in cell G6 formula) which is relative row-wise. The total price cell reference $F6 didn’t change as it was in the formula for G6 which is absolute row-wise.

• Locate the fill handler at the right bottom corner of cell G6 and drag it down to G6:G10.

• Double click cell G9 to explain what happens here. In the formula, we can see the tax rate cell reference is C$13 (also C$13 in cell G6 formula) which is absolute column-wise. The total price cell reference $F6 did change to $F9 which is relative column-wise.

• Get all the values for Price After Tax following the previous steps.

Notes

We can also use a cell reference from one worksheet in a formula that is in another worksheet, i.e., across multiple worksheets. We used the mixed tax rate cell reference from the mixed worksheet. Although the formula is in the worksheet, which is named a ‘different worksheet’ for this, we need to put the worksheet name in a quote and an exclamation (!) sign afterward. Put the cell reference. See the example below:

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

• How to Make Different Types of Cell Reference in Excel
• Cell Reference in Excel VBA
• Excel VBA: R1C1 Formula with Variable

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel