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

Often, we have to deal with multiple worksheets in Excel and hover to different sheets to find a value. In this article, we demonstrate how to link sheets in Excel to a master sheet.

Let’s say we have Sale data for December’21 of three different cities, New York, Boston, and Los Angeles. These three Sale data are identical in orientation, so we show only one worksheet as a dataset.

Dataset-How to Link Sheets in Excel to a Master Sheet

We want to link city sale sheets to a master sheet using HYPERLINK, INDIRECT functions as well as multiple Excel features.


Download the Workbook


5 Easy Ways to Link Sheets to a Master Sheet in Excel

Method 1: Using HYPERLINK Function to Link Sheets to a Master Sheet in Excel

As we want to link sheets to a master sheet, we can use the HYPERLINK function to link multiple sheets as clickable hyperlink destinations in a master sheet. The HYPERLINK function converts a destination and a given text into a hyperlink. For this purpose, we can instantly move to a worksheet as per our demand only clicking on the links existing in a master sheet.

The syntax of the HYPERLINK function is

HYPERLINK (link_location, [friendly_name])

In the formula,

link_location; the path to the sheet you want to jump.

[friendly_name]; display text in the cell where we insert the hyperlink [Optional].

Step 1: Paste the following formula in any cell (i.e., C5).

=HYPERLINK("#'"&B5&"'!A1",B5)

If we compare the arguments,

 “#'”&B5&”‘!A1″= link_location

B5=[friendly_name]

Formula insertion-How to Link Sheets in Excel to a Master Sheet

 Step 2: Press ENTER then Drag the Fill Handle to make the other hyperlinks appear in cells C6 and C7.

Result

You see hyperlinks for Boston and Los Angeles appear as they did for New York.

You can check whether the hyperlinks work or not, by clicking on any hyperlinks. For this reason, we click on the New York named hyperlink.

Click on hyperlink

In a moment, we jump to the New York sheet’s A1 cell (as directed in the formula) as shown in the image below.

Hyperlink destination

You can test the hyperlinks for each sheet and every time you’ll jump to the destination as directed in the formula. For better understanding and brief representation, we use only three Excel worksheets, you can use as many as you want.

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


Method 2: Using Reference in a Formula to Link Sheets to a Master Sheet in Excel

In the previous method, we discussed linking sheets to a master sheet. What if we want some cell value fetched in a master sheet? For example, we have a Total Sale amount for each sheet, and we want to link only the Total Sale value in the master sheet. We can achieve it by inserting a cell reference of respective sheets in a formula in the master sheet.

Step 1: To insert a formula, just Type the Equal Sign (=) in the formula bar.

Equal in formula bar

Step 2: After typing the Equal Sign (=) in the Formula Bar, Go to the respective sheet (i.e., New York) you want to reference a cell from then Select Total Sale sum amount cell (i.e., F13) as reference.

Destination sheet

Step 3: As you select the reference cell, Hit ENTER. You’ll jump back to the master sheet with the sum amount of Total Sale for the respective sheet (i.e., New York) similar to the image below.

Reference in master sheet

You can link other sum amounts by repeating the same steps (i.e., Steps 1 to 3) mentioned earlier. And you’ll come up with something like the following picture.

Result-How to Link Sheets in Excel to a Master Sheet

In this method, we can use any cell reference to link the sheets with a master sheet.

Read More: How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)


Method 3: Using INDIRECT Function to Link Sheets into a Master Sheet in Excel

We link a cell of sheets with a cell reference in the master sheet using a formula. However, this can also be achieved using the INDIRECT function. The INDIRECT function creates a cell reference using a text string. The syntax of the INDIRECT function is

INDIRECT (ref_text, [a1])

The arguments refer,

ref_text; reference in the form of text.

[a1]; a boolean indication for A1 or R1C1 style reference [Optional]. Default represents TRUE=A1 style.

Step 1: Write the following formula in any blank cell (i.e., C5).

=INDIRECT("'"&B5&"'!F13")

Since we know the cell reference for the sum of Total Sale is in F13 for all three sheets and B5 represents the sheet name from where the data will be fetched.

Formula insertion- Indirect function

Step 2: Pressing ENTER, drag the Fill Handle to bring out the amount for other sheets. In a moment, you’ll see the sum amounts of Total Sale appear.

Fill handle-How to Link Sheets in Excel to a Master Sheet

You can use the Context Menu to do the same job. For achieving the same outcomes using the Context Menu,

1st right-click on any sheet (i.e., New York) cell (i.e., F13) then Select Copy.

Context menu

2nd Go to the Master sheet, right-click on the cell where you want to insert the value. The Context Menu appears, Choose Paste Special > Click on Paste Link (from Other Paste Options).

Paste special-How to Link Sheets in Excel to a Master Sheet

You’ll see the sum value as shown in the following picture. You can repeat these two simple sequences for other cells also.

Result-Context menu

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


Related Readings


Method 4: Using Name Box to Link Sheets to a Master in Excel

Excel offers a feature called Name Box. Using the Name Box feature, we can refer to any cell or range in Excel. For this case, we can use the Name Box to name a certain cell in sheets and then link it to the master sheet. As we want to link the sum amount of each sheet, we have to assign a name to each sum Total Sale cell of the respective sheets.

Step 1: Assign a name (i.e., NY_Total_Sale) for New York to cell F13 using the Name Box. Repeat the step for other sheets such as Boston and Los Angeles.

Name Box-How to Link Sheets in Excel to a Master Sheet

It can be checked whether naming by Name Box successfully does the job or not. To do so, Go to Formulas Tab > Select Name Manager (from the Defined Names section).

Defined Names

⏩ The Name Manager window pops up and you can find all the assigned names in the workbook.

Name manager-How to Link Sheets in Excel to a Master Sheet

From the above screenshot, you can see the assigned names that we named after certain cells.

Step 2: After assigning names, Go to the master sheet, Type =NY… for inserting the sum value from the New York sheet. You see assigned names as selectable options. Select the option.

Name insertion

As you select the option, the sum of the Total Sale (for New York) value appears in the cell.

Value

If you repeat the Steps (i.e., Steps 1 and 2) for other cities, you’ll get all the values for respective cities as shown in the following image.

Final result Name Box-How to Link Sheets in Excel to a Master Sheet

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


Method 5: Using Paste Link Option to Link Sheets to a Master Sheet in Excel

In Insert Tab, Excel offers a feature as Insert Link. We can make any cell link using this feature then insert it in any cell we want. In this case, we can make individual links for a cell from any sheet then insert them into the master sheet. In this way, we can link multiple sheets to a master sheet.

Step 1: First, identify the cell that you want to insert the link. The cell is F13 of the New York sheet. You have to repeat the steps for each sheet.

Inspection-How to Link Sheets in Excel to a Master Sheet

Step 2: In the master sheet, Place the mouse (i.e., C5) where you want to insert the link. Then Go to Insert Tab > Select Insert Link (from Link section).

Insert link

Step 3: The Insert Hyperlink window opens up. In the window,

Select Place in the Document (under Link to options).

Type F13 (in the Type the cell reference option)

Select ‘New York’ (under Or select a place in this document)

Afterward, you see ‘New York’!F13 as Text to display.

Click OK.

Insert hyperlink window-How to Link Sheets in Excel to a Master Sheet

Execution of Step 3 inserts the link in the cell similar to the image below. If you want to check the link, just click on it.

Cross check

It’ll take you to the New York sheet where the value sits.

Destination sheet-How to Link Sheets in Excel to a Master Sheet

You have to repeat the steps to achieve something like the below screenshot.

Final result of Paste link-How to Link Sheets in Excel to a Master Sheet

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


⧭ Note

🔁 Linking the worksheet to a master sheet slows down the Excel workbook. It decreases the performance of the workbook as a whole.


Conclusion

In the article, we demonstrate multiple ways to link worksheets to a master sheet. To do so, we use HYPERLINK and INDIRECT functions as well as multiple Excel features. I hope the above-described methods do your job. If you have further queries or something to add, feel free to comment in the comment section. See you in the next article.


Further Readings

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo