ExcelInsider Team
When we work with multiple Excel sheets, managing data can quickly become difficult. A common solution is linking several sheets to one master sheet for easy tracking, analysis, and reporting. This process is easy and you can do that in minutes.
To link all sheets in Excel to a master sheet, follow these steps:
➤ Open the workbook and identify the sheets you want to link.
➤ In the master sheet, select the cell where the data will appear.
➤ Enter the linking formula (e.g., =HYPERLINK(CONCAT(“#'”, B2, “‘!A1”), “North Region”)) for each dataset.
In this article, we will cover the three different methods like Hyperlink, Top method and custom VBA method to link sheets in Excel to a master sheet.
Using Name Manager & INDEX Function to Link Sheets to a Master Sheet
This method creates a worksheet in Excel that links to all other worksheets in the same file. It is good when we deal with workbooks that have many sheets (e.g., monthly reports, departmental data, or project logs). Instead of scrolling through dozens of sheet tabs, you can quickly jump to any worksheet to use clickable links.
A company maintains monthly sales records in separate worksheets inside one Excel workbook. Worksheets are named after months (e.g., Jan 2025, Feb 2025, …, Dec 2025). The challenge is to quickly move between sheets without scrolling through dozens of tabs.
Steps:
➤ We will insert a new sheet at the beginning of the workbook. To do that, create a new worksheet and rename it as TOC. TOC stands for Table of Contents.
➤ Create a new table with the following columns: Serial No, Table of Content and Hyperlink. Assign the serial number in column A.
➤ Go to the Formulas tab → Name Manager → New
➤ Give the Name ListofWorkSheets without using Space. Enter the following formula in the Refers to and click Ok .
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
This function automatically collects all worksheet names in the workbook.
➤ Go back to the TOC table. In cell B2, enter the formula to extract sheet names:
=INDEX(Listofworksheets, A2)
➤ Drag the fill handle down from B2 to fill the formula for all rows.
➤ In column C, insert this Hyperlinks for Navigation in cell C2 and press Enter:
=HYPERLINK(CONCAT("#'", B2, "'!A1"), "Go to Sheet")
Here,
➧ B2 = The cell with the sheet name.
➧ !A1 = Means the hyperlink will take you to cell A1 of that worksheet.
➧ “Go to Sheet” = The clickable text displayed.
➤ Drag this formula down for all rows. Now you can click “Go to Sheet” to navigate any worksheet.
Applying HYPERLINK Function to Link Sheets to a Master Sheet in Excel
This method allows you to create a central navigation sheet (Master Sheet) that links to all other sheets in your workbook. It is particularly useful when we work with large datasets across multiple sheets, such as sales reports by region, monthly logs, or department-wise performance sheets.
In this example, we will create a Master Sheet that acts as a navigation hub. Instead of scrolling through dozens of sheets, you can click the hyperlinks in the Master Sheet to instantly jump to each region’s data sheet.
Steps:
➤ Insert a new worksheet and rename it to Master Sheets. This will be the central hub for navigation.
➤ Click on cell B2 and use the following formula which refers to the North Region.
=HYPERLINK("#'North Region'!A1","North Region")
➤ Repeat the same formula in the rest of the cells after changing the sheet name. Here I have entered “South Region” to navigate to the sheet named South Region.
=HYPERLINK("#'South Region'!A1","South Region")
➤ After repeating this same action for every cell in the Master Sheet now we have the master sheet.
Frequently Asked Questions
How do I link an Excel sheet to another sheet?
You can use a simple formula like =SheetName!CellReference to link a cell from one sheet to another.
How do I combine multiple Excel sheets into one master sheet?
You can either use formulas, the “Consolidate” tool, or Power Query to bring data together into one sheet.
How do I pull data from a master sheet to another sheet in Excel?
Enter a formula in the secondary sheet that references the cell in the master sheet, e.g., =MasterSheet!A1.
How do I link entire sheets in Excel to a master sheet?
Use Power Query or VBA macros for automating the process when linking entire sheets instead of individual cells.
Concluding Words
We have shown 2 methods on how to link sheets in excel to a master sheet. Download the excel file with all the dataset we have used in this article, practice all methods there and let us know if you have any questions in the comment section below.
