The tutorial explains what a cell address is, how to make absolute and relative references in Excel, how to reference a cell in another sheet, and more.
As simple as it seems, Excel cell reference confuses many users. How is a cell address defined in Excel? What is an absolute and relative reference and when each should be used? How to cross reference between different worksheets and files? In this tutorial, you will find answers to these and many more questions.
A cell reference or cell address is a combination of a column letter and a row number that identifies a cell on a worksheet.
For example, A1 refers to the cell at the intersection of column A and row 1; B2 refers to the second cell in column B, and so on.
When used in a formula, cell references help Excel find the values the formula should calculate.
For instance, to pull the value of A1 to another cell, you use this simple formula:
To add up the values in cells A1 and A2, you use this one:
In Microsoft Excel, a range is a block of two or more cells. A range reference is represented by the address of the upper left cell and the lower right cell separated with a colon.
For example, the range A1:C2 includes 6 cells from A1 through C2.
There exist two address styles in Excel: A1 and R1C1.
A1 is the default style used most of the time. In this style, columns are defined by letters and rows by numbers, i.e. A1 designates a cell in column A, row 1.
R1C1 is the style where both rows and columns are identified by numbers, i.e. R1C1 designates a cell in row 1, column 1.
The below screenshot illustrates both the A1 and R1C1 reference styles:
To switch from the default A1 style to R1C1, click File > Options > Formulas, and then uncheck the R1C1 reference style box.
To make a cell reference on the same sheet, this is what you need to do:
For example, to add up the values in cells A1 and A2, you type the equal sign, click A1, type the plus sign, click A2 and press Enter:
To create a range reference, select a range of cells on the worksheet.
For example, to add up the values in cells A1, A2 and A3, type the equal sign followed by the name of the SUM function and the opening parenthesis, select the cells from A1 through A3, type the closing parenthesis, and press Enter:
To refer to the whole row or entire column, click the row number or the column letter, respectively.
For instance, to add up all the cells in row 1, start typing the SUM function, and then click the header of the first row to include the row reference in your formula:
To change a cell address in an existing formula, carry out these steps:
To refer to cells in another worksheet or a different Excel file, you must identify not only the target cell(s), but also the sheet and workbook where the cells are located. This can be done by using so-called external cell reference.
To refer to a cell or a range of cells in another worksheet, type the name of the target worksheet followed by an exclamation point (!) before the cell or range address.
For example, here's how you can refer to cell A1 on Sheet2 in the same workbook:
If the name of the worksheet contains spaces or nonalphabetical characters, you must enclose the name within single quotation marks, e.g.:
To prevent possible typos and mistakes, you can get Excel to create an external reference for you automatically. Here's how:
For more information, please see How to reference cell in another worksheet in Excel.
To refer to a cell or range of cells in a different Excel file, you need to include the workbook name in square brackets, followed by the sheet name, exclamation point, and the cell or a range address. For example:
If the file or sheet name contains non-alphabetical characters, be sure to enclose the path in single quotation marks, e.g.
As with a reference to another sheet, you don't have to type the path manually. A faster way is to switch to the other workbook and select a cell or a range of cells there.
For the detailed guidance, please see How to reference cell in another workbook.
There are three types of cell references in Excel: relative, absolute and mixed. When writing a formula for a single cell, you can go with any type. But if you intend to copy your formula to other cells, it is important that you use an appropriate address type because relative and absolute cell references behave differently when filled to other cells.
A relative reference is the one without the $ sign in the row and column coordinates, like A1 or A1:B10. By default, all cell addresses in Excel are relative.
When moved or copied across multiple cells, relative references change based on the relative position of rows and columns. So, if you want to repeat the same calculation across several columns or rows, you need to use relative cell references.
For example, to multiply numbers in column A by 5, you enter this formula in B2:
When copied from row 2 to row 3, the formula will change to:
For more information, please see Relative reference in Excel.
An absolute reference is the one with the dollar sign ($) in the row or column coordinates, like $A$1 or $A$1:$B$10.
An absolute cell reference remains unchanged when filling other cells with the same formula. Absolute addresses are especially useful when you want to perform multiple calculations with a value in a specific cell or when you need to copy a formula to other cells without changing references.
For example, to multiply the numbers in column A by the number in B2, you input the following formula in row 2, and then copy the formula down the column by dragging the fill handle:
The relative reference (A2) will change based on a relative position of a row where the formula is copied, while the absolute reference ($B$2) will always be locked on the same cell:
More details can be found in Absolute reference in Excel.
A mixed reference contains one relative and one absolute coordinate, like $A1 or A$1.
There may be many situations when only one coordinate, column or row, should be fixed.
For example, to multiply a column of numbers (column A) by 3 different numbers (B2, C2 and D2), you put the following formula in B3, and then copy it down and to the right:
In $A3, you lock the column coordinate because the formula should always multiply the original numbers in column A. The row coordinate is relative since it needs to change for other rows.
In B$2, you lock the row coordinate to tell Excel always to pick the multiplier in row 2. The column coordinate is relative because the multipliers are in 3 different columns and the formula should adjust accordingly.
As the result, all the calculations are performed with a single formula, which changes properly for each row and column where it is copied:
For real-life formula examples, please check out Mixed cell references in Excel.
To switch from a relative reference to absolute and vice versa, you can either type or delete the $ sign manually, or use the F4 shortcut:
Repeatedly hitting the F4 key switches the references in this order: A1 > $A$1 > A$1 > $A1.
In simple terms, a circular reference is the one that refers back to its own cell, directly or indirectly.
For example, if you put the below formula in cell A1, this would create a circular reference:
In most situations, circular references are a source of trouble and you should avoid using them whenever possible. In some rare case, however, they could be the only possible solution for a specific task.
The following tutorial explains how to find and remove circular references in Excel.
3-D reference refers to the same cell or range of cells on multiple worksheets.
For example, to find an average of values in cells A1 to A10 in Sheet1, Sheet2 and Sheet3, you can use the AVERAGE function with a 3d reference:
To make a formula with a 3d reference, here's what you need to do:
For more details, please see 3D reference in Excel.
Structured reference is a special term for including table and column names in a formula instead of cells addresses. Such references can only be used for referring to cells in Excel tables.
For example, to find an average of numbers in the Sales column of Table1, you can use this formula:
For more information, please see Structured refereces in Excel.
An individual cell or a range of cells in Excel can also be defined by name. For this, you simply select a cell(s), type a name into the Name Box, and press the Enter key.
Upon creating new names, you may wish to replace the existing cell references in your formulas with the defined names. Here's how:
To replace the references with defined names in all formulas on the active sheet, select any single blank cell.
As the result, the references in all or selected formulas will be updated to the corresponding names:
The detailed information on Excel names can be found in How to create and use a named range in Excel.
That's how you work with cell references in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents