How to Link Excel Data Across Multiple Sheets (7 Easy Ways)

Let’s consider the following dataset to explain this article, which contains the Month and Sales columns.

How to Link Excel Data Across Multiple Sheets

For this article, we have 3 sheets. These sheets contain the Sales Overview of a particular State. The first sheet contains the Sales Overview for Texas.

The second sheet contains the Sales Overview for Virginia.

And, the third sheet contains the Sales Overview for Florida.

Dataset to Link Excel Data Across Multiple Sheets


Method 1 – Using Paste Special Option to Link Excel Data Across Multiple Sheets

Here, we will link the Total Sales of every sheet to a new sheet to show the Total Sales for every State together.

Using Pase Special Option to Link Excel Data Across Multiple Sheets

Steps:

  • Select the cell from where you want to link the data. We selected cell C11 because it contains the Total Sales for Texas.
  • Copy the cell by pressing Ctrl + C.

  • Select the cell where you want to link the data. We selected cell C5 from the sheet named Sales Overview for Texas.
  • Right-click on the cell.
  • Select Paste Special.

  • A dialog box named Paste Special will appear. Select Paste Link.
  • Click on OK.

Paste Special Dialog Box to Link Data Across Multiple Sheets in Excel

  • This pastes the data with a link. If you change the source data it will be updated automatically.

  • Copy the other values using the Paste Special option in the same way.

Read More: How to Link Two Sheets in Excel


Method 2 – Applying Sheet Reference to Link Data Across Multiple Sheets in Excel

Steps:

  • Select the cell where you want to link the data. We selected cell C5.
  • Copy the following formula into it:
='Sales Overview for Texas'!C11

Applying Sheet Reference to Link Data Across Multiple Sheets in Excel

Here, I have used the cell sheet reference to link the data. This formula will return the data that is in cell C11 of the sheet named Sales Overview for Texas.

  • Press Enter and you will get your linked data.

  • Repeat the process for the other sheets.

Read More: How to Link Sheets in Excel with a Formula


Method 3 – Using Link Option to Insert Hyperlink

Let’s put the hyperlinks to the Total Sales for every State in the following table.

Employing Link Option to Insert Hyperlink

Steps:

  • Select the cell where you want to insert the link. We selected cell C5.
  • Right-click on the cell.
  • Select Link.

Using Link Option to Link Excel Data Across Multiple Sheets

  • A dialog box named Insert Hyperlink will appear.
  • Select Place in This Document from the Link to section.
  • Choose the sheet that has the data you want to link. We used the sheet named Sales Overview for Texas.
  • Select the cell that carries the data. We selected cell C11 because this cell carries the Total Sales.
  • Type the text you want to display in the cell. We put Total Sales for Texas.
  • Select OK to insert the link.

Insert Hyperlink Dialog Box to Link Data Across Multiple Sheets in Excel

You will see the link to that particular cell has been inserted into your selected cell.

  • Repeat for other links in the same way.

Now, you can click on any link to see that particular data. Here, we clicked on Total Sales for Texas.

You can see that the link leads to the linked cell of that particular sheet that carries the Total Sales for Texas.

Read More: How to Link Cell to Another Sheet in Excel


Method 4 – Applying HYPERLINK Function to Link Data

Steps:

  • Select the cell where you want to insert the link, such as C5.
  • Copy the following formula into it:
=HYPERLINK("#'Sales Overview for Texas'!C11","Total Sales for Texas")

Applying HYPERLINK Function to Link Data

Now, in the HYPERLINK function, we selected “#’Sales Overview for Texas’!C11” as link_location, and “Total Sales for Texas” as friendly_name. The function will return a link that will directly lead to the selected location which is cell C11 of the sheet named Sales Overview for Texas.

  • Press Enter, and you will get the link. 

Using HYPERLINK Function to Link Data Across Multiple Sheets in Excel

  • Insert the other links in the same way.

After that, you can select any link and then click on that link to see that particular data. Here, we clicked on Total Sales for Virginia.

