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

Get FREE Advanced Excel Exercises with Solutions!

We can save a lot of time, minimize error rate, and improve data integrity in Excel if we can properly Link Sheets. This article will cover the methods to Link Sheets in Excel with a Formula.

To help you understand better, I’m going to use a sample dataset as an example. The following dataset represents the Salesman, Product, and Net Sales of a company.

Link Sheets in Excel with a Formula


How to Link Sheets in Excel with a Formula: 4 Effective Methods

1. Create a Link Formula in Excel to Link Sheets

A very simple way to Link Sheets in Excel is by creating a link formula. It can be used to link sheets in the same workbook or sheets in other workbooks.

1.1 Same Workbook

If you have missing data in your worksheet, you can easily bring that back from another sheet by linking it to that sheet. Here, we have missing data in the link formula1 sheet and we’ll bring that back from the link formula2 sheet. Follow the steps below to find out how to do it.

Create a Link Formula in Excel to Link Sheets

STEPS:

  • First of all, select cell C7 of link formula1 sheet and type the formula:
='link formula2'!C7

Create a Link Formula in Excel to Link Sheets

  • Next, press Enter, and it’ll return the cell value from the link formula2 sheet.

Create a Link Formula in Excel to Link Sheets

  • Then, use the AutoFill tool to fill up the cell on the right side.
  • Here, you should check Fill Without Formatting from the AutoFill Options to keep the format as it’s in the source sheet.

Create a Link Formula in Excel to Link Sheets

  • Now, use the AutoFill tool again to fill up the rest of the missing data.

Create a Link Formula in Excel to Link Sheets

Read More: How to Link Two Sheets in Excel


1.2 Different Workbook

Suppose, you need to fill up missing data from a different workbook sheet. You can create the link formula for this purpose too.

Here, our source workbook is Book1 and the sheet name is X sheet.

Create a Link Formula in Excel to Link Sheets

And we need to fill the missing data as shown in the red-colored box in the following picture in sheet link X sheet of the workbook we’re using.

STEPS:

  • First, select cell C9 and type the formula:
='[Book1]X sheet'!C9 

  • Now, press Enter and use the AutoFill tool to fill up the cell on the right side.
  • Here, you should check Fill Without Formatting from the AutoFill Options to keep the format as it’s in the source sheet.

  • Finally, use the AutoFill tool again to fill up the rest of the missing data.


2. Excel HYPERLINK Function for Linking Sheets

Another great way to Link Sheets in Excel is by applying the HYPERLINK function. This function will take you to the source location with just a click.

In this example, we have missing data in cell D7 and we want to see the cell value present at the worksheet HYPERLINK1, which is our source.

Excel HYPERLINK Function for Linking Sheets

STEPS:

  • In the beginning, select cell D7 and type the formula:
=HYPERLINK("#'HYPERLINK1'!D7", "link location")

Excel HYPERLINK Function for Linking Sheets

  • Next, press Enter and you’ll see the link location in underlined blue color as it’s shown in the image below.

Excel HYPERLINK Function for Linking Sheets

  • And finally, pressing the link location will take you to the source worksheet.

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


3. Link Sheets with Excel 3D Formula

In this method, we’ll use an Excel 3D Formula to link other sheets and also calculate the Sum. 3D Formulas operate by referencing the identical cell locations present across different worksheets.

In this example, we’ll show how to find out the total of Net Sales by adding the values from sheets sheet1 and sheet2.

Link Sheets with Excel 3D Formula

STEPS:

  • Firstly, select cell D5 and type the formula:
=SUM(sheet1:sheet2!D5)

Link Sheets with Excel 3D Formula

  • Then, press Enter and use the Fill Handle tool to fill the series.
  • And finally, you’ll get your desired outcome.

But if you have Space in your worksheet title, you have to change a bit in the formula. Follow the below process.

STEPS:

  • Firstly, select cell D5.
  • Here, type the formula:
=SUM('sheet 1:sheet 2'!D5)

  • Then, press Enter and use AutoFill to fill up the missing data.

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


4. SUM Formula to Link Sheets in Excel

We’ll apply the SUM Formula in our last method to Link Excel Sheets and add different cell values.

In this example, we’ll link the SUM summary sheet with sheets sheet3 and sheet4 and also add the D5 cell values of sheets sheet3 and sheet4.

SUM Formula to Link Sheets in Excel

STEPS:

  • First, select cell D5.
  • Here, type the formula:
=SUM(sheet3!D5,sheet4!D5) 

SUM Formula to Link Sheets in Excel

  • Next, press Enter and use the Fill Handle tool to fill up the rest of the cells.
  • And in the end, you’ll get your desired output.


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Now you will be able to Link Sheets in Excel with a Formula using the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo