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!

How to Make Summary in Excel from Different Sheets: 3 Suitable Ways

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.

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:

=INDIRECT(B5&"!C14")

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:

=INDIRECT(B6&"!C15")

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.

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:

=COUNTA(Monday!B5:B12)

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:

=COUNTA(Tuesday!B5:B13)

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

Afterward, select cell C9 and type the following formula:

=Monday!C14

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:

=Tuesday!C15

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.

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.

Download Practice Workbook

You can download the Excel workbook from here.

Making Summary from Different Sheets.xlsx

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. Don’t forget to drop your comments, suggestions, or queries in the comment section below.