What Is Cell Reference In Excel?
A cell reference in Excel refers to a cell or a cell range. It can be either an alphanumeric value or the name assigned to the referred cell. Users can use cell references in excel formulas to refer to data in cells of the same or different workbooks. It helps them find the values on a worksheet to perform the necessary calculations.
For example, you want to find out the square of the value in cell A2 and display the output in cell B2. You can use the required formula, with a cell reference to cell A2, in cell B2. The expression in cell B2 will be:
=A2*A2
When you press the Enter key, you will get the result as 25 in cell B2. Cell references to a cell or cell range are also possible using their defined names.
For instance, the image below shows two tables. The first one lists the monthly salary and expenses in $, while the second table shows the total savings in $. Using the Define Name option in the Formulas tab, we named cells B2:B7 as Total_Monthly_Salary and C2:C7, Total_Monthly_Expenses.
And the SUM excel formula to calculate the total savings in cell B9 becomes:
=SUM(Total_Monthly_Salary)-SUM(Total_Monthly_Expenses)
So, here the cell references are represented using the names, Total_Monthly_Salary and Total_Monthly_Expenses for cell ranges B2:B7 and C2:C7. We do not have to mention the cell coordinates for the two cell ranges referenced in the SUM function.
Table of contents
- What Is Cell Reference In Excel?
- Range Reference
- Cell Reference Styles
- How To Create A Reference In Excel?
- How To Change A Cell Reference In A Excel Formula?
- Cross Reference In Excel
- Types Of Cell References in Excel
- Switch Between Different References Types
- Circular Reference
- Frequently Asked Questions (FAQs)
- Download Template
- Recommended Articles
Key Takeaways
- Cell references in Excel enable users to refer to cells or cell ranges in a formula and use the data or values present in the specific cells for completing the calculations.
- A1 and R1C1 are the two cell reference styles.
- One can change cell references and cross-reference Excel cells from another worksheet or even a different workbook.
- Relative, absolute, and mixed are the prominent cell reference types. One can switch between them in a formula using the F4 key. You can also enable circular reference when you want a cell reference in a formula referencing its cell.
Range Reference
Range reference refers to the cells between and including the referenced cells. For example, you can use a range reference in a formula by providing addresses of the first and last cells of the required cell range, separated by a colon.
Some examples of the range references in Excel are listed below:
Range Reference | Interpretation |
---|---|
F1:F7 | Cells from rows 1 to 7 of column F |
G2:J10 | Cells from rows 2 to 10 of columns G, H, I, J |
H:K | All cells in columns H, I, J, K |
A:A | All cells in column A |
4:4 | All cells in row 4 |
We can use them in formulas like VLOOKUP and SUMIFS. Moreover, range references can have absolute, relative, or mixed references. These cell references in Excel enable users to ensure specific cell range row or column or both references remain constant, as required for formulas.
The above examples follow the A1 referencing style. However, excel cell references also follow other alternatives, which you will learn about in the following section.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Cell Reference Styles
Excel offers two cell or range referencing styles, A1 and R1C1. These offer users ways to use cell references in a formula.
#1 – A1 Reference
The A1 style reference is the most widely used form of cell reference in Excel. It consists of a letter representing the cell column and a number that denotes the row reference.
For example, consider the image below. It shows two tables. One lists the employee names and their designation and the second table shows the pay scale band for each title.
The aim is to enter the pay scale band details for the employees listed in the first table using the VLOOKUP function from Lookup & Reference option in the Formulas tab.
Below are the steps to lookup data from the second table and enter it in the first table in column C, using the A1-style of cell references in Excel:
Step 1: In cell C2, enter the VLOOKUP function. The arguments you will require to enter are as follows:
lookup_value: B2
table_array: E:F
col_index_num: 2
[range_lookup]: 0
Ensure you enter a ‘,‘ after every argument, and the formula in cell C2 becomes:
=VLOOKUP(B2,E:F,2,0)
Step 2: Press the Enter key to view the output.
Step 3: With the cursor on cell C2, drag, and select cell C3:C15 to copy the cell C2 formula. You will achieve the result as shown below.
Please Note: The VLOOKUP function uses the A1 reference style. While the alphabet in an excel cell reference denotes the cell column, the number indicates the respective cell row. These cell references used in the formula are quite straightforward, making A1-style the default referencing format in Excel.
#2 – R1C1 Reference
It is another style for using cell references in excel formulas. It uses row and column offset values to refer to the respective cell row and column.
If you wish to enable this referencing style in the excel workbook, go to Fileà Options à Formulas. Then, select the R1C1 Reference Style option under the Working with formulas section. Once you enable it, you will see the columns represented in numbers and the cell reference in the Name box as shown below:
In the above scenario, cell A1 gets denoted as cell R1C1, where R1 represents row 1 and C1 indicates column 1.
R1C1 referencing style offers two reference types, relative and absolute.
When using the relative option, the default type, you need to include square brackets for the numbers. In this case, you refer to a cell by the row and column number relative to the current cell.
For example, the table below lists fruits, their respective quantities, and rates per kilogram (Kg). And column 4 is where you will enter the product of the amount and rate per Kg as the total price for each fruit.
Step 1: In cell R2C4 (cell D2 in A1 reference style), enter ‘=’, select cell R2C2, type ‘*’, and select cell R2C3. The formula becomes:
=RC[-2]*RC[-1]
Step 2: Press the Enter key. You will get 10 as the output in cell R2C4.
Step 3: Next, with the cursor on cell R2C4, drag downwards to copy cell R2C4 formula till cell R6C4.
Please Note: The expression remains the same from cell R2C4 to R6C4. Each time the current cell changes, every cell reference in the formula remains relative to the new current cell.
For example, when you enter the expression in cell R2C4, the references to cells R2C2 and R2C3 are RC[-2] and RC[-1] relative to cell R2C4. As the row is 2 for both the cells in the formula, the row reference remains as R. However, the columns shift two cells and one cell to the left of cell R2C4 while selecting cells R2C2 and R2C3, so their references change to C[-2] and C[-1] respectively.
On the other hand, you do not require the square brackets for the absolute option. Instead, you can refer to a cell by its row and column number after the notation R and C, respectively. Considering the above example, you will have to type the formula in cell R2C4, referencing the same two cells, R2C2 and R2C3, in the following way to achieve the same output.
=R2C2*R2C3
Please Note: In the absolute referencing option with R1C1-style, the cell referencing remains constant wherever you copy the formula.
How To Create A Reference In Excel?
You can create Excel cell references when entering formulas in other cells. You can refer to a single cell, multiple cells, or a cell range, depending on your calculation needs.
- Select the cell where you want to enter the formula.
- Enter ‘=’ and type the formula.
- Select the specific cell or range as a reference. You can also enter the cells and cell ranges, refer to their defined names, and manually complete the formula.
- Press the Enter
Please refer to the image below. It shows two tables. The first table shows the attendance details of 4 employees for week 1. And the second table is where you need to determine the number of days they marked their attendance in the week, using the COUNTIF excel formula with cell references in cells D2:D5.
Step 1: Select cell D2 to enter the formula.
Step 2: Type ‘=’ and enter the COUNTIF function with the range argument A:A and the criteria argument C2. The formula becomes:
=COUNTIF(A:A,C2)
Step 3: Press the Enter key to view the output.
Step 4: With the cursor on cell D2, drag downwards to copy the formula into cells D3:D5. The result will be as depicted below.
Please Note: Here, the referencing style is A1. And the COUNTIF function refers to a cell range to count a value, which is the criteria present in another cell.
The above example shows relative references. The excel cell references change as you copy the formula with the specific references from one cell to another. The change is relative to the cell where you copy the formula.
In the above scenario, the cell reference changes from C2 to C5 as you copy the formula from cells D2:D5. But the cell range remains the same A:A in all cells in column D. If you copy the function in a cell to the left or right of cell D2, the cell range will change accordingly. However, if you wish a cell reference remains constant in a formula when copying it in different cells, you need to use an absolute cell reference.
- Choose the cell to enter the formula.
- Enter ‘=’ and type the formula.
- Select the cells you want to refer to in the formula and insert the ‘$’ symbol before the rows and columns of the cells you wish to maintain as constant.
- Complete the formula and press Enter to view the result.
You can also keep the row or column constant in a cell while referencing it in a formula. It is a scenario of mixed references. You need to insert the ‘$’ symbol in front of the part (row or column) of the cell reference in the excel formula you need to maintain as a constant while copying it into other cells.
How To Change A Cell Reference In A Excel Formula?
You can change cell references in Excel, be it a cell or cell range.
For example, consider the table shown in the image below.
Suppose you added two cell references in cell D2 referencing an incorrect cell and copied the formula in cells D3:D5, as shown below:
Instead of referencing cell C2, you referenced cell C3 in cell D2. Here is how you can change it:
Step 1: Select cell D2. In the Formula Bar, change row 3 of cell C3 to 2 to change the cell reference to C2. The formula becomes:
=B2+C2
Step 2: With the cursor on cell D2, drag and copy the formula in cells D3:D5. You will se the updated result as shown below:
Please Note: You can also delete the cell reference you wish to change and select or enter the correct cell reference instead in the formula.
Likewise, you can change a cell range in a formula in the following ways.
- Use the cursor to increase or decrease the cell range if it is the same row or column you want to make changes in, as per your new requirements.
- Delete the existing cell range in the formula and select or enter the required cell range. The new cell range will get highlighted.
- Manually update a named range in a formula by replacing it with the required cell range with a defined name.
Cross Reference In Excel
Cell references in Excel also include scenarios where you can refer to a cell of another worksheet or workbook. Here are a few illustrations to explain the process in the two cases.
1. From Another Sheet
Consider you have two worksheets, State_Demographics and State_Demo_Asian_Hawaiian. The first sheet represents the demographics for various United States states, while the second worksheet shows the Asian and Hawaiian demographics in the respective states. The aim is to find the demographics in the others category for the U.S. states listed, apart from Whites, Blacks, Asians, and Hawaiians.
Step 1: Select cell D2 in the worksheet State_Demographics to enter the formula for the first state, Alabama. Enter ‘=’, 1, ‘–‘, ‘(‘, select cell B2, type ‘+’, select cell C2, and type ‘+’. Then go to worksheet State_Demo_Asian_Hawaiian, select cell B2, type ‘+’, select cell C2, and enter ‘)’. The formula in cell D2 of worksheet State_Demographics will be:
=1-(B2+C2+State_Demo_Asian_Hawaiian!B2+State_Demo_Asian_Hawaiian!C2)
Step 2: Press the Enter key to view the result.
Step 3: With the cursor on cell D2 of worksheet State_Demographics, drag and copy the formula in cells D3:D11. The output will be as depicted below.
Please Note: Cell references in different excel sheets use extended addresses to identify the cells in the other worksheet. The extended address includes the worksheet name, row, and column coordinates.
2. From Another Workbook
Suppose you have two workbooks, Country_Electricity_Population Ranking and Country_Total Population. And you need to enter the rank for each country listed in the first workbook based on the population data provided in the second workbook. You can use the MATCH Excel function and ensure both the workbooks are open. The images for the two workbooks are:
Step 1: Select cell D2 of the workbook Country_Electricity_Population Ranking. Enter ‘=’, Match, ‘(‘, select cell A2, and type ‘,’. Then go to workbook Country_Total Population and select cell range B2:B41, enter ‘,’ and 0. Here the MATCH function arguments are:
lookup_value: A2
lookup_array: ‘[Country_Total Population.xlsx]Sheet1’!$B$2:$B$41
[match_type]: 0
Thus, the formula in cell D2 of workbook Country_Electricity_Population Ranking, Sheet 1 is:
=MATCH(A2,'[Country_Total Population.xlsx]Sheet1′!$B$2:$B$41,0)
Step 2: Press Enter to view the output.
Step 3: With the cursor on cell D2, drag and copy the formula in cells D3:D11. You will achieve the output as shown in the below image.
Please Note: In this case, the cell references in one workbook use an extended cell address to refer to cells from another. It includes the workbook name, sheet name, and specific cell range of the cells referred to from the other workbook.
And if you open the other workbook only while entering the formula, you will see the file location displayed before the workbook name in the expression. However, it will give you the same output.
Types Of Cell References in Excel
Typically, you will use three types of cell references in your daily tasks, which are as follows:
1. Relative Cell References
In Excel, a cell reference is by default a relative reference. The reference to a cell is relative to the location of the cell you refer to it from in a formula. So, when you use relative cell references in a function, the references change when you copy the formula into another cell. And the updated references will be relative to the new cell location.
2. Absolute Cell References
Absolute references in excel ensure locking cell references. An absolute reference in a function does not change when you move or copy the formula into another cell. In the formula, you will have to insert the ‘$’ symbol before the row and column coordinates of the cells for which you wish to create absolute cell references.
3. Mixed Cell References
Mixed references in excel also allow locking cell references. However, you can keep the cell row or column static at a time by inserting the ‘$’ symbol before the specific cell coordinate in the formula. The other part, which is non-static, remains relative and changes according to the location of the cell where you copy or move the formula.
Switch Between Different References Types
You can use the F4 key to switch between various cell references in excel. It is the shortcut method, and the steps are as follows:
- First, select the cell with the formula where you wish to update cell references.
- Select the specific cell reference in the Formula Bar and press the F4.
- Keep pressing the F4 key until you have the cell reference type you want to use in the formula. For instance, if the cell reference is initially relative, you need to press the F4 key once to make the reference absolute. On pressing it twice, you will have the cell row constant, and when you use the key for the third time, the cell column becomes static. In this way, you can have mixed cell references. Finally, the cell reference becomes relative again when you press the F4 key for the fourth time.
You can also switch between the three cell reference types by manually adding or removing the ‘$‘ symbol in front of the cell coordinates in the formula. But, again, it will depend on the references you want to use as constants and non-static while applying and copying a formula into other cells.
Circular Reference
You get a circular reference in excel when you have a formula that refers to its cell. Such cell references in excel can be either direct or indirect.
You can allow circular references in Excel. Select File > Options > Formulas and enable the Enable iterative calculation option below.
Applying a circular reference formula will return a 0 or the last calculated value as the outcome.
Consider a scenario where you want to apply a formula in cell D5, which uses a reference to cell D5. You will get the following output:
However, you will get the below error message if you do not enable iterative calculation.
When you click OK, you will see the output as 0 with a message below the worksheet name, stating cell D5 has a circular reference, as shown below:
You can also check the Circular References option in the Error Checking drop-down option in the Formulas tab. And you can see the cell with a circular reference.
Frequently Asked Questions (FAQs)
Enter the formula in the required cell with the ‘=‘ sign. If you have to refer to a cell in the function, type the cell row and column or select the cell. You can enter or select as many cells similarly per your calculation requirements.
You can use the A1 or R1C1 referencing style to denote cell coordinates in a formula.
The three cell reference types are relative, absolute, and mixed.
Select the cell or cell range if you have a formula applied and wish to replace a cell reference in the expression. Next, press Ctrl + H or click on the Replace option in the Find & Select drop-down option in the Home tab. The below window will pop up.
Enter the cell reference you want to replace in the Find what box and the cell reference you want as the replacement in the Replace with box. Also, select Formulas in the Look in box and click Replace All to update all reference instances in the formula.
Suppose you want to copy a cell reference from one sheet to another. Then for referring to the cell in the formula in the other worksheet, the syntax you will use in the expression for cell referencing is:
Sheet_name!Cell_address
And if it is a cell range, the syntax becomes:
Sheet_name!First_cell:Last_cell
Download Template
This article must be helpful to understand the Cell References in Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Cell References in Excel. Here we explain their styles, types, how to create & change them with examples, & a downloadable template. You can learn more from the following articles –
Leave a Reply