Sometimes we need to summarize the data for a whole year or sometimes more than that. If we follow the manual process then it will take a lot of time to summarize a lot of data. Excel has some excellent features to create a summary sheet of multiple worksheets very easily. We can also hyperlink the multiple worksheets of an Excel workbook through this process. Let’s see below the methods to create a summary sheet in Excel.
How to Create a Summary Sheet in Excel: 4 Easy Ways
Suppose, we have an Excel workbook that contains 4 worksheets named Level-1, Level-2, Level-3 and Level-4 respectively. Here, each worksheet contains a dataset (B4:E9) that has the Marks of a student in different Subjects of 3 Terms. In this article, we will discuss 4 quick methods to create a summary sheet of these worksheets. So, without further delay, let’s get started.
1. Create a Summary Sheet Using Automatic Update from Group Sheet Feature
In the first method, we will learn to create a summary sheet using the group sheet technique that contains automatic update features. So, if we change any data in the worksheets then the summary sheet will automatically update. Here, we have used the SUM function to do so. For example, we will calculate the total marks of Math, Physics, Chemistry, and Economics at all 4 levels. Let’s see the steps below.
Steps:
- First, open a new worksheet and create a dataset (B4:C7) like the screenshot below.
- Secondly, select the cell next to the cell of Total Marks of Math.
- Thirdly, go to the Home tab.
- Then, go to the Editing group and click on the AutoSum option.
- As a result, the SUM function will automatically appear in the cell just like the screenshot below.
- Now, click on the Level-1 sheet tab and holding down the Shift key, click on the Level-4 sheet tab.
- Consequently, all the worksheets from Level-1 to Level-4 will be selected. We can also see the selection in the Formula Bar of the screenshot below.
- At this time, select the cell range C5:E5. See the Formula Bar of the screenshot below.
- After pressing the Enter key, we will get the result in the C4 cell of the worksheet.
- Finally, drag the fill handle to find the summation of the rest of the Subjects.
- Therefore, we can see all the summations in the screenshot below.
- However, change the Marks of Math of Term 1 to 90 to see if it automatically updates the summation.
- Hence, we can see that the summation is also updated to 1059.
Read More: How to Group and Summarize Data in Excel
2. Insert Excel VBA to Form a Summary Sheet with Hyperlinks
We can also use the VBA code in Excel to create a summary sheet in Excel. By this method, we can create a hyperlink to the worksheets in a new worksheet. Let’s see the steps below to utilize this method.
Steps:
- First, create a new worksheet and select a blank cell (B4) in it.
- Then, go to the Developer tab.
- After that, go to the Code group and click on Visual Basic.
- When the Microsoft Visual Basic for Applications window appears, go to Insert and select Module.
- Now, insert the below code in the code window.
Sub SummarySheet()
Dim s As Worksheet
Dim c As Range
For Each s In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> s.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & s.Name & "'" & "!A1", TextToDisplay:=s.Name
ActiveCell.Offset(1, 0).Select
End If
Next s
End Sub
- Subsequently, go to Run and select Run Sub/UserForm.
- Thus, the hyperlinks of all the worksheets of the Excel workbook will be added to the desired worksheet. Hence, by clicking on any hyperlink, we will move to that worksheet at once.
3. Prepare a Summary Sheet Using Excel Consolidation Tool
If you have two or more similar Microsoft Excel worksheets, you can use Excel’s Consolidate tool to create a summary sheet. This tool is located at the Data tab of an Excel worksheet and helps to make a summary report from multiple worksheets. The steps to use this feature are below.
Steps:
- In the beginning, select a blank cell (C4) in a new worksheet.
- Next, go to the Data tab.
- Now, go to the Data Tools group and select the Consolidate option.
- As a consequence, a Consolidate window will pop up.
- If you want the sum of the values of the worksheets then select Sum from the Function dropdown.
- After that, keep the cursor in the Reference box.
- Then, click on the Level-1 sheet tab and select cell C5.
- Consequently, the reference of the cell will be added to the Reference box.
- Eventually, click on Add and then the reference will be inserted in the All references box.
- Subsequently, click on the Level-2 sheet tab and we will see that the cell (C5) that we previously added is already selected.
- Go to the Consolidate window again and add this cell reference also.
- Similarly, add all the references in the box.
- You also need to give a tick mark in the box of Create links to source data to automatically update any change of the source data.
- Lastly, click on OK.
- In this way, we can find the summation of the values from multiple worksheets.
Read More: How to Make Summary in Excel From Different Sheets
4. Apply Excel Pivot Table Feature to Summarize Multiple Worksheets
We can also use the Pivot Table feature in Excel to create a summary sheet. The Pivot Table toolbar is in the Insert tab of an Excel worksheet and offers a set of icon buttons that allow you to filter and sort data. The steps to summarize multiple worksheets in Excel using the Pivot Table feature are below.
Steps:
- To begin with, select a blank cell (B4) in a new worksheet.
- Now, go to the Insert tab and click on Pivot Table.
- Then, select From Table/Range from the dropdown.
- In turn, a window named PivotTable from table or range will open.
- Here, keep the cursor in the Table/Range box.
- Afterward, select the desired table.
- Therefore, the reference of the table will be added to the Table/Range box.
- Subsequently, choose the Existing Worksheet and check the Location of the table in the worksheet.
- Eventually, click on the OK button.
- As a result, we will see a Pivot Table area and a section named PivotTable Fields in the worksheet.
- Now, select the column headings of the table that you want to insert here. For example, we selected Subject, Term 1 and Term 2.
- Thus, we will see the contents of the column headings and also the summation of the values in each column. However, the Pivot Table feature has automatically calculated the summation.
- If you want the average of the column values, select the cell (C10).
- At this moment, go to the PivotTable Fields section on the right side of the sheet.
- Accordingly, go to Values and click on the dropdown arrow of the heading.
- Later, click on Value Field Settings.
- Thereupon, the Value Field Settings window will open up.
- In this case, go to Summarize value field by and select Average.
- Finally, click OK.
- Like this, we can find the Average of the column.
Read More: How to Summarize Data Without Pivot Table in Excel
Download Practice Workbook
Download the practice workbook from here.
Conclusion
I hope the above methods will be helpful for you to create a summary sheet in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.
Related Articles
- How to Summarize Subtotals in Excel
- How to Create Summary Table in Excel
- How to Summarize a List of Names in Excel
- How to Summarize Text Data in Excel
- How to Summarize Data by Multiple Columns in Excel
- How to Create Summary Table from Multiple Worksheets in Excel
<< Go Back to Summarize Data In Excel | Data Analysis with Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
