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.

Link Sheets in Excel with a Formula


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.

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 Excel Sheets to Another Sheet (5 Ways)


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.

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.

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 (7 Methods)


Similar Readings


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

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.

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.


Further Readings

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo