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.
We want to link city sale sheets to a master sheet using HYPERLINK, INDIRECT functions as well as multiple Excel features.
How to Link Sheets in Excel to a Master Sheet: 5 Easy Ways
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]
Step 2: Press ENTER then Drag the Fill Handle to make the other hyperlinks appear in cells C6 and C7.
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.
In a moment, we jump to the New York sheet’s A1 cell (as directed in the formula) as shown in the image below.
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 Cell to Another Sheet in Excel
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.
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.
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.
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.
In this method, we can use any cell reference to link the sheets with a master sheet.
Read More: How to Reference Cell in Another Excel Sheet Based on Cell Value
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.
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.
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.
⏩ 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).
You’ll see the sum value as shown in the following picture. You can repeat these two simple sequences for other cells also.
Read More: How to Link Sheets in Excel with a Formula
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.
⏩ 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).
⏩ The Name Manager window pops up and you can find all the assigned names in the workbook.
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… to insert the sum value from the New York sheet. You see assigned names as selectable options. Select the option.
As you select the option, the sum of the Total Sale (for New York) value appears in the cell.
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.
Read More: How to Link Two Sheets in Excel
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.
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).
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.
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.
It’ll take you to the New York sheet where the value sits.
You have to repeat the steps to achieve something like the below screenshot.
Read More: How to Link Data in Excel from One Sheet to Another
⧭ Note
🔁 Linking the worksheet to a master sheet slows down the Excel workbook. It decreases the performance of the workbook as a whole.
Download the Workbook
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.
