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.

## Reference Cell/Range on Another Sheet in Same Workbook

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 – .

### How to Reference One Cell

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

### How to Reference a Cell Range

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.

### Point-and-Click Method to Reference Cell/Range

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:

- Select the cell on the worksheet where you want to create the formula that will reference a cell or cell range on another worksheet in the same workbook.
- Begin entering the formula. For instance, if you want to sum values on another sheet, type = (an equal sign ) to start the formula.
- With the formula still active (you should see the blinking cursor in the cell where you started the formula), click the tab of the worksheet that contains the cell or cell range you want to reference.
- Select the cell or the cell range you want to reference on the other worksheet. Excel will automatically insert the correct reference into your formula. The reference will include the worksheet name followed by an exclamation mark (!) and the cell or cell range address, such as =Sheet2!B5.
- Finish typing your formula as needed. For example, if you are summing values, your formula might look like =SUM(Sheet2!A1:A10).
- Once the formula is complete, press the Enter key. Excel will return to the original worksheet and display the formula’s result. The cell or cell range reference will be visible on the formula bar.

*Example #1: Reference a Cell Range on Another Single Sheet*

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:

- Select cell A1 on the ‘Summary’ worksheet and begin to type in the formula by typing =SUM( as shown below:

- With the cursor still blinking in cell A1, click the ‘Sales’ worksheet tab.
- Select the cell range A1:A15 on the ‘Sales’ worksheet.

Excel creates the cell range reference on the formula bar:

- Press the Enter key.

Excel returns to the ‘Summary’ worksheet and displays the result of the formula in cell A1.

*Notes*

- Excel will enclose the worksheet name in single quotes if it contains spaces or special characters such as #, !, @, and -. For example, =SUM(‘Sales Data’!A1:A15).
- The reference created by Excel will be relative by default, meaning it changes when you copy the formula. If you want to make it absolute so it doesn’t change when you copy the formula, you can manually add dollar signs ($). For example, =SUM(Sales!$A$1:$A$15).

*Example #2: Reference Cells on Other Multiple Sheets*

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:

- Select cell A1 on the ‘Summary’ worksheet and begin to type in the formula by typing =SUM( as shown below:

- With the cursor still blinking in cell A1, click the ‘Sales-Qtr1’ worksheet tab.
- Select the cell range A1:A15 on the ‘Sales-Qtr1’ worksheet.

Excel creates the cell range reference on the formula bar:

- Type a comma after the reference on the formula bar:

- Click the tab of the ‘Sales-Qtr2’ worksheet and select the cell range A1:A7.

Excel creates the second cell range reference on the formula bar:

- Press the Enter key.

Excel returns to the ‘Summary’ worksheet and displays the result of the formula in cell A1.

## Reference Cell / Range in Another Workbook

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.

### Create an External Reference to an Open Workbook

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.

*Reference One Cell in Another Open Workbook*

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:

- ='[Annual Reports.xlsx]ImportedProducts’!A1
- ='[AnnualReports.xlsx]Imported Products’!A1
- ='[Annual Reports.xlsx]Imported Products’!A1

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.

### Point-and-Click Method to Reference Cell / Cell Range In 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:

- Select the cell on the worksheet where you want to create the formula that will reference a cell or cell range in another open workbook.
- Begin entering the formula. For instance, to sum values in another open workbook, type = (an equal sign ) to start the formula.
- With the formula still active (you should see the blinking cursor in the cell where you started the formula), activate the workbook with the sheet that contains the cell or cell range you want to reference.
- Click the tab of the worksheet with the cell or cell range you want to reference.
- Select the cell or the cell range you want to reference on the other worksheet. Excel will automatically insert the correct reference into your formula. The reference will include the workbook name, the worksheet name, an exclamation mark (!), and the cell or the cell range address, such as =Sheet2!B5.
- Finish typing your formula as needed. For example, if you are summing values, your formula might look like =SUM(‘[Annual Sales]Sheet2’
**!**A1:A10). - Once the formula is complete, press the Enter key. Excel will return to the original workbook and worksheet and display the formula’s result. The cell or cell range reference will be visible on the formula bar.

*Example of Creating an External Reference*

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:

- Select cell A1 on the current worksheet in the ‘Sales.xlsx’ workbook and begin to type in the formula by typing =SUM( as shown below:

- Activate the workbook ‘Regional Sales.xlsx,’ click the tab of the ‘Electronics’ worksheet, and select the cell range A1:A10.

- Press the Enter key.

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.

### How to Reference a Cell or Cell Range In A Closed Workbook

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:

- File path – the folder in which the external workbook is stored.
- Workbook name – the name of the external workbook. The name must include the file extension.
- Worksheet name – the name of the worksheet containing the cell or cell range you want to reference.
- Cell or cell range address – the absolute reference of the cell or cell range you are referencing.

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.

Also read: 3D Reference in Excel – What is it and How to Use it?

## Effect on External References of Renaming/ Moving External Workbooks

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.

### How to Reference a Named Range

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:

- Select the cell or cell range you want to name.
- On the Formulas tab, click Defined Name on the Defined Named group.

- In the Name box of the New Name dialog box that appears, type a name you want to give to the selected cell or cell range. In this case, I have typed in ‘AnnualSales.’ Choose ‘Workbook’ on the ‘Scope’ drop-down menu to make the name available throughout the workbook. You can choose a sheet to make the name available only within that worksheet.

*Note: A defined name cannot contain spaces.*

- Ensure the range in the ‘Refers to’ box near the bottom of the dialog box is correct.
- Click OK.

*Notes*

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.’

### How to Use Defined Names in Internal References

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.

### How to Use Defined Names in External References

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.

### How to Use the Paste Name Dialog Box to Reference Named Ranges

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:

- Select the cell where you want to get the sum of the named range.
- Begin to type in the formula by typing =SUM( as shown below:

- Press F3 on the keyboard to open the Paste Name dialog box. The Paste Name dialog box displays the list of all workbook-level names you have defined in your workbook.

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.

- Double-click the name you want to insert, in this case, ‘AnnualSales.’

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.

- Press the Enter key.

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:**