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.

• 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:
`=Excel.CurrentWorkbook()`

• 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)

Row_1 = headers.Row + 1
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
`=VLOOKUP(B5,Marks!B4:C14,2)`

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.

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

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

1. Power query is the replacment to MSQuery. I was au fait with MSQ but after a number of years have not managed to get to grips with Power Query. What I want to do is quite simple: to join two tables into one data set. This is more than just dump them into one query/table as they have different yet related data, along the lines of your student ID master file holding student names etc. I could use VLOOKUP() but I don’t want tens of thousands of additional formulae. I am avoiding using Access which is perhaps the obvious contender. How to I create relationships between tables using this new-fangled so-called “power” query?

• Hello Keith Miller,

I understand your frustration with transitioning from MSQuery to Power Query, especially after being familiar with MSQuery for so long. The learning curve can be a bit daunting, but the good news is that Power Query is incredibly powerful and can handle the task you described quite efficiently.

To join two tables into one data set without resorting to Access or using numerous VLOOKUP formulas, Power Query is indeed your best bet.

To create relationship between two tables you can follow the given steps too:
Let’s say, we have two tables describing different products ordered by some customers from separate addresses and their respective prices.

I have created the first column named Customer Information with headings: Customer ID, Name, and Address.

Another table named Order Information has headings like: Name, Product, and Price.

It is noticeable that there must be a common column to create a relationship between the tables.

Here, I will show the method of creating relationships using Pivot Table. In order to demonstrate this method, proceed with the following steps.

Steps:

• First of all, select the data range of the first table (i. e. Customer Information)> go to the Insert tab> click Table.

• Then, click OK on the Create Table dialogue box.

• Now, your table will be created.

• Here, follow the same procedure for the other table (i.e. Order Information).

• Now, click on the Table > go to the Table Design tab> assign a name for the table.

I have named the first table Customer and the second table Order.

• After that, click on the Customer table> go to the Insert tab> and click Pivot Table.

• Here, PivotTable from table or range dialogue box will show up. Mark  Add this data to the Data Model box and click OK.

• Now, the Pivot Table Fields will show up.

• Now, select the data type from both the tables (i.e. Address from the first table and Price from the second table) and click CREATE.

• After that, Create Relationship dialogue box will show up. Here, assign the table names and the common column (i.e. Name) and click OK.

• Finally, Excel will create a relationship between the two tables.

So, these are the steps you can follow to create a relationship between tables using the Pivot Table option.

Download the Excel File: Creating Relationship Between Tables.xlsx

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF