When you reference a cell or range of cells in another Excel sheet, you create a formula that points to a cell or range of cells in another sheet.
This referencing enables you to consolidate data, perform calculations across multiple sheets, and maintain dynamic links that automatically update when the referenced data changes.
In this tutorial, I will show you how to reference a cell or a range of cells on another sheet within the same workbook or another workbook.
Table of Contents
You create an internal reference when you reference a cell or range of cells in another sheet within the same workbook.
The format or syntax of the internal reference must include the sheet name and the cell or cell range address.
The syntax will also be determined by whether the sheet name has spaces or special characters such as #, !, @, and – .
To reference a cell in another sheet in the same workbook, you use a formula with the following format or syntax:
=SheetName!CellAddress
Note: The sheet name is followed by an exclamation mark and the cell address.
For instance, to reference cell A1 on a sheet named ‘Products,’ you would use the formula below:
=Products!A1
If the sheet name contains spaces or special characters such as #, !, @, and -, enclose the sheet name in single quotes. For example, for a sheet named ‘Imported Products,’ you would use the formula below:
='Imported Products'!A1
To reference a cell range in another sheet within the same workbook, you use a formula with the following syntax:
=SheetName!FirstCellAddress:LastCellAddress
Note: The sheet name is followed by an exclamation mark and the cell range address.
For example, to reference the cell range A1:C10 on a sheet named ‘Products,’ you would use the formula below:
=Products!A1:C10
If the sheet name contains spaces or special characters such as #, !, @, and -, enclose the sheet name in single quotes. For instance, to reference cell A1 on a sheet named ‘Imported Products,’ you would use the formula below:
='Imported Products'!A1:C10
Note: Excel will automatically update the reference if you rename the worksheet.
Now that you understand the syntax of internal cell references, you can enter the references manually. However, this method is time-consuming and prone to errors.
In the next section, I will show you the point-and-click method, a better way of referencing a cell or cell range in another sheet within the same workbook.
The point-and-click method for referencing cells in another worksheet allows you to select cells directly. It lets Excel create the cell or cell range reference automatically, ensuring accuracy and saving time.
Here is how to use the point-and-click method:
Suppose you have a workbook with two worksheets named ‘Summary’ and ‘Sales,’ and you have data in the cell range A1:A15 of the ‘Sales’ worksheet and want to get the sum of these values in cell A1 of the ‘Summary’ worksheet.
Here’s how to do it:
Excel creates the cell range reference on the formula bar:
Excel returns to the ‘Summary’ worksheet and displays the result of the formula in cell A1.
Suppose you have a workbook with three worksheets: ‘Summary,’ ‘Sales-Qtr1’, and ‘Sales-Qtr2’.
You have data in the cell range A1:A15 on the ‘Sales-Qtr1’ worksheet and the cell range A1:A7 on the ‘Sales-Qtr2’ worksheet, and you want to get the sum of these values in cell A1 of the ‘Summary’ worksheet.
Here’s how to do it:
Excel creates the cell range reference on the formula bar:
Excel creates the second cell range reference on the formula bar:
Excel returns to the ‘Summary’ worksheet and displays the result of the formula in cell A1.
You create an external reference when you reference a cell or range of cells in another workbook.
The format or syntax of the external reference depends on whether the other workbook is open and whether the workbook’s name contains spaces or special characters such as hyphens and underscores.
When you reference a cell or a range of cells in another open workbook, you must specify the workbook name, the worksheet name, and the cell or cell range address.
To reference a cell in another workbook, you use a formula with the following format or syntax:
=[WorkbookName]SheetName!CellAddress
Note: The workbook name enclosed in square brackets is followed by the sheet name, an exclamation mark, and the cell address.
For example, to reference cell A1 on the ‘Products’ sheet in the ‘Reports.xlsx’ workbook, use the formula below.:
=[Reports.xlsx]Products!A1
If the workbook or sheet name contains spaces or special characters such as #, !, @, and -, you should enclose the workbook and sheet names in single quotes. For instance, to refer to cell A1 on a sheet named ‘Imported Products’ in a workbook named ‘Annual Reports.xlsx,’ you would use the following formula:
='[Annual Reports.xlsx]Imported Products'!A1
The following are examples of external cell references when the workbook name, the sheet name, or both have spaces:
Note: Excel will automatically update the reference when you reference a cell or cell range on a sheet in an open external workbook and then rename the worksheet. However, the reference breaks down if you rename the workbook or move it to a different folder.
Now that you understand the format of external references, you can enter the formulas manually, but this method is time-consuming and prone to errors.
In the next section, I will show you the point-and-click method, a better way of referencing a cell or cell range in another open workbook.
The point-and-click method for referencing cells in another open workbook allows you to select cells directly, enabling Excel to create the external reference automatically.
Here is how to use the point-and-click method:
Suppose you have numeric values in cells A1:A10 on a worksheet named ‘Electronics’ in a workbook called ‘Regional Sales.xlsx,’ and you want to obtain the sum of these values in cell A1 of the current worksheet in a workbook named ‘Sales.xlsx.’
Here’s how to do it:
Excel returns to the current worksheet in the ‘Sales.xlsx’ workbook and displays the result of the formula in cell A1.
When you select cell A1, you can see the external reference created by Excel on the formula bar. Notice that the reference has absolute references.
Excel always creates external references with absolute references so that they do not change when you copy the formula. If you want to make the references relative so that they change when you copy them, you can manually remove the dollar signs ($) from the references.
When referencing a cell or range of cells in another open workbook, you only need to specify the workbook name, the worksheet name, and the cell or cell range address. However, specify the file path if you want to reference a cell or cell range in a closed workbook.
The following is an external reference to the cell range A1:A10 on the ‘Electronics’ worksheet in the ‘Regional Sales.xlsx’ workbook:
=SUM('C:\Sales Reports\[Regional Sales.xlsx]Electronics'!$A$1:$A$10)
Note that since the workbook is closed, the reference also shows the location of the workbook.
An external reference to a closed workbook has the following parts:
Note: If you create an external reference to another open workbook and then close the workbook, Excel automatically updates the reference to include the file path.
If you create an external reference to an external workbook and then rename or move the workbook to another location, the link breaks.
You will notice this when you close and reopen the workbook with the external reference, and Excel displays a warning message box shown below:
When you click Update, a yellow ribbon with the button Manage Workbook Links appears at the top of the Excel window just below the Excel Ribbon.
When you click the Manage Workbook Links button, a Workbook Links pane appears on the right of the Excel window.
If you want to break the link, click the ‘Break all’ option at the top of the pane. If you want to keep the workbooks linked, click the button with the ellipses icon next to the name of the external workbook, and click the ‘Change source’ option on the list that appears:
Excel opens the ‘Change source’ feature, which lets you specify the new file location or point to the renamed workbook and update the link.
You can assign a name to a cell or a cell range in Excel. The cell or cell range becomes a named range. You can then conveniently reference the named range instead of using specific cell or cell range addresses.
Before using named ranges in your internal or external references, you must define them. Here’s how to create a named range:
Note: A defined name cannot contain spaces.
A workbook-level name is available throughout the entire workbook, and therefore, you can use it on any sheet or formula within the workbook. For instance, you can use the name ‘AnnualSales’ we defined above in formulas on any worksheet in the workbook.
A worksheet-level name is available only within that particular worksheet. If you try to use it in another worksheet, it won’t be recognized unless you fully qualify it. For instance, if you define ‘Expenses’ on Sheet1, you can only use it on Sheet1. To use it on Sheet2, you must qualify it with the sheet name, like ‘Sheet1!Expenses.’
You can use a workbook-level name on any sheet in the workbook by using the defined name directly. For instance, if you want to sum the values in the ‘AnnualSales’ named range, you can use the formula below:
=SUM(AnnualSales)
If you have created a name at the worksheet level, you can use it only on the sheet where you created it. If you want to use it on another sheet, you must qualify the name with the sheet on which you created it.
For instance, if you defined a worksheet-level name ‘AnnualSales’ on Sheet1 and want to use it on Sheet2, you would use the formula below:
=SUM(Sheet2!AnnualSales)
If the sheet name contains spaces or special characters such as @, !, and -, you should enclose it in single quotes.
If you want to reference a named range in an open or closed external workbook, specify the workbook name and then the defined name; however, for a closed workbook, you must include the file path.
For instance, if you have an external open workbook named ‘Sales.xlsx’ with the ‘AnnualSales’ named range, you can use the formula below to get the sum of the values in the named range from the current workbook:
=SUM(Sales.xlsx!AnnualSales)
If the workbook name contains spaces or special characters such as hyphens and underscores, you should enclose it in single quotes.
To create an external reference to a worksheet-level named range, specify the workbook’s name and the worksheet.
The following is an example of an external reference to a sheet-level named range:
=SUM('[Regional Sales.xlsx]Sheet2'!AnnualSales)
If you want to create an external reference to a named range in a closed workbook, specify the file path, the workbook name, and the named range. Below is an example of an external reference to a named range in a closed workbook:
=SUM('C:\Sales Reports\Regional Sales.xlsx'!AnnualSales)
Note: If you create an external reference to a named range in an open workbook and then close the workbook, Excel automatically updates the reference to include the file path.
The Paste Name dialog box in Excel can enable you to insert named ranges in your formulas quickly. The dialog box is particularly helpful if your workbook contains many named ranges.
Suppose you want to use the ‘AnnualSales’ named range in a formula to sum the values in the range. Here’s how to do it:
Note: The ‘Paste Name’ dialog box only shows names defined at the workbook level, not names defined at the worksheet level. Worksheet-level names are not visible or accessible from other worksheets, so they do not appear on the Paste Name dialog box.
The step above inserts the name you have double-clicked into the formula. You can continue working on the formula. In this case, we type in the closing parenthesis to end the formula.
Excel displays the sum of the named range in the cell with the formula.
Note: To use a named range in an external workbook in a formula, you must go to the external workbook, check the name using the Paste Name dialog box, and type the named range in the formula in the correct format.
In this tutorial, I showed you how to reference a cell or a cell range on another sheet within the same or external workbook. I hope you found the tutorial helpful.
Other Excel articles you may also like:
I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.
