Linking Excel Sheets to a Summary Page (4 Easy Methods)

While working with Microsoft Excel, you may have dealt with several sheets in your workbook. However, if you need to link Excel sheets to a summary page, you can utilize these worksheets. Today, in this article, we’ll learn four quick and suitable ways for linking Excel sheets to a summary page effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Suitable Ways of Linking Excel Sheets to a Summary Page

Let’s say, we have a dataset that contains information about several Sales Representatives of the Armani group. The Name of the Sales Representatives, their Identification number, and the revenue earned in several quarters by the Sales Representatives are given in columns B, C, and D respectively. We have several sheets which contain the sales statement for several quarters. The name of those sheets is Quarter 1, Quarter 2, Quarter 3, and Quarter 4. We will link those sheets using the Hyperlink command, consolidate command, and so on. Here’s an overview of the dataset for today’s task.

linking excel sheets to a summary page


1. Use Consolidate Command to Link Excel Sheets to a Summary Page

In this section, we will apply the Consolidate command to link Excel sheets to a summary page. This is an easy and time-saving task. Let’s follow the instructions below to learn!

Step 1:

  • First, select the cell After that, from your Data ribbon, go to,

 Data → Data Tools → Consolidate

Use Consolidate Command to Link Excel Sheets to a Summary Page

  • As a result, a Consolidate dialog box will appear in front of you. From the Consolidate dialog box, firstly, type ‘Quarter 1’!$B$4:$D$14 in the Reference Further, press Add option.

  • Similarly, Add the rest of the sheet value. After that, check the Top row, Left column, and Create links to source data like the below screenshot. At last, press OK.

Use Consolidate Command to Link Excel Sheets to a Summary Page

  • Finally, you will be able to link Excel sheets to a summary page which has been given in the below screenshot.

  • Now, we will sum up the total revenue earned by the sales representatives using the SUM function like the below screenshot. The return of the SUM function is $5,270,036.00.

Use Consolidate Command to Link Excel Sheets to a Summary Page

Step 2:

  • After linking Excel sheets to a summary page, we will check whether it works or not. To do that, firstly, change any value of the source data. We will change the revenue earned by Samuel Jhonson from $51,371.00 to $71,371.00.

  • Hence, we will notice that the total revenue earned is changed from $5,270,036.00 to $5,290,036.00 automatically which has been given in the below screenshot.

Use Consolidate Command to Link Excel Sheets to a Summary Page

Read More: How to Make Summary in Excel From Different Sheets


2. Perform Hyperlink Command to Link Excel Sheets to a Summary Page

Now, we will apply the Hyperlink command to link Excel sheets to a summary page. Let’s follow the instructions below to learn!

Step 1:

  • First, select the cell After that, from your Insert ribbon, go to,

 Insert → Links → Link

Perform Hyperlink Command to Link Excel Sheets to a Summary Page

  • As a result, an Insert Hyperlink dialog box will appear in front of you. From the Insert Hyperlink dialog box, firstly, select Place in This Document under the Link to Further, select ‘Quarter 1’ under the Or select a place in this document drop-down list. At last, press OK.

  • Hence, you will be able to link the ‘Quarter 1’ sheet with the Summary_Hyperlink sheet in cell B5.

Perform Hyperlink Command to Link Excel Sheets to a Summary Page

  • Similarly, you can link the rest of the sheets with the Summary_Hyperlink sheet like the below screenshot.

Step 2:

  • Click on any of the cells and it will take you to the respected worksheet.

Perform Hyperlink Command to Link Excel Sheets to a Summary Page

  • And our job is done!

Perform Hyperlink Command to Link Excel Sheets to a Summary Page

Read More: How to Create a Summary Sheet in Excel (4 Easy Ways)


Similar Readings


3. Define Selection Portion to Link Excel Sheets to a Summary Page

The easiest way for linking Excel sheets to a summary page giving the name of the selected portion. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cells B5 After that, give a name of the selected range, and the name is Group. To do that, press on the left-top box and type that name like the below screenshot.

Define Selection Portion to Link Excel Sheets to a Summary Page

  • After that, select cell B5 and type the below formula. The formula is,
=Group

  • Hence, simply press ENTER on your keyboard, and you will get the return of that function with linking.

Define Selection Portion to Link Excel Sheets to a Summary Page

  • After linking Excel sheets to a summary page, we will check whether it works or not. To do that, firstly, change any value of the source data. We will change the revenue earned by Samuel Jhonson from $51,371.00 to $61,371.00.

Define Selection Portion to Link Excel Sheets to a Summary Page

  • Hence, we will notice that the revenue earned by Samuel Jhonson is changed from $51,371.00 to $61,371.00 automatically which has been given in the below screenshot.

Define Selection Portion to Link Excel Sheets to a Summary Page

Read More: How to Group and Summarize Data in Excel (3 Suitable Ways)


4. Linking Excel Sheets to a Summary Page Manually

Last but not the least, we will link Excel Sheets to a summary page. From our dataset, we can easily do that. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cell C5 and type =.

Linking Excel Sheets to a Summary Page Manually

  • Hence, go to the ‘Quarter 1’ sheet, and select cell D15.

  • After that, simply press ENTER on your keyboard, and you will be able to link the ‘Quarter 1’ sheet with the sheet named Summary.

Linking Excel Sheets to a Summary Page Manually

  • Similarly, linking the sheets ‘Quarter 2’, ‘Quarter 3’, and ‘Quarter 4’ with the sheet named Summary which has been given in the below screenshot.

  • Now, we will sum up the total revenue earned by the sales representatives using the SUM function like the below screenshot. The return of the SUM function is $2,762,760.00.
  • To check the linking works or not, simply repeat step 2 of method 1.

Linking Excel Sheets to a Summary Page Manually

Read More: How to Summarize Subtotals in Excel (3 Easy Methods)


Things to Remember

➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.

#DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.


Conclusion

I hope all of the suitable methods mentioned above to link Excel sheets to a summary sheet will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo