Excel 2019

Referencing other worksheets and workbooks

Excel can use references to cells and ranges that are in a different worksheets or workbooks. Referencing other worksheets or workbooks is called external cell reference. To refer to a cell in a different worksheet, precede the cell reference with the worksheet name followed by an exclamation sign !. See following example to understand how a formula uses a cell reference in a different worksheet.

A1 reference style:

=Sheet2!A1

R1C1 reference style

=Sheet2!R1C1

Note: you must enclose worksheet name in single quote if its name includes space:

='Sheet 2'!A1

To create an external cell reference that refer to a cell in a different workbook, precede the cell reference with the workbook name surrounded by square brackets, the sheet name, and an exclamation sign !, for example:

=[workbook1.xlsx]Sheet1!A1

You need to enclose workbook name (including brackets) and worksheet name in single quotation marks If the workbook name or sheet name in the reference includes one or more spaces, for example:

='[brain bell.xlsx]Sheet1'!A1

Note: Above examples will not work if the referenced workbook is closed or in a different folder. You must include the full path to the workbook reference if the referenced workbook is closed or in different location:

='D:\New Folder\[brain bell.xlsx]Sheet1'!A1

Summary

The syntax for the external cell reference to a different workbook:

'Full Path[Workbook Name]Worksheet Name'!Cell Reference

For different worksheet:

'Worksheet Name'!Cell Reference
Advertisement:
Advertisement: