This Microsoft Excel tutorial will show you how to reference another sheet in Excel.

Cell referencing in Excel allows you to calculate data-related problems efficiently. Results update automatically as you change the input data of the referred cell ranges.

You might often need to reference cell ranges in another worksheet of the same workbook or a different workbook. However, pulling data from another sheet is a delicate skill that requires referencing external sheets in cells or formulas.

Follow along with the methods mentioned below to learn this indispensable Excel skill or brush up your skill if you already know the basics of sheet referencing.

📒 Read More: 8 Ways to Find External Links in Microsoft Excel

The Basics of Referencing Another Worksheet

The default way to create a reference to another worksheet in the same workbook is as outlined in the following syntax:

Sheet_name!Cell_address

For example, if you would like to import the data from cell range E1:E10 in Sheet1 to A1:A10 in Sheet7, you can enter the following cell range reference syntax in A1 of Sheet7:

  =Sheet1!E1:E10

Now, you must hit Enter to generate the values as a spill range.

If you delete the formula in A1 of Sheet7, Excel will remove the imported data.

So, if you’re not looking for a dynamic range feature, select the imported data in the destination worksheet.

Press Ctrl + C to copy the data.

Now, press Ctrl + Alt + V on the same cell range and choose Values in the Paste Special dialog box.

The dynamic spill range will be converted to hard-coded data.

Suppose, the source data worksheet is in another workbook. In this scenario, you can use the following syntax instead:

=[Workbook_name.XLSX]Sheet_name!Cell_address

Find above an example showing how this syntax would work with a real-world dataset.

However, if you haven’t opened the workbook on your PC, you can use the following worksheet referencing syntax:

='Drive_letter:\[Workbook_name.xlsx]Sheet_name'!Cell_address

The above screenshot shows how to use this syntax with real data.

Using the Sheet_name!Cell_address syntax for referencing another sheet can sometimes generate incorrect results due to incorrect sheet names or missing exclamation marks. To avoid any errors while creating an external sheet referencing you can also use the following visual method:

Go to the destination cell where you’d like to create a reference for another worksheet’s cell range.

Enter the equals (=) symbol to start the sheet referencing formula.

Now, click on the source worksheet in the same workbook. Then, select the cell range you want to import to the destination cell.

Hit Enter to complete importing data from the other worksheet.

You can also use this method to reference a cell range to another worksheet in a different workbook which is already opened in the PC.

If the source workbook isn’t open, open it before applying this method for sheet referencing.

Refer to Another Sheet Using Link Cells

If you wish to refer to another worksheet in the destination cell to display content from the source cell, you can use this method. This technique is highly useful for showing a summary of multiple worksheets in the first or the dashboard worksheet.

Go to the source worksheet and select the data you want to reference in the destination sheet.

Press Ctrl + C to copy the source data.

Navigate to the destination and press Ctrl + Alt + V to bring up the Paste Special dialog box.

Hit the Paste Link button.

Excel will populate the referred cell range with individual references to the source cells.

The Link Cells feature offers the following benefits:

• Automatically updates data in the destination cell ranges when the source data is updated.
• Eliminates all the chances of errors that might occur due to manual copying and pasting of updated data from the source worksheet.
• It also shows the source of the data for each cell in a range for data traceability purposes.

A downside of this method is it’s only available in Excel 2016 and later editions.

Refer to Another Sheet Using a Named Range

You can make formulas easy to read and error-free by using the Named Range feature of Excel. You can use the Name Manager tool to create one or more Named Ranges for important cell ranges in different worksheets.

When you write an Excel formula, instead of entering cell ranges, you can enter the Named Range of that cell range for easier referencing. Named Ranges are available across worksheets and workbooks.

In the Same Workbook

Firstly, you need to create a Named Range.

Select the source data in the worksheet of the same or different workbook.

Click on the Name Manager command inside the Defined Names block of the Formulas tab.

The Name Manager dialog box will open.

Click on the New tab to open the New Name dialog box.

Type a name for the selected range in the Name field. The Name text should not contain any blank space between two or more words. If you must separate words in the Name text for clarity, use the underscore symbol.

Click on the Scope drop-down and choose Workbook.

The Refers to field should already show the selected cell range.

Click OK to save the Defined Name you’ve just created.

Close the Name Manager dialog box as well.

Suppose, you’ve created the Named Range for D2:D10 in the worksheet Sheet1 (2). Now, you’d like to SUM the data in the Named Range in the destination cell A1 in Sheet1.

In A1, enter the following SUM formula:

=SUM(Total_Revenue)   

Suppose, there’s more than one Named Range and you don’t remember the one you want to refer to in the formula. Press F3 when you need to enter the Named Range in the formula syntax.

The Paste Name dialog box will open. It’ll show all the Named Ranges available in the current workbook. Select the one you want to enter using the mouse cursor and click OK to enter it.

Hit Enter to calculate the SUM formula.

In Another Workbook

If you wish to refer to another cell range in a worksheet of a different Excel file, ensure the source Excel file is open.

Now, go to the destination cell. Enter the equals (=) sign followed by the function syntax, arguments, etc.

When you need to enter the Named Range in the formula, go to the source worksheet where you created the Defined Range previously.

Excel will export the formula syntax you’ve created so far in the source worksheet of the alternative workbook.

Press F3 to show up the Paste Name dialog.

Select the Named Range from this box and click OK.

