7 Ways To Reference Another Sheet in Microsoft Excel

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
Basic sheet referencing formula
Basic sheet referencing formula

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
Populate spill range
Populate spill range

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.

Copy spill range data
Copy spill range data

Press Ctrl + C to copy the data.

Paste Special dialog box
Paste Special dialog box

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

Converted spill rang to normal data
Converted spill range to normal data

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
Example dataset for referencing open workbook sheets
Example dataset for referencing open workbook sheets

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
Example dataset for closed workbook referencing
Example dataset for closed workbook referencing

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:

Start formula with an equals sign
Start formula with an equals sign

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.

Select data with mouse
Select data with the mouse

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

Spill range created with sheet referencing
Spill range created with sheet referencing

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.

Copy source data range
Copy source data range

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.

Paste Special dialog box Paste Link
Paste Special dialog box Paste Link

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

Hit the Paste Link button.

Referred to another worksheet using Paste Link
Referred to another worksheet using Paste Link

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.

Name Manager
Name Manager

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.

New Name dialog box
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.

SUM formula with a Named Range
SUM formula with a Named Range

In A1, enter the following SUM formula:

=SUM(Total_Revenue)   
Paste Name dialog
Paste Name dialog

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.

Calculate SUM formula
Calculate SUM formula

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.

Start creating the formula with function syntax

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

Paste Name on different workbook
Paste Name on different workbook

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.

Excel has insereted the Named Range
Excel has insereted the Named Range

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.

Calculate SUM formula with Named Range
Calculate SUM formula with Named Range

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.

Sample dataset 1

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.

Enter array formula
Enter array formula

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.

Calculate and generate Spill Range
Calculate and generate Spill Range

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:

Sample dataset 2

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.

Destination worksheet
Destination worksheet

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.

Constructing VLOOKUP formula
Constructing VLOOKUP formula

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.

Get lookup data
Get lookup data

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.

Sample PivotTable
Sample PivotTable

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.

Get total revenue
Get total revenue

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.

Enter partial formula
Enter partial formula

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

=GETPIVOTDATA("Sum of Unit Price",
Populate the PivotTable reference
Populate the PivotTable reference

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.

Complete the formula
Complete the formula

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)
Reference to another PivotTable
Reference to another PivotTable

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:

VBA script 1
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.

Get destination
Get destination

Excel will ask you to choose the destination cell range.

Get input dataset
Get input dataset

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.

Imported data using sheet referencing with VBA
Imported data using sheet referencing with VBA

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