Cell referencing in Excel is an indispensable knowledge when mastering Microsoft Excel. It is the process of representing the value of a cell in another cell within a workbook.
Cell references in Excel are a simple way of letting another cell know the value of a cell by using cell addresses. As discussed before, a cell address is represented by the intersection of a column label and a row number.
For example, the image below shows a worksheet with different cell references for computing students’ total and average scores.
In the worksheet above, you can observe that in cell references, you can link to a cell or range. Linking to a cell, e.g., =B1 is the simplest form of a cell reference. In this tutorial, we shall discuss in detail cell referencing in Excel.
Cell Reference in Excel
What is a cell reference?
A cell reference is the value of a cell or range of cells represented in another cell within a worksheet. The cell reference usually represents the address of the referred cell or range in the current cell.
Because cell referencing copies the value of a cell to another, it is usually used in Excel formulas. They represent a variable in Excel formulas because the result of the formula changes when the value of such cells changes.
When working with cell referencing in Excel, you can reference cells as follow:
- Cells within the same worksheet in a workbook
- Cells in a different worksheet in the same workbook, and
- Cells in a worksheet in a different workbook
When cell reference is used in formulas, it helps Excel to locate the actual data or value of such cell. Valid data or values are therefore used by the formula to perform the necessary computations.
Types of cell reference in excel
There are three (3) main types of cell reference in Excel, namely
- Absolute cell reference
- Relative cell reference, and
- Mixed cell reference
There are two (2) other types that are not often used. However, you may find yourself using them in some cases. They are:
- Circular cell reference, and
- 3-D cell reference
Let us discuss each of these types of cell references with examples.
Absolute cell referencing in excel
An absolute cell reference is used to represent a constant cell in Microsoft Excel. When working with formulas in Excel, we usually copy and fill formulas into other rows and columns. This helps to save the time it will take to type the same formula in different cells.
In Microsoft Excel, when formulas are copied, the cell address changes relative to the position of the column/row. This means that, if you copy a formula from cell B2 to cell B3, the reference changes to reflect B3 by default.
However, in absolute cell referencing, this does not happen. The cell address being copied remains constant across the cells it was copied.
To achieve absolute cell referencing in Excel, you must add a dollar sign ($) in the cell address. For example, instead of B2, we have $B$2. This will ensure that cell B2 remains constant when it is copied.
Let us illustrate;
In the diagram below, if we copy the formula in H2 through H11, the result will be the same as shown.
What this means is that the value of cell H2 is replicated all through the cells it is copied to. If the value of cell H2 changes, the result in the referenced cells also changes to reflect the change.
There are cases when you will use absolute cell referencing in computations. For example, if you are working on a variable that has a fixed value, e.g. VAT, interest rate, rent, etc.
Since the value will remain the same in all computations, it is better to make it constant. If the value changes, it reflects in all computations.
Relative cell referencing in excel
Microsoft Excel uses relative cell reference by default. Relative cell referencing is the process of referencing a corresponding cell based on the relative position of a column/row.
This means that when you copy a formula in cell H2 to cell H3, the cell reference changes to H3. That is, the formula will now refer to the corresponding cells in relation to cell H3.
This type of cell reference makes working with excel formulas easy and less tedious. Using this type of cell reference, you can copy a single formula to multiple columns/rows and worksheets.
To use absolute cell referencing, use cell addresses and ranges as they appear by default. That is, use cell A1 as A1 in a referenced cell.
For example, the diagram below shows that copying the formula in I2 through I11 will vary the result.
The result of cells I2 through I11 will vary depending on the corresponding values of cells in their relative positions. E.g., the value of I5 is the sum of cells C5, D5, E5, F5, and G5.
The table below shows how relative cell reference works when the autofill handle is dragged downwards.
Mixed cell referencing in excel
A mixed cell referencing is a process of keeping the row constant while varying the column and vice versa.
By default, when a formula is copied down a column, that column becomes constant. Similarly, when copying within the same row, that row becomes constant.
However, when formulas and functions are copied between rows and columns, you may need to keep either row or column constant.
A mixed cell reference uses the dollar sign ($) on either the column label or the row number. For instance,
| Mixed Cell Reference | Description |
| A$1 | The column is relative while the row is absolute. When copied across columns and rows, values change to reflect the corresponding column position, while rows remain the same. |
| $A1 | The row is relative while the column is absolute. When copied across columns and rows values change to reflect the corresponding row position, while columns remain the same. |
In the example below, we are calculating three (3) different prices of a product based on 3 markups. The price markups appear in row 20 and columns C, D, and E as shown in the worksheet below.
To get the three price levels for each product, we make the row absolute and make the column relative. And to get the price level of all the products, we make the column absolute and row relative.
Hence, we enter the following formula in cell D21 [=(1 + C$20)*$C23]. When you drag across columns, you’ll get price 2 and price 3. When you drag across rows, you get prices for each product at each price level as shown below.
In the worksheet above, we added the dollar sign (C$20) before the row number to make the row fixed. So, when the formula is copied across the rows, the referenced row remains constant and will not change. Similarly, we added the dollar sign ($C23) before the column label to make the column fixed. So, when the formula is copied across the columns, the referenced column remains constant and will not change.
Tip: switch between cell references
You can easily switch between the three types of cell references without typing a dollar sign. To switch between relative to absolute and to mixed cell references, do the following.
- Select the cell that contains the cell reference that you want to change, e.g., C2
- As the cell is selected, click on the formula bar to activate cell editing
- With cell editing active, press F4 on the keyboard.
- Continue pressing F4 until you get your desired cell reference type.
Circular cell referencing in excel
A circular cell reference is a cell that refers to itself directly or indirectly. Microsoft Excel will usually warn you when you use a circular cell reference, hence it’s not to be advised.
However, there are cases when you may use it. For example, in the worksheet above, I entered formula to calculate the cost rate and the cost of a product. The formula is as follow:
- In cell E2 we have [=F2/D2], to get the product cost rate, and
- In cell F2 we have [=D2*E2], to get the total product cost
This is a case of a cell referencing itself indirectly because one value results in another. However, this kind of circular reference is removed when the value of one cell is entered.
3-d cell referencing in excel
A 3-d cell reference in excel is used to reference more than one worksheet at the same time. The worksheets to be referenced usually have the same structure.
For example, the worksheet below calculates the total, average, max, and min scores of students in JS1A, JS1B, and JS1C.
The worksheets have the same structure and occupy sheet names: JS1A, JS1B, and JS1C. We want to calculate the summary scores for the 3 classes at the same time in a worksheet. To perform the calculations, we enter the following formulas:
| English | Maths | Agric. | Science | Social | |
| Total score | =SUM(JS1A:JS1C!C2:C16) | ||||
| Average score | =AVERAGE(JS1A:JS1C!C2:C16) | ||||
| Max score | =MAX(JS1A:JS1C!C2:C16) | ||||
| Min score | =MIN(JS1A:JS1C!C2:C15) |
The above formula can be easily entered in the required cells as follow:
- Select the cell where you want to place the calculation, e.g., B2
- Enter the required function, e.g., =SUM(
- Select the first sheet tab in the 3d reference, in this case, sheet JS1A
- Hold the SHIFT key on the keyboard and click the last sheet tab in the 3d reference, JS1C
- Select the data range for the calculation, in this case, C2:C16
- Close the bracket and press ENTER on the keyboard.
Reference cell in excel
As highlighted above, you can reference a cell within the same worksheet, in a different worksheet and workbook. In this section, we shall look at how to create cell references in excel.
Reference a cell on the same worksheet
The examples we have given so far show how to reference a cell on the same worksheet. This can be achieved by referencing a single cell, range, or defined name as shown below.
| Cell Reference | Explanation |
| =C2 | This is a simple reference that refers to C2 and will return the value of C2 in the selected cell |
| =D2:D11 | This is a range reference that refers to cells D2 through D11. It will return the values of all the cells in the selected range. |
| =Eng | This is a named reference that refers to a cell or range named ‘Eng’ and will return the value(s) contained in Eng. |
How to create cell reference on the same worksheet
- Select the cell in which you want to enter the cell reference.
- Type the equal sign (=) on the formula bar. You can also enter the formula you want.
- Select a cell or range of cells on the same worksheet.
- If you selected a cell, press Enter key on the keyboard. If you selected a range of cells, hold CTRL+SHIFT and press the ENTER key on the keyboard. You can also close the formula bracket if you entered a formula, and press ENTER on the keyboard.
- If you are referencing a defined name,
- Type in the defined cell or range name, and press ENTER, or
- Press F3 and select the defined name from the Paste Name dialog box, click OK, and press ENTER.
Reference a cell to another worksheet
When working with multiple worksheets on the same workbook, you can create cell references between worksheets. The difference between this and the previous is that you will add the sheet name with an exclamation mark (!).
This can be achieved by referencing a single cell, or range as shown below.
| Cell Reference | Explanation |
| =Sheet1!C2 | This is a simple cross-reference that refers to Sheet1 and cell C2 and will return the value of C2 in the selected cell |
| =Sheet1!D2:D11 | This is a range cross-reference that refers to Sheet1 and cells D2 through D11. It will return the values of all the cells in the selected range. |
| Defined name | Defined names are unique in a workbook. You can refer to a defined name from any worksheet in the same workbook. |
How to create cell reference to another worksheet
- Select the cell in which you want to enter the cell reference.
- Type the equal sign (=) on the formula bar. You can also enter the formula you want.
- Select the sheet tab of the worksheet you want to enter its cell reference.
- Select a cell or range of cells in the selected worksheet in (3) above.
- If you selected a cell, press Enter key on the keyboard.
- If you selected a range of cells, hold CTRL+SHIFT and press the ENTER key on the keyboard. You can also close the formula bracket if you entered a formula, and press ENTER on the keyboard.
Create cell reference to another workbook
You can also link to a cell, range, or defined name in another workbook. When data in the referenced workbook changes, it updates in the referring workbook as well.
Here, you will enclose the workbook name in a square bracket followed by the sheet name, and the cell. This can be achieved by referencing a single cell or range as shown below.
| Cell Reference | Explanation |
| ='[workbook name.xlsx]Sheet1′!$G$2 | Referring to a cell in sheet1 of another workbook |
| ='[workbook name.xlsx]Sheet1′!$C$4:$G$4 | Referring to a range in sheet1 of another workbook |
| =’workbook name.xlsx’!definedname | Referring to a defined name in another workbook |
How to create cell reference to another workbook
- Open both workbooks, if more than two, open all the workbooks.
- Select the cell in which you want to enter the cell reference.
- Type the equal sign (=) on the formula bar. You can also enter the formula you want.
- Select the workbook you want to enter its cell reference.
- Select the sheet tab of the worksheet you want to enter its cell reference
- If you selected a cell, press Enter key on the keyboard.
- If you selected a range of cells, hold CTRL+SHIFT and press the ENTER key on the keyboard. You can also close the formula bracket if you entered a formula, and press ENTER on the keyboard.
- If you are referencing a defined name,
- Type in the defined cell or range name, and press ENTER, or
- Press F3 and select the defined name from the Paste Name dialog box, click OK, and press ENTER.
Conclusion
Cell referencing in Excel is very important. Its knowledge will help you master how to add and copy formulas in Excel to make your work easy.
So far, we have discussed what is cell a cell reference, and the types of cell referencing in Excel. We also discussed how to create cell references in a workbook.
If you did not understand any part of this tutorial, please, contact us for clarification. We shall continue next week with working with formulas in Excel. Before then, kindly click the share button to share.
Previous tutorials in this series include: