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

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.

Download Practice Workbook

Download the following workbook to practice by yourself.

4 Effective Methods to Link Sheets in Excel with a Formula

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.

STEPS:

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

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

• 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.

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

Read More: How to Link Excel Workbooks (4 Effective Methods)

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.

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:

• 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.

Read More: Reference from Another Excel Workbook without Opening (5 Examples)

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.

STEPS:

• In the beginning, select cell D7 and type the formula:

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

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

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

Similar Readings

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

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.

STEPS:

• Firstly, select cell D5 and type the 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:

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

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

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.

STEPS:

• First, select cell D5.
• Here, type the formula:

• 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.

Read More: How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)

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

• How to Link Two Workbooks in Excel (5 Methods)
• Transfer Specific Data from One Worksheet to Another for Reports
• How to Link Data from One Spreadsheet to Another in Excel
• Link Data in Excel from One Sheet to Another 
• How to reference cell in another Excel sheet based on cell value
• How to Link Excel Data Across Multiple Sheets (7 Easy Ways)