To perform calculations and retrieve data based on the values in specific cells, you must know how to use a cell address in Excel Formula. However, it can be intimidating for beginners unfamiliar with its functions and formulas.
you can create complex formulas that automate tasks and save time in your work. In this article, we’ll explore the basics of how to use the cell address in Excel, including cell references in formulas, relative and absolute references, and ways to apply these skills in various contexts.
What Is Cell Address in Excel?
In Excel, a cell address refers to the unique identifier of a specific cell in a worksheet. It is made up of the column letter and row number that intersect at the cell. For example, cell A1 is the first cell in the first column of the worksheet, while cell B4 is the fourth cell in the second column of the worksheet.
Read More: Example of Cell Address in Excel
How to Use Cell Address in Excel Formula: 8 Suitable Examples
Let’s consider a dataset of the Sales Report of a store where we have ID, customer, product, quantity, price, and delivery date of some products.
Using this dataset, we will explore how to use the cell address in Excel formulas. This section will cover 8 suitable examples of using cell addresses.
1. Show Cell Value by Referring to Cell Address Directly
You can just directly refer to a cell to show the value of this cell. Put an equal sign in a cell where you want to show the cell value of another cell and click on the cell of which value you want to show. For example, if you want to show the cell value of C7 in cell E19, enter the following formula cell E19.
=C7
2. Convert Cell Address Obtained with ADDRESS Function to Value
The ADDRESS function takes the row number and column number as arguments and returns the address of a specific cell, and wrapping the ADDRESS with the INDIRECT function converts the cell address to a value.
Here, in the example, we have a row number in cell E18 and a column number in cell E19. In cell E20, let’s apply the formula below.
=INDIRECT(ADDRESS(E18,E19))Here,
- E18 = Row number
- E19 = Column number
💡 Formula Breakdown
The cell address of row and column number is used in the ADDRESS function. Later, the ADDRESS(E18,E19) portion returns the cell value of row 10 and column 4 => D10.
The INDIRECT function converts the address to value => Monitor.
3. Use Relative Cell Address to Perform Calculations for Range
Generally, relative cell reference is used to perform calculations for a range. If the row and column are not locked, they change by dragging the Fill Handle tool to make the formula dynamic.
If our dataset has the price of each product and its quantity, we can calculate the total price by simply multiplying the quantity by the unit price. To find the total price of the Laptop, apply the following formula in cell G5 to multiply the cell values of E5 and F5.
=E5*F5Now, to find the total price of the other products, just drag the Fill Handle tool downwards and it will automatically calculate the prices by changing the cell references as we have used relative reference. In cell G6, the formula will be automatically changed to
=E6*F6And so on for the rest of the cells.
4. Use Absolute Reference to Lock Cell Address
The absolute reference means that kind of reference where the row and column number don’t change while dragging or copying the formula.
5. Use Cell Address to Trace a Cell of Highest and Lowest Value
In this segment, we will use the cell address to trace a cell with the Highest and Lowest numbers. The method is very simple and handy.
From the Quantity column in our dataset, let’s apply the MAX function to find the maximum value.
=MAX(E5:E16)To find the cell address of the highest value, apply the formula below-combined ADDRESS, MATCH, and COLUMN functions.
=ADDRESS(MATCH(E19,E:E,0), COLUMN(E4))💡 Formula Breakdown
The MATCH function searches for an exact match of cell E19 in column E:E and returns the position of E19 => 9.
COLUMN(E4) returns the column number => 5.
Finally, the ADDRESS function returns the cell address using the row and column index. ADDRESS(MATCH(E19,E:E,0), COLUMN(E4)) = ADDRESS(9,5) = $E$9.
Similarly, you can find the address and value cell with minimum quantity with the MIN function.
Read More: How to Return Cell Address of Match in Excel
6. Use OFFSET Function to Refer Cell Based on Another Address
When you need to refer to a cell based on its position relative to another cell, you can use the OFFSET function. This function starts off from a particular cell, moves to a specific number of rows down, then to a specific number of columns right, and then extracts out a section from the data set having a specific height and width.
For the dataset, if we want to extract the information about the Product, Quantity, Price, and Delivery date (4 types of info) of the 5th customer, apply the formula below.
=OFFSET(D4,5,0,1,4)This will extract a section with height 1 and width 4 after moving just 5 rows down 0 rows right to cell D4.
7. Get Column Letter from Column Number Using Cell Address
You can use the cell address and with the help of the column number, you can also get the column letter.
For our dataset, we have placed the column heading on the right side where we have also put the column number of the header. To find the column letter from the column number, we can create a formula combining ADDRESS with the SUBSTITUTE function.
To find the letter from the number of cell J5, apply the following formula in cell K5.
=SUBSTITUTE(ADDRESS(1,J5,4),"1","")💡 Formula Breakdown
The ADDRESS function returns the address of the cell located in the first row and the column number specified by the value in cell J5.
The SUBSTITUTE function replaces a specified text string with a different text string in a given text. In this case, the SUBSTITUTE function replaces the string “1“.
So, the overall result of this formula is a text string that represents the column letter of the cell specified in cell J5, without the row number => B.
8. Extract the Cell Address of a Data Range
In this method, we will use the cell address to get the address of a full dataset table. We have renamed our dataset range to “Sales”.
Then apply the formula below to get the cell address of this range. Microsoft Excel Version 365 will return the address of every cell of the named range.
=ADDRESS(ROW(Sales),COLUMN(Sales))
But in other versions, you have to put the formula individually for finding the address of the full range, the first cell of the range, and the last cell of the range.
The formula for finding the address of the first cell:
=ADDRESS(ROW(Sales), COLUMN(Sales))For the last cell:
=ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)Here,
- ROW(Sales) + ROWS(Sales)-1 means the last absolute Row Number &
- COLUMN(Sales) + COLUMNS(Sales)-1 means the last absolute Column number of the dataset.
For cell range (i.e. B5:G16):
=ADDRESS(ROW(Sales), COLUMN(Sales)) & ":" & ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)Here,
- ROW(Sales), COLUMN(Sales) means the first Row and Column number respectively.
- ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1 means the last row and column number respectively.
That’s all about how to use cell addresses in Excel formulas.
Frequently Asked Questions
1. What is a formula in Excel?
In Excel, a formula is a set of instructions that performs a calculation on one or more values and produces a resulting value.
2. What is the last cell address in Excel?
To determine the last cell address in your worksheet, you can use the “CTRL+END” keyboard shortcut. This will take you to the last cell in your worksheet that has been used or formatted.
Takeaways from this Article
- Cell references allow you to refer to specific cells in your formulas and perform calculations or manipulations based on the values in those cells.
- Absolute references always refer to the same cell, relative references change based on the position of the formula.
- You can use the ADDRESS function to create a text representation of a cell address based on its row and column number. This can be useful when constructing formulas that refer to specific cells.
- You can use the INDIRECT function to create a reference to a cell based on a text string that represents its address. This can be useful when you want to dynamically refer to a cell based on the value in another cell.
- You can use the OFFSET function to create a reference to a cell based on its position relative to another cell. This can be useful when you want to refer to a range of cells that is not fixed in size or position.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
In conclusion, we can say that you can easily and efficiently perform calculations, analyze data, and create complex spreadsheets if you know how to use cell addresses in Excel formulas. Whether you’re a beginner or an advanced user, mastering the use of cell addresses can help you save time and improve the accuracy of your work. So start practicing and experimenting with different formulas, and you’ll be on your way to becoming an Excel expert in no time.
