How to use ADDRESS function in Excel

The ADDRESS function in Excel takes the number of rows and column numbers as arguments and returns the reference of the standard cell (cell address). For example, if you go to row 4 and column 3, the function returns C4. The ADDRESS function of Excel can return an absolute or relative reference in either of the two reference formats of Excel.

Types of references

1. Relative references are expressed in columns and row numbers (eg M290 ). When you copy a formula that contains relative cell references, references - row numbers and column characters - are adjusted to reflect the position where you copied the formula.
2. The absolute reference has the symbol $ in front of the column letter and the row number (eg $ M $ 290 ). When you copy a formula that contains absolute cell references, that reference remains unchanged.
3. Mixed references have the symbol $ in front of the column or row number (eg $ M290 or M $ 290 ). When you copy a formula that contains a mixed cell reference, the reference part with the symbol $ does not change, but the other part does.

The following image shows a spreadsheet in which the formula entry with relative cell references causes a problem. The total (Total) is the result of adding taxes to the initial amount. The tax rate is 7.5% (0.075). This ratio is in cell C1 and is referenced by formulas. The first formula is entered in cell C7 and has the following content: = B7 * (1 + C1) .

The formula in cell C7 is correct. It references cell C1 to calculate the sum. But if you use fill handle (the black dot in the lower right corner of the selected cell) to copy the formula from cell C7 to cell C8 and C9, there will be a problem. Reference to cell C1 has changed to cell C2 and C3. Because these fields are empty, the results in cells C8 and C9 are incorrect (the results are the same as the amount on the left and the tax has not been added).

To better understand, look at column D showing the formulas in column C. When the formula in cell C7 is pulled down, the C1 reference has changed to C2 in cell C8 and C3 in cell C9. Typically, users will want to let Excel automatically change the cell reference when the formula is copied. But sometimes (in this situation, for example), you don't want to do this. You need an absolute cell reference.

The formula in cell C17 is similar to the formula in cell C7, except that the reference to cell C1 has been made absolute, by placing the $ symbol before the row number. The formula in cell C17 looks like this: = B17 * (1 + C $ 1) . When this formula is dragged down to C18 and C19, the reference is not adjusted but remains in cell C1. Note that in this example, only the row of the reference is absolute. You may have created an absolutely absolute reference by entering the following syntax: = B17 * (1 + $ C $ 1) . The result will be the same, but that is not required in this example.

2 cell reference types

Note : Set the $ symbol before the column letter of the cell reference to create the absolute column reference. Set the $ symbol before the number of rows to create the absolute row reference.

Excel supports two types of cell references: the old A1 style and the R1C1 style . The R1C1 type uses a number system for both rows and columns, such as: R4C10. In this example, R4C10 means row 4 column 10.

To change the cell reference type, select File> Options and check the R1C1 reference type in the Working with Formulas area on the Formulas tab . Using R1C1 format also forces the columns on the spreadsheet to display numerically instead of the text system. This is useful when you work with a large number of columns. For example, the CV column by position is the 100th column. Remember the number 100 is easier than remembering the CV.

To return to the ADDRESS function of Excel, it takes up to 5 arguments:

1. Number of rows of reference
2. Number of columns of reference
3. Some let the function know how to return a reference. This number defaults to 1 , but can also be:
   1. 1 : Both row and column are absolute
   2. 2 : Absolute row and relative column
   3. 3 : Relative and absolute columns
   4. 4 : Both row and column are relative
4. Value 0 or 1 to let the function know which reference type to use:
   1. 0 uses R1C1 type.
   2. 1 (default value and ignored if used) uses type A1.
5. An external workbook or workbook and spreadsheet reference.

Only the first two arguments are required. The function returns the reference specified as text.

=ADDRESS(5,2) $B$5 Only columns and rows are provided as arguments. The function returns an absolute absolute address. =ADDRESS(5,2,1) $B$5 When 1 is used as the third argument, an absolute absolute address is returned. The result is the same if omitting the third argument. =ADDRESS(5,2,2) B$5 When 2 is used as the third argument, a mixed reference is returned, with relative and absolute column values. =ADDRESS(5,2,3) $B5 When 3 is used as the third argument, a mixed reference is returned, with absolute and relative column values. =ADDRESS(5,2,4) B5 When 4 is used as the third argument, a relatively complete reference is returned. =ADDRESS(5,2,1,0) R5C2 When the fourth argument is incorrect, the R1C1 type reference is returned. =ADDRESS(5,2,3,0) R[5]C2 This example indicates that the function returns a mixed reference in the style R1C1. =ADDRESS(5,2,1,,"Sheet4") Sheet4!$B$5 The fifth argument returns a reference to an external workbook or workbook. This syntax returns reference type A1 to cell B5 on Sheet 4. =ADDRESS(5,2,1,0,"Sheet4") Sheet4!R5C2 This syntax returns the reference type R1C1 for cell B5 on Sheet 4.

How to use the ADDRESS function in Excel

Use the ADDRESS function in Excel as follows:

1. Click on a cell where you want the results to appear.

2. Enter = ADDRESS ( to start the function.

3. Enter the row number, comma (,) and column number. You can also enter references to cells where those values ​​are set.

4. If you want the result to be returned in a mixed or complete reference, enter a comma (,) and an appropriate number: 2, 3 or 4 .

5. If you want the result returned as R1C1, enter a comma (,) and 0 .

6. If you want the result to refer to another spreadsheet, enter a comma and put the name of the spreadsheet in quotation marks.

If you want the reference to an external workbook, enter a comma (,), workbook name and worksheet together. The workbook name is enclosed in brackets and all references have quotation marks, such as: "[Book1] Sheet2".

7. Enter ) and press Enter.

Instead of entering the row number and column number directly in ADDRESS, you can enter the cell reference. However, the value you find in those cells must be estimated for numbers that can be used as the number of rows and columns.