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.
Retrieve a Cell Value
Video of the Day
Step 1
Retrieving Values Using an A1-Style Reference
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.
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
Retrieving Values Using a Named Reference
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.
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
Write Formula Using a Named Reference
To write the formula that retrieves the desired cell value, enter an equal sign and then type the name. The resulting formula is =SalesTotal.