Now, go back to the destination worksheet and Excel will gain carry over the partially constructed formula there.

Complete the formula and hit Enter.

Excel shall calculate the formula if you’ve successfully entered all the mandatory arguments.

Refer to Sheet Using an Array Formula

Suppose, you’ve got a raw data worksheet containing downloaded data of unit price and unit costs of products from POS or inventory management tools.

You would like to create an array formula in another worksheet, probably a dashboard to show the revenue earned from the sales proceeds each day.

When you update the raw data worksheet the next day, revenue values will be updated automatically in the dashboard.

You can achieve this using an array formula with references to the raw data worksheet.

The source worksheet could look like the one shown in the above screenshot. The cell ranges of Unit Price and Units Sold columns will be referred in the destination worksheet.

In the destination cell, from which you want to create the spill range for the Total Revenue calculation, enter the following formula:

=[Workbook1.xlsx]Sheet1!$C$2:$C$10*[Workbook1.xlsx]Sheet1!$D$2:$D$10

You must adjust the formula arguments according to your own worksheet references.

Press Enter to calculate the Total Revenue values.

You just need to create this formula once.

Then next time you download and paste the Unit Price and Units Sold data in the referred worksheet’s designated cell ranges, Total Revenue values will update automatically.

Refer to Sheet in VLOOKUP

If you want to run the VLOOKUP function to find a value or text string in the lookup table situated in a different worksheet, you can do so by following these steps:

Your product or content database in Excel could look like the one shown in the above screenshot. This lookupo table is in the Sheet2 of the Workbook1.

Suppose, you now want to create a VLOOKUP function to fetch the Supplier Name for a product entered in the Enter Product ID cell. Refer to the sheet structure shown above. This is the Sheet7 in a different Excel workbook.

In B3, where you’d like to fetch the supplier’s name for a product, enter this formula:

=VLOOKUP(B1,[Workbook1.xlsx]Sheet2!$A$1:$F$11,6)

Don’t forget to customize the formula according to your own worksheets.

Enter a product ID in B1, like 101.

Hit Enter to calculate the VLOOKUP function.

Excel will retrieve the text string you need in B3.

Refer to a PivotTable in Anothet Sheet

If you need to refer to various fields of a PivotTable in another worksheet, you can use the GETPIVOTDATA function in Excel.

For example, you’ve created a PivotTable of Sum of Units Sold and Sum of Unit Price for various products listed in Row Labels using the above dataset.

Find above the outline of the PivotTable. This object is in Sheet1 of Workbook1.

Now, you’d like to create a dashboard in Sheet7 of the Referencesheet workbook where you want to show the grand total values for Sum of Units Sold or Sum of Unit Price.

Here, you’ll need to utilize your Excel skill of referencing to a PivotTable in another worksheet or workbook.

In the cell B2 of the destination worksheet, you can enter the following partial formula:

=GETPIVOTDATA("Sum of Unit Price",

Now, open the source worksheet and click on the first cell where you’ve populated the PivotTable, like E2 in the current exercise.

Excel will automatically create the necessary codes to refer to the PivotTable from Workbook1.

Navigate to the destination workbook again and complete the formula by closing it with a parenthesis.

=GETPIVOTDATA("Sum of Unit Price",[Workbook1.xlsx]Sheet1!$E$2)

Hit Enter to get the Total Revenue value from the referenced PivotTable in another workbook.

Reference Another Sheet Using Excel VBA

You can speed up the sheet referencing process using Excel VBA programmatic approach. This method also eliminates the chances of formula or sheet referencing errors.

Before moving further with the VBA scripts, learn below the technique to set up a VBA macro:

📒 Read More: How To Use The VBA Code You Find Online

The following script will let you import datasets from a different worksheet via external sheet referencing:

Sub ImportDatabyReferencing()
    Dim destRange As Range
    Dim inputRange As Range
    Dim spillRange As Range
    
    ' Step 1: Select Destination Cell Range
    On Error Resume Next
    Set destRange = Application.InputBox("Select the destination cell range:", Type:=8)
    On Error GoTo 0
    
    If destRange Is Nothing Then
        MsgBox "No destination range selected. Exiting."
        Exit Sub
    End If
    
    ' Step 2: Choose Input Dataset
    On Error Resume Next
    Set inputRange = Application.InputBox("Select the input dataset range:", Type:=8)
    On Error GoTo 0
    
    If inputRange Is Nothing Then
        MsgBox "No input dataset selected. Exiting."
        Exit Sub
    End If
    
    ' Step 3: Create Spill Range
    Set spillRange = destRange.Resize(inputRange.Rows.Count, inputRange.Columns.Count)
    spillRange.Value = inputRange.Value
    
    MsgBox "Data imported successfullyt!"
End Sub

After creating the VBA macro, press Alt + F8 to bring up the Macro dialog box.

Select the ImportDatabyReferencing macro and hit Run.

Excel will ask you to choose the destination cell range.

Then, you’ll see another input box so you can choose the reference cell range from another worksheet. Ensure, the workbook is open if the source is a different Excel file.

VBA macro will populate the dataset in the destination cell.

Conclusions

If you’re here, congratulations! You’ve acquired the required skill to solve Excel problems if your interviewer or employer asks you how to reference another sheet in Excel.

If the article helped you to learn external sheet referencing effortlessly, acknowledge it in the comment box. If you know a better method worth listing in this Excel tutorial, mention that in your comment.