How Do I Reference a Cell in Another Worksheet in Excel?

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Reference to cell in different worksheet.
Image Credit: Image courtesy of Microsoft

To retrieve a cell value with a formula in Excel 2013, you can use an A1-style reference or a named reference. An A1-style reference describes the cell at the intersection of the column (A) and the row (1). You can also name a cell and then reference the cell by the name, which is helpful when retrieving cell values from different worksheets or workbooks.

Advertisement

Retrieve a Cell Value

Video of the Day

Step 1

A1-style reference
Image Credit: Image courtesy of Microsoft

Retrieving Values Using an A1-Style Reference

Advertisement

Video of the Day

To retrieve a cell value from a different worksheet using an A1-style reference, type an equal sign to begin the formula and then navigate to the desired sheet by clicking the sheet's tab. Select the desired cell and Excel will insert the proper reference.

For example, if the desired cell value is stored in C12 on a sheet named Sheet 2, the resulting formula is ='Sheet 2'!C12.

Advertisement

Excel inserts the sheet name and the cell reference separated by an exclamation mark. If a sheet name includes a space, the sheet name must by enclosed by 'single quotes.' If a sheet name does not include a space, then single quotes are not required.

Step 2

Name a cell
Image Credit: Image courtesy of Microsoft

Retrieving Values Using a Named Reference

Advertisement

Advertisement

To retrieve a value from a different worksheet using a named reference, select the desired cell and then enter a friendly name into the Name Box, such as SalesTotal. After typing the name, be sure to press the Enter key on your keyboard. If you don't press the Enter key, and instead click a different cell or icon, Excel won't store the name.

Advertisement

Note: Excel has restrictions for names, for example, a name can't include spaces or conflict with an existing Excel name such as A1.

Step 3

Named reference
Image Credit: Image courtesy of Microsoft

Write Formula Using a Named Reference

Advertisement

To write the formula that retrieves the desired cell value, enter an equal sign and then type the name. The resulting formula is =SalesTotal.

Advertisement

Advertisement

resources