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

Linking excel data across multiple sheets helps to save time by updating all the values if one value is changed. If you are looking for a way to link data across multiple sheets in Excel then this article will be helpful for you. The focus of this article is to explain how to link Excel data across multiple sheets.

Download Practice Workbook

7 Easy Ways to Link Excel Data Across Multiple Sheets

Here, I will explain how to link Excel data across multiple sheets in 7 easy and effective ways. I have taken the following dataset to explain this article. The dataset contains the Month and Sales columns.

For this article, I have taken 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.

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

In this method, I will explain how to link Excel data across multiple sheets using the Paste Special option. Here, I will link the Total Sales of every sheet to a new sheet to show the Total Sales for every State together.

Let’s see the steps.

Steps:

• Firstly, select the cell from where you want to link the data. Here, I selected cell C11 because it contains the Total Sales for Texas.
• Secondly, Copy the cell by pressing CTRL+C. This is the keyboard shortcut to copy.

• Thirdly, select the cell where you want to link the data. Here, I selected cell C5 from the sheet named Sales Overview for Texas.
• After that, Right-click on the cell.
• Next, select Paste Special.

Here, a dialog box named Paste Special will appear.

• Now, select Paste Link.

Now, you will see that you have pasted the data with a link. That means, that if you change the source data it will be updated automatically.

• Finally, copy the other values using the Paste Special option in the same way.

Read More: How to Link Excel Sheets to Another Sheet (5 Ways)

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

In this method, I will show you how you can apply sheet reference to link Excel data across multiple sheets. Let’s see the steps.

Steps:

• Firstly, select the cell where you want to link the data. Here, I selected cell C5.
• Secondly, in cell C5 write the following formula.

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.

• Finally, press ENTER and you will get your linked data.

• Now, link the other data in the same way. Here, I have linked mine.

Read More: How to Link Sheets in Excel with a Formula (4 Methods)

3. Employing Link Option to Insert Hyperlink

In this method, I will show you how to link data across multiple sheets in Excel by using the Link option. Here, you will not get the data of the linked cell. Rather you will get a link that will help you to jump to that particular cell that carries the data. I will link the Total Sales for every State in the following table.

Let’s see how it is done.

Steps:

• Firstly, select the cell where you want to insert the link. Here, I selected cell C5 because I want to insert a link for Total Sales for Texas in this cell.
• Secondly, Right-click on the cell.
• Thirdly, select Link.

Now, a dialog box named Insert Hyperlink will appear.

• Firstly, select Place in This Document from the Link to section.
• Secondly, select the sheet that has the data you want to link. Here, I selected the sheet named Sales Overview for Texas.
• Thirdly, select the cell that carries the data. Here, I selected cell C11 because this cell carries the Total Sales.
• Next, type the text you want to display in the cell. Here, I wrote Total Sales for Texas.
• Finally, select OK to insert the link.

Here, you will see the link to that particular cell has been inserted into your selected cell.

• After that, insert the other links in the same way.

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

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

Read More: How to Link Data from One Spreadsheet to Another in Excel

4. Applying HYPERLINK Function to Link Data

For this method, I will use the HYPERLINK function to link Excel data across multiple sheets. Here, you will get a link that will help you to go to that particular cell that carries the data. Let’s see the steps.

Steps:

• Firstly, select the cell where you want to insert the link. Here, I selected cell C5.
• Secondly, in cell C5 write the following formula.

Now, in the HYPERLINK function, I 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.

• Thirdly, press ENTER, and you will get the link. 

• Now, 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, I clicked on Total Sales for Virginia.

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

Read More: How to Link Sheets to a Master Sheet in Excel (5 Ways)

Similar Readings

• How to Link Two Workbooks in Excel (5 Methods)
• Reference from Another Excel Workbook without Opening (5 Examples)
• Transfer Specific Data from One Worksheet to Another for Reports
• How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)
• Reference Cell in Another Excel Sheet Based on Cell Value

5. Using Named Range to Link Data Across Multiple Sheets

In this method, I will show you how to link Excel data across multiple sheets by using a named range. To explain this method, I have taken the following dataset. Here, I will show the Sales for every Month for each State in the same sheet by linking the data.

Let me show you the steps.

Steps:

To begin with, will name the ranges.

• Firstly, select the data range you want to link.
• Secondly, give this range a name. Here, I named mine as Sales_Texas. This name will always refer to this range.

• After that, select the second range of data that you want to link.
• Next, name the range as you want. Here, I named mine as Sales_Virginia.

• Now, select the third range of data that you want to link.
• Next, name the range as you want. Here, I named mine as Sales_Florida.

At this point, I will link the data.

• Firstly, select the cell from where you want to start the linked range. Here, I selected cell C6.
• Secondly, in that cell write the following formula.

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

• Thirdly, press ENTER to get the linked range.

Now, I will link the second set of data.

• Firstly, select the cell from where you want to start the linked range. Here, I selected cell D6.
• Secondly, in that cell write the following formula.

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

• Thirdly, press ENTER and you will get the linked range of data.

Now, I will link my third set of data.

• Firstly, select the cell from where you want to start the linked range. Here, I selected cell E6.
• Secondly, in that cell write the following formula.

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

• Thirdly, press ENTER and you will get the linked range of data.

Read More: How to Link Two Sheets in Excel (3 Ways)

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

In this method, I will use a 3D formula to link Excel data across multiple sheets. Here, I will 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.

Let’s see the steps.

Steps:

• Firstly, select the cell where you want to link the data. Here, I selected cell C5.
• Secondly, in cell C5 write the following formula.

To write this formula, 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.

• Thirdly, press ENTER to get the result.

• After that, drag the Fill Handle to copy the formula.

Finally, you can see I have copied the formula to all the cells.

Read More: How to Link Cell to Another Sheet in Excel (7 Methods)

7. Applying Consolidate Command to Link Data Across Multiple Sheets

Here, I will explain how you can apply the Consolidate command to link Excel data across multiple sheets. Let’s see the steps.

Steps:

• Firstly, select the cell from where you want to link your data range. Here, I selected cell C5.
• Secondly. go to the Data tab.
• Thirdly, select Consolidate.

Here, a dialog box named Consolidate will appear.

• Firstly, select the drop-down option for Function.
• Secondly, select the function you want. Here, I selected the Sum function.

• Thirdly, select the marked button to select the Reference.

• After that, select the range for Reference.
• Next, select the marked button to add the Reference.

• Now, select Add.

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

• Now, select the marked button to select the second Reference.

• After that, select the range for Reference.
• Next, select the marked button to add the Reference.

• Finally, select Add.

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

• After that, select the marked button to select the third Reference.

• Next, select the range for Reference.
• Then, select the marked button to add the Reference.

• Now, select Add.

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

• After that, check the Create links to source data option
• Next, select OK.

Finally, you will see that you have created your consolidated data that are linked to the source data.

Read More: How to Link Data in Excel from One Sheet to Another (4 Ways)

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

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

Conclusion

In this article, I tried to cover how to link Excel data across multiple sheets. Here, I explained 7 easy and efficient methods of doing it. I hope this was helpful for you. To get more articles like this visit ExcelDemy. Last but not the least, if you have any suggestions or any other feedback let me know in the comment below.

Related Articles

• Create Reference with Another Workbook in Excel (5 Ways)
• Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)
• How to Link Files in Excel (5 Different Approaches)
• Link Excel Workbooks for Automatic Update (5 Methods)
• How to Link Word Document to Excel (2 Easy Methods)
• Link Excel Workbooks (4 Effective Methods)