All about Excel Cell Reference

As already discussed in the article “How to Create a Formula in Excel“. If a cell contains a formula copied to another cell, it will generate a cell containing a formula as well. By default, the cell address used by the formula will change to adjust the location of the new cell.

Excel provides three options to set a reference to the cell if the cell contains a formula copied, i.e., relative cell reference, absolute cell reference and mixed cell reference

Relative Cell Reference

What is Relative Cell Reference?

When a cell address contains a formula copied and cell address changing according to the new cell address location, that is a relative cell reference. Relative Reference excels default behavior for all formulas.

How to Use Relative Reference in Excel

There is no specific way how to use relative reference in excel because the relative cell reference is the default cell reference used in excel. Each cell address used in creating formulas by default uses a relative cell reference.

Relative Cell Reference Example

For example, there are data such as the image below. Mr. Fulan is a very kind person :). He bought two iPhoneXs for both his parents, 1 Galaxy S9 + for his wife, 3 Moto G6 for his sisters and 4 Honor 7X for his friends. How much money should be paid to buy all these gifts?

To find out how much money spent by Mr. Fulan. First, calculate the subtotal for each cell phone by multiplying the price and the quantity of each cell phone.

Here are the steps for calculating subtotals.

1. Place the cursor in cell E2
2. Type the equal sign “=”
3. Point the cursor to cell C2
4. Type the asterisk sign “*”
5. Point the cursor to cell D2
6. Press the ENTER key

The result is the price to be paid for two iPhoneX 256GB. What about the other items subtotal? Should the formula be typed one by one for each cell phone? Of course not. If there are many items, then it will be a very time-wasting.

The solution is simple, do a copy (CTRL+C) in cell E2 that already contains a formula, then paste (CTRL+V) in the range E3:E5. The results are as shown below.

The subtotal of each item differs from the subtotal of the first item, why this happens? We did copy the existing data in cell E2, why data in range E3:E5 is not the same as the data in cell E2?

If a cell containing a formula copied, the formula is copied, not the value. Excel will copy the formula =C2*D2 in cell E2. If placed 1 row below, then the formula will change to =C3*D3 (shifted down 1 row), if moved two rows below the formula change to =C4*D4 (moved down two rows) and so on.

That’s called a relative cell reference. Thanks to Excel who has made the relative cell reference. With this, there is no need to create a formula one by one for each item.

Absolute Cell Reference

What is an Absolute Cell Reference?

The absolute cell reference is the opposite of relative cell reference. If the formula using a relative cell reference is copied and placed in a new location, then the cell address will change relatively according to the new position.

The cell address of formula with absolute reference will not change when copied and put in a new location.

How to Make an Absolute Reference in Excel

The absolute cell reference sign is a $ in front of the column letter and row number. To use an absolute cell reference, you can type the $ sign directly or by pressing the F4 key once.

Absolute Cell Reference Example

For example, there are data such as the image below. The data is like the previous case with the addition of discount data. How much the discount for each item?

The formula to calculate the discounts is the multiplication between subtotal1 data in column E with the existing discount data in cell C2.

1. Place the cursor in cell F5
2. Type the equal sign “=”
3. Point the cursor to cell E5
4. Type the asterisk “*”
5. Point the cursor to cell C2
6. Press the ENTER key

The result is as shown below

How about discounts on other items? Just do a copy (CTRL+C) in cell F5 then paste (CTRL+V) in the range F8:F6. The result is as shown below.

Why the results are not as expected?

Check the formula in cell F5. The subtotal1 data point to cell E5, correct and discount data point to cell C2, correct.

 

Check the formula in cell F6. The subtotal1 data point to cell E6, correct and discount data point to cell C3, wrong.

 

Check the formula in cell F7. The subtotal1 data point to cell E7, correct and discount data point to cell C4, wrong.

 

Check the formula in cell F8. The subtotal1 data point to cell E8, correct and discount data point to cell C5, wrong.

The wrong formula is a formula in the discount data section, when the formula is copied down the cell address point to discount data shifted down too. It should remain pointing to cell C2.

The solution is to use the absolute cell reference, when the formula copied and placed in a new place, cell address remains unchanged and point to the same address.

1. Place the cursor in cell F5
2. Do an edit by pressing the F2 key
3. Block cell address C2
4. Press the F4 key once; the cell address C2 turns to $C$2; there is a dollar sign in front of column letter and row number. It’s mean cell address C2 uses an absolute cell reference
5. Press the ENTER key
6. Do a copy in cell F5 (CTRL+C) then paste (CTRL+V) in the range F6:F8

The result is as shown below, cell address C2 does not change even if copied down.

Absolute vs. Relative Cell Reference

What is the difference between absolute cell reference and relative cell reference? As already explained above, the cell address with a relative cell reference will change when copied and placed to a new place, while the cell address with an absolute cell reference remains unchanged when copied and put to a new location. Which one will be used depending on condition.

Mixed Cell Reference

What is a Mixed Cell Reference?

The mixed cell reference is a combination of the relative and absolute cell reference. When using a relative cell reference, then everything either column or row is relative as well when using absolute cell reference everything becomes absolute.

Mixed cell reference can combine both. Relative in the column and absolute in a row or otherwise absolute in column and relative in a row.

How to Create a Mixed Reference in Excel

To use a mixed cell reference could use a dollar sign “$” in front of the column letters or row numbers. The placement of the dollar sign specifies which column/row will be absolute.

