In general, you must understand cell addresses clearly in order to use Excel. Since the cells contain the values, they are the most essential element for inserting or removing formulas. Moreover, addressing a cell becomes essential when you need to know the cell of your desired value. In this article, I will show you some examples to make you understand what a cell address is in Excel.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
What is Cell and Cell Address in Excel?
Usually, every worksheet in Excel contains a certain number of rows and columns. A cell is nothing but the intersection of a row and a column in an Excel worksheet. Moreover, it is identified as a rectangle shape box we see in the Excel sheet.
Typically, a cell address is used to specify a particular cell in a spreadsheet. It is also known as “cell reference”. Moreover, a cell address can contain both letters and numbers as values. Generally, you can identify a cell address by column and row numbers. However, the column is designated by one or more letters, while the row is denoted by a number.
6 Types and an Example of Cell Address in Excel
When you insert or extract formulas, the cells are the most important factor, as they carry the values. In general, we utilize the cell address or cell reference to return a cell value when performing an Excel formula. In this article, I will show you different types and examples of cell addresses in Excel.
Types of Cell Addresses
Generally, there are three different types of cell addresses in Microsoft Excel. It includes absolute, relative, and mixed-type cell addresses. Apart from those, there are some other types of cell addresses which include cross, 3-D, and circular cell references. In the below part, I will briefly discuss all these types of cell addresses.
1. Relative Cell Address
All cell addresses are Relative type by default. Relative cell references come in handy when you need to develop a formula for a set of cells. Here, the formula makes a reference to a relative cell reference. When you need to repeat the same calculation across numerous rows or columns, relative references are extremely useful. As a result, you can easily apply the AutoFill tool to these types of cell references. For example, F36, G51, and H66 are relative type cell references.
2. Absolute Cell Address
Usually, an absolute cell address is not like a relative cell reference. When filling up cells, there may be situations when you don’t want a cell reference to change. Fortunately, you can utilize the absolute cell address in such cases. It is quite handy to use when you copy formulas but you don’t want the cell reference to alter. You can not use the AutoFill tool or copy paste the same formula to other columns and rows. In this case, you have to use a dollar sign ($) in front of the row and column numbers. However, you can use the absolute cell reference in order to keep the row and column constant. For example, $P$75, $J $91, and $Z$29 are absolute type cell addresses.
3. Mixed Cell Address
In general, a mixed cell address is the combination of both a relative cell address and an absolute cell address. It is quite useful and trickier to use than the other two types of cell addresses. Moreover, you can use it in two different ways. However, the first one is that the row is frozen while the column is changed, and the second one is that the column is locked and the row is changed when the formula is copied. You have to put the dollar sign ($) accordingly. For example, R$35, $K72, and N$88 are mixed type cell addresses.
4. Cross-Cell Reference
Certainly, you will need to refer to cells from different sheets or workbooks. Hence, you must know not only the target cell or cells but also the worksheet and workbook where they are located in order to refer to cells in another worksheet or another Excel file. Generally, this is known as cross cell reference. Place an exclamation point (!) before the cell or range address to refer to a cell or range of cells in a different worksheet. After that, type the name of the target worksheet after the exclamation point (!). For example, you can refer to Cell B2 on Sheet 2 of the same workbook. Just write the following formula.
5. 3-D Cell Address
Sometimes, you need to apply the same procedure to the same cell or range of cells to multiple sheets. This is known as the 3-D cell address. The important thing is that the pattern and data type should be the same across all of the referred sheets. However, you can apply the same function to a particular cell range to multiple sheets. For example, you want to find the sum of values in cells B2 to B6 in Sheet 1, Sheet 2, and Sheet 3. You can use the 3-D reference. Just write the following formula in a casual cell to perform the operation.
6. Circular Cell Reference
Generally, a circular cell reference directly or indirectly returns to its own cell. Circular references are typically problematic, therefore you should try to avoid using them whenever you can. However, in some uncommon circumstances, they might be the only option for a particular task. For example, select Cell B1 and write the following formula. It will create a circular cell reference.
Example: Using ADDRESS Function to Reference Cell by Row and Column
The ADDRESS function in Excel is under the Lookup and Reference functions category. In order to get the address of a cell, you can utilize the ADDRESS function in an Excel worksheet. Moreover, the ADDRESS function is able to return the cell address in three different formats. For the purpose of demonstration, I have used the following dataset.
- Firstly, specify the row and column number in Cell B5 and C5.
- Secondly, enter the following formula in Cell D5 to get the corresponding cell address.
- Thirdly, press Enter However, you can use the AutoFill tool to apply the same formula to the entire column.
- Finally, you will get your desired output cell address as in the following picture.
These are all the steps you can follow to find what is a cell address in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.