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

In this Excel tutorial, you will learn various essential techniques to link sheets in Excel. You will learn to link sheets within a workbook using Excel features, functions, and VBA. We will also walk you through how to connect sheets within different workbooks.

While writing this article, I used Microsoft 365. However, all the topics covered here are usable in Excel 2021, 2019, 2016, 2013, 2010, and 2007.

Linking sheets in Excel is about establishing connections between different worksheets or workbooks. It is handy for tasks like combining information or making reports. We can combine data from various sources and organize it when we link sheets.

In the following overview image, we can see that clicking on any cell of the Link column will redirect us to the corresponding sheets.

Download Practice Workbooks

When Do You Need to Link Sheets in Excel?

You need to link sheets in Excel to put data from different parts of your workbook together. For example, when making reports or checking information within various sheets. It also helps you avoid copying data and changes in one sheet and update others in real-time. Sometimes, we work in a workbook, but we have to link sheets from a different workbook. Linking sheets across other workbooks enables us to integrate data from external sources.

How Many Methods Are Available to Link Sheets in Excel?

There are five methods available to link sheets in Excel. You will learn all the methods throughout the following context.

1. Use the HYPERLINK Function to Link Sheets

Using the HYPERLINK function is the most common way of linking sheets in Excel.

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

• Drag Fill Handle to cell C7.
• Click on C5.

As a result, the sheet New York will activate.

Click the image to get a better view

2. Link Sheets Using Cell Reference in a Formula

We can also link sheets using Cell References in a formula. However, we will only get the values using this method instead of redirecting to the source data.

• To begin, choose cell C5.
• Apply the formula below and press Enter.

• After that, 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

3. Use Name Box to Link Sheets in Excel

In this section, we will use the Name Box feature to link two Excel sheets. Like the previous method, we can only retrieve data here instead of redirecting to the source sheet.

• Select cell F13 in the New York sheet.
• After that, give a name to cell F13.

Likewise, we must insert names to 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.

• Now, choose cell C6 and input the following formula.

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

As a result, cell value will be updated if we make any changes to the sales sheets.

4. Link Sheets Using Paste Link Option

We can also link cell values with the help of the Paste Link option. Please follow the below instructions.

• Select cell C5.
• After that, go to Insert and click on Link.

Due to this, the Edit Hyperlink window will appear.

• Now, choose Place in This Document.
• Type display text and input cell reference.
• Select New York and press OK.

Likewise, follow the same procedure for C6 and C7 cells.

If we click on cell C7, the Los Angeles sheet will activate.

5. Run Excel VBA Code to Link Sheets

Throughout this context, you will see how we can use the Excel VBA code to link sheets. We will develop a sub-procedure and run to achieve the goal.

To begin, choose the intended sheet.

After that, press Alt+F11 to open the VBA editor.

The VBA Editor window will now open.

• Now, click on Insert followed by Module.
• Later, 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 we click on cell C5, the New York sheet will activate.

How Many Methods Are Available to Link Sheets in Different Workbooks in Excel?

There are several ways to link sheets within different workbooks. However, we will present two beneficial ways to connect sheets from external sources.

1. Use Excel Formulas to Link Sheets in Different Workbooks

We can form an Excel formula by combining the INDIRECT and CONCATENATE functions to link sheets in different workbooks. Please follow the below procedures.

• Open the intended workbook, in this case, Source Workbook.
• Select cell C5 of the destination workbook and apply the formula below.

• Drag Fill Handle to cell C7.

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

2. Link Sheets in Different Workbooks By Running VBA Code

Throughout this section, you will see how we can use the Excel VBA subroutine to link sheets. However, this time, we will link sheets with different workbooks.

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

Due to this, the VBA Editor window will appear.

• Now, click on Insert followed by Module.
• After that, 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

As a result, a dialog box will open up.

• Choose Source Workbook.xlsx and press OK.

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

What Are the Problems We May Face When Linking Large Data Between Excel Sheets?

Linking large amounts of data between sheets may decrease the workbook performance. Indeed, linking in sheets must make your workbook easier to manage operations. However, linking extensive data will slow your workflow. We recommend importing data if you are working with a large dataset.

Which Things You Have to Keep in Mind?

• Ensure to keep the provided workbooks open when going through each topic.
• 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

1. What is meant by a linking sheet?

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

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

Answer: 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.

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

Answer: We can automatically import data from one sheet to another in Excel. You must go to the sheet where you want to import the data. Next, click the Data tab, Get Data, and choose From Other Sources.

Conclusion

I hope the article has guided you with everything you need to know about linking sheets in Excel. Apply the HYPERLINK function, Name Box, and Paste Link when working in the same workbook. If you need to link sheets from external Excel files, combine the INDIRECT and CONCATENATE functions. You use Excel VBA Code whether working in the same or different workbooks. Do not forget to comment if you have any suggestions or queries.

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