The cell address $A1 shows column A using absolute cell reference while row 1 uses relative cell reference. Pressing F4 key three times will generate this mixed cell reference.

The cell address A$1 shows the opposite, column using relative cell reference and row uses absolute cell reference. Pressing F4 key twice will generate this mixed cell reference.

Mixed Cell Reference Example

Suppose there is data like the picture below. It’s like the previous example with the addition of sales tax data. How much is the sales tax for each item? Is it possible to calculate sales tax just by copying the formula to calculate discounts?

1. Select range F5:F8 then do a copy (CTRL+C)
2. Place the cursor in cell G5
3. Do a paste (CTRL+V)

The results are as shown below.

The resulting number is not as desired, why can it be like this?

 

Check the formula in cell G5. Subtotal1 cell reference should remain pointing to cell E5 but shifted right 1 column point to cell F5. While sales tax cell reference which should move right 1 column point to cell D2, remains point to cell C2.

For subtotal1 cell reference, when copied to the right, from column F (Discount) to column G (Sales Tax) column position unchanged, remain in column D. Conversely, if copied down from row 5 to row 8, row position must change to adjust to the new location.

So, the cell reference for subtotal1 is written $E5, column uses absolute reference, indicated by the $ sign and row using relative reference.

For discount cell reference, when copied to the right, the position of the column should change. Conversely, if copied down the row position remain unchanged.

So, the cell reference for the discount is written C$2, column using a relative reference and row uses the absolute reference, indicated by the $ sign.

Edit the formula in cell F5. Block cell E5 then press F4 key three times; block cell C2 then press the F4 once. The formula becomes =$E5*C$2, then press the ENTER key.

To calculate the discount on other items. Do a copy (CTRL+C) in cell F5, and then do a paste (CTRL+V) in the range F6:F8. To calculate sales tax, do a paste again (CTRL+V) in range G5:G8.

The results are as shown below.

Check the formula in cell F5. Subtotal1 cell reference point to cell E5 and discount cell reference point to cell C2.

 

Check the formula in cell G5. Subtotal1 cell reference remains to point cell E5 and sales tax cell reference shifted right 1 column point to cell C3.

By understanding the differences of relative reference, absolute reference, and mixed reference, formula writing can be done only once for various purposes. For the above case, one formula can be used to calculate discount and sales tax at once.

3D Cell Reference

What is a 3D Reference in Excel?

3D reference is a cell reference used in multiple worksheets in a workbook that has the same data structure.

How to Do a 3D Reference in Excel

For example, there are data such as the image below; there are three worksheets. Each worksheet has the same data structure. Three columns with eight cellphone and sales data. Two sheets contain sales data for an area, and one sheet has the total sales for all regions. How does the formula to calculate the total sales of all areas?

Solution Using Addition formula

The following steps to create the formula by using the “+” sign.

1. Place cursor in cell C2, worksheet “Total”
2. Type equal sign “=”
3. Click worksheet “West”, then click cell C2
4. Type plus sign “+”
5. Click worksheet “East”, then click cell C2
6. Press the ENTER key

Below is the result of the formula

=West!C2+East!C2

To calculate total sales of another cell phone. Do a copy (CTRL+C) in cell C2, then paste (CTRL+V) in range C3:C9. The result as shown below.

This way is easy because workbook only has a few worksheets. If there are a lot of worksheets, then it will be time-consuming and increase the possibilities of error. Also, if there is a new worksheet (new area), the formula should be edited to include the sales of the new area.

The above problems can be solved using 3D reference. The SUM function will be used to calculate the total sales.

Solution Using 3D Reference

Here are the steps to create a formula using 3D reference.

1. Place the cursor in cell C2, worksheet “Total”
2. Type =SUM(
3. Click the “West” worksheet
4. Press the SHIFT key and hold
5. Click on the “East” worksheet, then release the SHIFT key
6. Click cell C2
7. Type ) “close brackets”
8. Press the ENTER button

Below is the result of the formula

=SUM(West:East!C2)

To calculate total sales of another cell phone. Do a copy (CTRL+C) in cell C2, then paste (CTRL+V) in range B3:B7. The result is as shown below.

There is no difference in results compared to the previous way. The difference shows when a new worksheet is added.

The addition of a new worksheet will automatically change the total sales without changing the formula. As a note, the new worksheet should be placed between the “West” and “East” worksheets.

External Cell Reference

What is External Cell Reference?

The external cell reference is the cell reference for the data point to another excel file (workbook).

How to Do an External Cell Reference

Suppose there are two sales areas (west, east), each of them is stored in an Excel file, and then there is another Excel file that contains the total sales from all regions.

“Please download the exercise file here”

Here are the steps to create an external reference.

1. Open all files (west.xlsx, east.xlsx and total.xlsx)
2. Select workbook total.xlsx, place the cursor in cell C2
3. Type =
4. Switch windows (CTRL+F6), select west.xlsx, click cell C2
5. Type +
6. Switch windows (CTRL+F6), select east.xlsx, click cell C2
7. Press the ENTER key

The result of the formula in cell C2 is

=[west.xlsx]West!$C$2+ [east.xlsx]East!$C$2

There is a $ sign in front of the cell address for each workbook. It’s meant the formula above using absolute reference, to change to a relative reference, remove the $ sign of each cell address.

To calculate the total sales of another cell phone. Do a copy (CTRL+C) in cell C2, then paste in range C3:C9. The results are as shown below.

If the sales data for one area is updated, then the total sales data in the workbook total.xlsx will be updated immediately.

Excel Cell Reference Practice File