You can see that the link leads to the linked cell of that particular sheet that carries the Total Sales for Virginia.


Method 5 – Using Named Range to Link Data Across Multiple Sheets

Here’s a dataset with the Sales for every Month for each State in the same sheet, which we will fill by linking the data.

Using Named Range to Link Data Across Multiple Sheets

Steps:

  • Select the data range you want to link.
  • Give this range a name. We named the first one from the Texas worksheet as Sales_Texas. This name will always refer to this range.

  • Repeat for other sheets and names. For the sheet with sales in Virginia, we used Sales_Virginia.

Naming Range to Link Data Across Multiple Sheets in Excel

  • For Florida sales, we put Sales_Florida.

  • Select the cell from where you want to start the linked range. We selected cell C6.
  • In that cell, input the following formula:
=Sales_Texas

Writing Formula wilth Named Range to Link Data Across Multiple Sheets in Excel

The formula will return the data range named Sales_Texas.

  • Press Enter to get the linked range.

  • Select the next cell to link data, C6.
  • Input the following formula:
=Sales_Virginia

Here, the formula will return the data range named Sales_Virginia.

  • Press Enter and you will get the linked range.

  • Select cell E6.
  • Copy the following into it:
=Sales_Florida

Here, the formula will return the data range named Sales_Florida.

  • Press Enter and you will fill the table.


Method 6 – Use of 3D Formula to Link Excel Data Across Multiple Sheets

Let’s add the Sales for every Month from three different sheets and show them on another sheet. But, to use this type of formula you have to have data in the same cell on every sheet.

Use of 3D Formula to Link Excel Data Across Multiple Sheets

Steps:

  • Select the cell where you want to link the data. We selected cell C5.
  • Input the following formula:
=SUM('Sales Overview for Texas:Sales Overview for Florida'!C5)

To write this formula manually, in the SUM function select the cell that contains the data you want to sum from the first sheet then press and hold the SHIFT key while selecting the last sheet. This formula will return the summation of the values in cell C5 from the sheet named Sales Overview for Texas to the sheet named Sales Overview for Florida.

  • Press Enter to get the result.

  • Drag the Fill Handle to copy the formula.

Dragging Fill Handle to Copy Formula to Link Data Across Multiple Sheets in Excel

  • Excel has copied the formula to all the cells.


Method 7 – Applying Consolidate Command to Link Data Across Multiple Sheets

Steps:

  • Select the cell from where you want to link your data range. Here, we selected cell C5.
  • Go to the Data tab.
  • Select Consolidate.

Applying Consolidate Command to Link Data Across Multiple Sheets

  • A dialog box named Consolidate will appear.

  • Select the drop-down option for Function.
  • Choose the function you want. We’ll use the Sum function.

Selecting Function in Consolidate Dialog Box to Link Excel Data Across Multiple Sheets

  • Select the up arrow in the Reference box to select the Reference.

  • Select the range for Reference.
  • Click on the arrow at the end of the box to add the Reference.

Adding Reference to Link Excel Data Across Multiple Sheets

  • Select Add.

Here, you will see your selected range is added to the All references.

  • Repeat to select the second Reference.

  • Select the next range for Reference.
  • Add the Reference with the arrow button at the end of the box.

  • Select Add.

Now, you will see your selected second range is added to the All references.

  • Repeat for the third Reference.

  • Select the range for the Reference and confirm.

  • Click on Add.

Here, you will see all the selected ranges are added to the All references.

  • Check the Create links to source data option.
  • Select OK.

  • You have created your consolidated data that are linked to the source data.


Things to Remember

  • Whenever using the 3D formula it should be kept in mind that all the sheets must contain the value in the same cells.
  • If you are using the Consolidate command to link the range then the data must be of the same type and they have to be in the same layout.

Practice Section

We have provided a practice sheet for you to practice how to link Excel data across multiple sheets.


Download Practice Workbook


Related Articles


<< Go Back To Excel Link Sheets | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo