How to Make Summary in Excel From Different Sheets

Sometimes we have to make a summary from different sheets of a workbook in Microsoft Excel. It is frequently used in schools, companies, and shops which helps them to summarize their dataset. This tutorial will teach you how to make summary in Excel from different sheets in 3 suitable ways. Let’s get started!

Download Practice Workbook

You can download the Excel workbook from here.

3 Suitable Ways to Make Summary in Excel from Different Sheets

We can make a summary in Excel from different sheets in 3 suitable ways. The first method is by using the Consolidate feature, the second method is by applying the INDIRECT function and the last method is by linking multiple sheets. We will learn all of these 3 methods in this article. Let us first introduce our dataset. We have 2 sheets in our Excel file. The first sheet shows the Employee details for Monday as shown below.

And the second sheet shows the Employee details for Tuesday. Our task is to make a summary sheet from these 2 sheets by applying the 3 ways mentioned above.

1. Use of Consolidate Feature

The Consolidate feature is used to make summary from different sheets in Excel and perform a function between the sheets. In this example, we will make a summary table from our 2 sheets and sum the paid amount of the employees for Monday and Tuesday using the Consolidate feature. Follow the steps below to do the task.

Steps:

• First, go to the Data tab and select the Consolidate feature as shown below.

• As a result, Consolidate window will pop up as shown below.
• Second, choose Sum from the Function drop-down.

• Third, click on the Reference field to choose a reference dataset.

• Furthermore, select cells ($B$4:$C$12) from the Monday sheet to choose the first reference dataset.

• Afterward, click Add to add the reference dataset.

• Subsequently, click on the Reference field again to choose the second reference dataset.

• Now, select cells ($B$4:$C$13) from the Tuesday sheet to choose the second reference dataset.

• After that, click Add to add the reference dataset.

• Next, check the Top row and Left column from the Use labels option and click OK as shown below.

• As a result, you will see a summary table like the below one which will sum up the Paid Amount of all the employees for Monday and Tuesday.

Read More: How to Summarize a List of Names in Excel (5 Effective Ways)

Similar Readings

• How to Summarize Data by Multiple Columns in Excel
• Summarize Data Without Pivot Table in Excel
• How to Summarize Data in Excel Using Pivot Table (2 Examples)

2. Applying INDIRECT Function

Applying the INDIRECT function is another way to make a summary from different sheets and reference values in Excel. In this example, we will make a summary of the Total Paid Amount on Monday and Tuesday from both sheets using the INDIRECT function. Follow the steps below to use this method.

Steps:

• Firstly, we need to make a dataset like the below one mentioning the names of our sheets exactly as they exist in the Excel Now we will reference the Total Paid Amount cells from both sheets and make a summary in this sheet.

• Secondly, select cell C5 and type the following formula:

• As a result, it will reference the C14 cell from the Monday sheet and display the Total Paid Amount for Monday.

• Similarly, select cell C6 and type the following formula:

• Consequently, it will reference the C15 cell from the Tuesday sheet and display the Total Paid Amount for Tuesday.
• Hence, our summary table is ready.

Read More: How to Summarize Data in Excel (8 Easy Methods)

3. Linking Multiple Sheets

Linking Multiple Sheets is an easy and efficient way to make a summary table in Excel from different sheets and reference any cell from another sheet. In this example, we will make a summary of the Total Attended Employee and Total Paid Amount for Monday and Tuesday linking multiple sheets.

Follow the steps below to apply this method.

Steps:

• To begin with, select cell C5 and type the following formula:

• As a result, it will count the Total Employee who was on Monday and display the number.

• Similarly, select cell C6 and type the following formula:

• Hence, it will count the Total Employee who was on Tuesday and display the number.

• Afterward, select cell C9 and type the following formula:

• Consequently, it will reference the value of cell C14 from the Monday sheet and display the value of the Total Paid Amount on Monday.

• Furthermore, select cell C10 and type the following formula:

• Hence, it will reference the value of cell C15 from the Tuesday sheet and display the value of the Total Paid Amount on Tuesday.
• Finally, our summary table is complete linking multiple sheets.

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

Things to Remember

• After using the Consolidate feature, you can format your dataset in your own way to represent it well.
• In this example, we have made a summary from 2 different sheets in Excel But you can use these methods to make a summary from more than 2 sheets.

Conclusion

Hence, follow the above-described steps. Thus, you can easily learn how to make summary in Excel from different sheets. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.

Related Articles

• How to Summarize Subtotals in Excel (3 Easy Methods)
• Group and Summarize Data in Excel (3 Suitable Ways)
• How to Summarize Text Data in Excel (with Easy Steps)