How to Link Sheets in Excel? (Same and Different Workbooks)

In the following overview image, clicking on any cell of the Link column will redirect to the corresponding sheets.

Download the Practice Workbooks

Method 1 – Use the HYPERLINK Function to Link Sheets

• Select cell C5.
• Apply the following formula and press Enter.

• Drag the Fill Handle to cell C7.
• Click on C5 and you’ll go to the New York sheet.

Method 2 – Link Sheets Using a Cell Reference in a Formula

• Choose cell C5.
• Apply the formula below and press Enter.

• Drag the Fill Handle to C7.

• If we change the cell F13 value in sheet New York, the value of cell C5 will be updated.

Read More: Link Sheets in Excel with a Formula

Method 3 – Use the Name Box to Link Sheets in Excel

• Select cell F13 in the New York sheet.
• Give a name to cell F13.

• Name the F13 cells in the Boston and Los Angeles sheets.

Click the image to get a better view

• Select cell C5 and apply the formula below.

• Choose cell C6 and input the following formula.

• Go to cell C7, apply the equation below, and press Enter.

Method 4 – Link Sheets Using the Paste Link Option

• Select cell C5.
• Go to Insert and click on Link.

The Edit Hyperlink window will appear.

• Choose Place in This Document.
• Type in the display text and input the cell reference.
• Select New York from the sheet list and press OK.

• Follow the same procedure for cells C6 and C7.

• If you click on cell C7, the Los Angeles sheet will be active.

Method 5 – Run Excel VBA Code to Link Sheets

• Choose the sheet you want to make the links in.
• Press Alt + F11 to open the VBA editor.

• Click on Insert then on Module.
• Insert the following code and press F9 to run.

Sub LinkSheets()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim sheetName As String
   
    Set wb = ThisWorkbook
    Set rng = wb.Sheets("Excel VBA Internal").Range("C5:C7")
   
    For Each cell In rng
   
        sheetName = cell.Value
        Set ws = wb.Sheets(sheetName)
       
        If Not ws Is Nothing Then
            cell.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="'" & sheetName & "'!A1", TextToDisplay:=sheetName
        End If
   
    Next cell

End Sub

• If you click on cell C5, the New York sheet will activate.

How to Link Sheets in Different Workbooks in Excel

Method 1 – Use Excel Formulas to Link Sheets in Different Workbooks

• Open the Source Workbook.
• Select cell C5 of the destination workbook and apply the formula below.

• Drag the Fill Handle to cell C7.

• If we make any changes to the Source Workbook, values in the current workbook will be updated.

Method 2 – Link Sheets in Different Workbooks by Running VBA Code

• Navigate to the Developer tab.
• Click on Visual Basic.

The VBA Editor window will appear.

• Click on Insert and select Module.
• Insert the following code and press F9 to run.

Sub LinkCellsToExternalWorkbook()

    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim targetRange As Range
    Dim cell As Range
    Dim sheetName As String
    Dim sourceFilePath As Variant
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select Source Workbook"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm"
        .AllowMultiSelect = False
       
        If .Show = -1 Then
            sourceFilePath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
   
    Set sourceWorkbook = Workbooks.Open(sourceFilePath)
    Set targetWorkbook = ThisWorkbook
    Set targetSheet = targetWorkbook.Sheets("Excel VBA External")
    Set targetRange = targetSheet.Range("C5:C7")
   
    For Each cell In targetRange
        sheetName = cell.Value
        Set sourceSheet = sourceWorkbook.Sheets(sheetName)
        If Not sourceSheet Is Nothing Then
            targetSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="'" & sourceWorkbook.Name & "'!" & sourceSheet.Range("A1").Address, TextToDisplay:=sheetName
        End If
    Next cell
   
    ThisWorkbook.Activate

End Sub

A dialog box will open up.

• Choose Source Workbook.xlsx and press OK.

• If you click in cell C5 in the current workbook, the New York sheet in the Source Workbook will activate.

Potential Issues with Linking Sheets

Linking large amounts of data between sheets may decrease workbook performance. We recommend importing data if you are working with a large dataset.

Things to Keep in Mind

• Keep the workbooks open when going through each method.
• When applying Excel VBA to link sheets in another workbook, ensure it is not open.
• If you want to apply the first method to link externally, you must open the Source Workbook first.

Frequently Asked Questions

What is meant by a linking sheet?

A linking sheet refers to establishing connections between different worksheets in Excel. As a result, data can be shared and updated automatically between them.

How do I link data from Sheet1 to Sheet2 in Excel?

In Excel, you can use formulas to link data from Sheet1 to Sheet2. In Sheet2, select the cell where you want the linked data to appear. Next, type an equal sign (=) followed by Sheet1, an exclamation mark (!), and the cell reference in Sheet1.

How do I automatically import data from one sheet to another?

Go to the sheet where you want to import the data. Go to the Data tab, select Get Data, and choose From Other Sources.

Excel Link Sheets: Knowledge Hub

• Link Cell to Another Sheet
• Link Excel Data Across Multiple Sheets
• Link Sheets to a Master Sheet
• Link Data in Excel from One Sheet to Another
• Link a Table in Excel to Another Sheet (2 Easy Ways)
• Automatically Update One Worksheet from Another Sheet
• Transfer Data from One Excel Worksheet to Another Automatically
• Reference Cell in Another Excel Sheet Based on Cell Value
• Transfer Specific Data from One Worksheet to Another for Reports
• Linking Excel Sheets to a Summary Page
• Make Excel Look Like an Application

<< Go Back To Linking in Excel | Learn Excel