How to Combine Data from Multiple Sheets in Excel (4 Ways)

We have several students along with their Student ID and their Marks, where each sheet contains values for a different subject. We’ll consolidate the Marks for different subjects.

Method 1 – Applying the Consolidate Feature to Combine Data from Multiple Excel Sheets

We will add the scores in Physics and Math for each student.

STEPS:

• We’ve made a new worksheet, Consolidate, and copied over the information for Student IDs and Names from the other sheets.
• Go to the Consolidate worksheet and select D5.

• Go to the Data tab and select Consolidate.

• A dialog box for Consolidate will appear.

• Keep the Function drop-down as is since we’re summing the values.
• Click on the search arrow for Reference.
• Go to the Dataset (Physics) worksheet and select the range D5:D14.
• Select Add.

• Add the reference for the range D5:D14 from the Dataset (Math) workbook.

• Click OK. Excel will combine them and return the sum as output.

Method 2 – Using Excel Power Query to Combine Data from Multiple Sheets

STEP 1 – Creating Tables

• Select the range B4:D14.

• Press Ctrl + T.
• The Create Table dialog box will pop up.
• Click OK.

• Excel will create the table.

• Go to the Table Design tab and rename the table.

Repeat to create tables for all datasets.

STEP 2 – Combine Data

• Go to the Data tab, select Get Data, choose From Other Sources, and select Blank Query

• The Power Query Editor window will appear. In the formula bar, use the formula:

• Press Enter. Excel will show the tables in your workbook.

• Click the double-headed arrow (see image).

• Select the columns that you want to combine. We will combine all of them.
• Leave the Use original column name as prefix unmarked.
• Click OK.

• Excel will combine the datasets.

• Select Close & Load.

• Excel will create a new table combining the datasets.

• Rename the Name column. We’re going to call this Section.

NOTE:

When you use the above method, you may face a problem.
Our new table’s name is Query1 which consists of 21 rows including the headers.

• Right-click to bring up the Context Menu and click Refresh.

• Once you refresh, you will see that the row number has changed to 41. That’s because Query1 itself is a table and is working as input.

• Go to the drop-down of the column Name.

• Go to Text Filters and select Does Not Contain.

• A Custom AutoFilter window will open. Write Query1 in the box (see image).
• Click OK.

• The rows having the name Query1 will not be seen even if you refresh the dataset.

• 20 rows are loaded now because Excel is not counting the header this time.

Method 3 – Combining Data from Multiple Sheets Using VBA Macro

We have two worksheets, Dataset (Physics_A) and Dataset (Physics_B). We’ll combine the data from these datasets into a new worksheet named Consolidate.

STEPS:

• Go to the Developer tab and select Visual Basic.

• Go to the Insert tab and Module.

• A Module window will appear. Insert the following code.

Sub combine_multiple_sheets()

Dim Row_1, Col_1, Row_last, Column_last As Long
Dim headers As Range

Set wX = Worksheets("Consolidated")
Set WB = ThisWorkbook
Set headers = Application.InputBox("Choose the Headers", Type:=8)
headers.Copy wX.Range("A1")

Row_1 = headers.Row + 1
Col_1 = headers.Column
Debug.Print Row_1, Col_1

For Each ws In WB.Worksheets

If ws.Name <> "Consolidated" Then
ws.Activate
Row_last = Cells(Rows.Count, Col_1).End(xlUp).Row
Column_last = Cells(Row_1, Columns.Count).End(xlToLeft).Column
Range(Cells(Row_1, Col_1), Cells(Row_last, Column_last)).Copy _
wX.Range("A" & wX.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If

Next ws
Worksheets("Consolidated").Activate

End Sub

• Press F5 to run the program. Excel will create a combined dataset.

NOTE:

This VBA code will combine all the sheets available in your workbook indiscriminately. Make sure that your datasets follow the same formatting.

Method 4 – Inserting the VLOOKUP Function to Combine Data from Multiple Sheets

We have a worksheet named Names where we have the names of some students and another one named Marks. The sheets share the Student ID column. We’ll create a proper Result sheet by combining them.

Steps:

• Create a new column Marks after Names.

• Go to D5 and insert the following formula

We have set the lookup value to B5 and the array is B4:C14 from the Marks sheet. The col_ind_num is 2 as we want the marks.

• Press Enter. Excel will return the output.

• Use the Fill Handle to AutoFill down to D14. Excel will extract the marks from the Marks worksheet for each student.

Download the Practice Workbook

<< Go Back To Merge Sheets in Excel | Merge in Excel | Learn Excel