Sometimes, while working on an Excel worksheet, one may need to Use Cell Address in Excel Formula. Addressing a cell becomes essential when you need to know the cell of your desired value. As using cell Address in Excel Formula is an essential job, I will show how to Use Cell Address in Excel Formula in this article with 4 easy methods. Hope you will understand the methods and increase your Excel skill.
Download Practice Workbook
Please download the practice workbook to practice yourself.
4 Easy Methods to Use Cell Address in Excel Formula
Let’s consider a dataset of the Sales Report of XYZ Traders. The dataset consists of four columns B, C, D, & E called ID, Customer Name, Product, and No. of Products respectively. The dataset ranges from B5 to E11. Moreover, the whole dataset has 7 rows. So, without further delay, let’s have a look at the methods to Use Cell Address in Excel Formula.
1. Return Specific Cell Value by Using Cell Address in Excel Formula
In this method, I will Use Cell Address in Excel Formula to return a specific value. It is a simple process. I will show the method step by step with specific illustrations.
- I have created a new table consisting of the Row number, Column Number, and Results.
- In C15, Write down the following formula.
- The argument C13 of ADDRESS Function indicates Row Number & C14 indicates Column Number.
- The INDIRECT Function returns a text value.
- As a result, You will find the text Hard Disc in Cell C15.
2. Use Cell Address to Trace a Cell of Highest and Lowest Value
In this part of this article, I will show another method to Use Cell Address in Excel Formula. The Cell Address will be used to trace a cell with the Highest and Lowest numbers. The method is very simple and handy. Hope you will understand it easily.
- I have added a new table ranging from B13 to D15 to find the maximum and minimum values as well as Cell numbers.
- Then, in cell C14, write down the following formula to find the maximum number.
- After that, Write down the following formula in the cell to find the minimum number.
- Hence, in cell C15, Write down the following formula to trace the cell having the highest number.
- Argument C14 indicates the lookup value which will be looked up in the desired range.
- E:E is the lookup array where the MATCH Function searches the value in C14.
- 0 means that MATCH finds the first value exactly equal to the lookup value.
- The value returns from the MATCH Function is the first argument of the ADDRESS
- The Argument COLUMN(E5) indicates the column number of the ADDRESS Function.
- Hence, you will find the cell number of the maximum value.
- Moreover, to indicate the cell number of minimum value write down the following formula.
- As a result, you will find the cell number of minimum value just like the picture is shown below.
3. Get Column Letter from Column Number by Using Cell Address in Excel Formula
In this portion of this article, we will use a modified version of our dataset. Let’s consider 2 new columns called column number and column letter instead of products and Product number. I will Use Cell Address in Excel Formula to get a column letter from a column number. Follow the steps described in the following.
- Write down the following formula in cell E5.
- Use 1 as the row number.
- Give a reference to the cell that contains the number, in this case, A2 for the column number.
- Enter 4 as the abs_num argument.
- ADDRESS(1, A2,4) would therefore return A1.
- Wrap the formula above in the SUBSTITUTE function and exchange out the “1” for an empty string to remove the row coordinate (“”).
- As a result, you will find the column letter in cell E5.
- Then, Fill handle the formula from E5 to E11.
- At last, you will find the result just like the picture given below.
4. Use Cell Address in Excel Formula to Get Address of a Dataset
This is the last method to use trace of cell in Excel Formula. In this method I will use cell address to get address of a full dataset table. I have added three new rows and two new columns to address the first cell, last cell, and a full range of the dataset. Let’s practice the method step by step.
- First Select the whole dataset and change its’ name to Sales.
- Write down the following formula in the H5
- Then, Press the Enter
- You will find the first cell’s absolute address in H5.
- In the H6 cell, write down the following formula.
=ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)
- 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.
- After that, you will find the result just like the one given below.
- Then, Write down the following formula in the H7
=ADDRESS(ROW(Sales), COLUMN(Sales)) & ":" & ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)
- The address function is used twice.
- ROW(Sales) and COLUMN(Sales) mean the first Row and Column number respectively.
- ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1 mean the last row and column number respectively.
- At last, you will see the result just like the picture given below.
Things to Remember
- Be careful about the equations you are using in the fourth method as they are quite long formulas.
In this article, I tried to show how to use the cell address in the excel formula. Hope you have enjoyed the mentioned methods. If you have any queries, feel free to ask me in the comment section.