How to Create Table from Multiple Sheets in Excel (4 Easy Ways)

Looking for ways to create a table from multiple sheets in Excel? Then, this is the right place for you. Sometimes, datasets are stored in different sheets, and we want to use the summary or some selective information from those data. In those cases, we can create a table from multiple sheets and look for the information we want. Here, you will find 4 ways to do that in Excel.

How to Create Table from Multiple Sheets in Excel: 4 Easy Ways

Here, we have 2 datasets containing the Product Name, Quantity, and Sales amount of 2 shops in different worksheets.

The above image is the dataset for Shop 1.

Additionally, this is Shop 2 worksheet which contains information about the other shop. Now, using these 2 worksheets we will show how you can create a table from multiple sheets in Excel.

1. Create Table from Multiple Sheets Using Keyboard Shortcut

In the first method, we will use the “Alt + D” keyboard shortcut to open the PivotTable and PivotChart Wizard to create a table from multiple sheets in Excel. This will add up the values in the 2 sheets and give us a summary of the 2 shops.

To do that, follow the steps given below.

Steps:

Firstly, press Alt + D and then press P.
Now, this will open the PivotTable and PivotChart Wizard box.
Next, select the Multiple consolidation ranges option.
Then, click on OK.

After that, select I will create the page fields option.
Again, click on Next.

Then, select cell range B4:D8 from the Shop 1 worksheet as the Range that you want to consolidate.
Now, click on Add.

Next, select cell range B4:D8 from the Shop 2 worksheet as the Range that you want to consolidate.
Again, click on Add.

After that, select the range of Shop 1 worksheet and set 1 as page fields number.
Further, type Shop 1 in the Field one box.

Next, select the range of Shop 2 worksheet and type Shop 2 in the Field one box.
Then, click on Next.

Now, select the New worksheet option as the destination where you want to put the PivotTable report.
Lastly, click on Finish.

Finally, you will get a pivot table created using the datasets from multiple sheets.

2. Use Relationships Feature to Create Table from Multiple Sheets in Excel

Now, suppose you have datasets in multiple sheets which do not contain the same fields and you want to summarize that information in one table. To do that, you can use the Relationship Feature in Excel.

Here, we have a dataset containing the Product ID, Name and Sales of a shop in the Sales Dataset worksheet.

Additionally, in the Cost Dataset worksheet, we have information about the Product Name, Quantity and Cost of making those products.

Now, we will show you how to create a table using these 2 worksheets which contain different fields.

Step 1: Creating Table

In the first step, we will create tables in individual worksheets using the datasets to create a relationship between them.

In the beginning, select cell range B4:D8 from the Sales Dataset worksheet.
Then, press Ctrl + T to create a table.

Now, the Create Table box will pop up and you can see that the cell range has already been selected.
After that, turn on My table has headers option.
Next, click on OK.

Then, to remove the filters from the table, go to the Data tab >> click on Sort & Filter >> click on Filter.

Further, to set a name for the table, go to the Table Design tab >> click on Properties >> type Sales in the Table Name box.

Similarly, create a table using the dataset in the Cost Dataset worksheet and remove filters from the fields.
After that, go to the Table Design tab >> click on Properties >> type Cost in the Table Name box.

Step 2: Inserting Pivot Table

Next, we will insert a Pivot Table to create a table from multiple sheets in Excel.

Firstly, in the Cost Dataset worksheet, go to the Insert tab >> click on PivotTable.

Now, the PivotTable from table or range box will open.
Then, insert Cost in the Table/Range box.
Next, select the New Worksheet option.
After that, turn on the Add this data to the Data Model option.
Lastly, click on OK.

Now, you will see that another worksheet has opened having the Cost table as the Active table.

Step 3: Using Relationships Feature

Finally, we will use the Relationship feature to create a table using the Cost and Sales tables from different worksheets.

Firstly, go to the PivotTable Analyze tab >> click on Calculations >> click on Relationships.

Now, the Manage Relationships box will appear.
Here, click on New.

Next, the Create Relationship box will open.
Then, select Cost as the Table, Sales as the Related Table, Product Name as the Column (Foreign), and Product Name as the Related Column (Primary).
Lastly, click on OK.

Now, you will see that a relationship has been created between the two tables.
Thus, click on Close.

Next, in the PivotTable Fields toolbox, go to the All option.
After that, click on Sales to expand the field names.
Then, drag the Product ID field in Rows and Sales in Values.

Now, click on Cost to expand the field names.
Afterward, drag the Quantity and Cost fields to the Values box.

Finally, you will get a table created by using datasets from multiple sheets in Excel.

3. Utilize Get Data Feature to Create Table in Excel

You can also use the Get Data feature form Data tab to create a table from multiple sheets.

Now, we will show you how to use this feature to create a table using the datasets in your Shop 1 and Shop 2 worksheets.

Here are the steps.

Steps:

To start with, create a table using the cell range B4:D4 from Shop 1 worksheet going through the steps shown in Method 2.
Then, go to the Table Design tab >> click on Properties >> type Shop_1 in the Table Name box.

Similarly, create a table using the cell range B4:D4 from Shop_2 worksheet and set the table name as Shop 2.

After that, go to the Data tab >> click on Get Data >> click on From Other Sources >> select Blank Query.

Now, a blank query will open in the Power Query Editor box.
Then, insert the following formula in the formula box and press Enter.

= Excel.CurrentWorkbook()

It will load all the Tables in the current workbook.
Next, to select your preferred tables click on the button shown below.

After that, we will select only the Shop_1 and Shop_2 table.
Then, click on OK.

Next, click on the button shown below.
Afterward, turn off the Use original column name as prefix option.
Lastly, click on OK.

Next, you will see that all the data from the 2 tables have been loaded together in a table.
Additionally, a new column has been added containing the Name of the Shops.

Further, if you want you can drag the Name column to make it the leftmost column.

After that, click on Close & Load >> select Close & load To.

Now, the Import Data box will open.
Then, select the Table option.
Afterward, select the New worksheet option as the destination of the table.
Lastly, click on OK.

Thus, you can create a table utilizing the Get Data feature in Excel.

4. Append Data in Excel Pivot Table from Multiple Sheets

In the final method, we will append data in the Excel Pivot Table using the Append Queries feature from multiple sheets. To know how to do this, go through the steps given below.

Step 1: Creating Connection

In the beginning, we will create a connection between the 2 tables to create a table from multiple sheets.

Firstly, create Shop_1 and Shop_ 2 tables by going through the steps shown in Method 3.
Then, select the Shop_1 table.
Next, go to the Data tab >> click on Form Table/Range.

Now, the Shop_1 table will open in the Power Query Editor.

After that, select Source from the Applied Steps box.

Then, select the Shop_1 table and Right-click on it.
Next, click on Duplicate.

This will create a duplicate table of Shop_1 named as Shop_1(2).

Next, select this table and click on Source from the Applied Steps box.

After that, change the Name to Shop_2 in the formula box.

Then, type Shop_2 as Name in the Query Settings.

Afterward, click on Close & Load >> click on Close & Load To.

Now, the Import Data box will open.
Next, select the Only Create Connection option.
Lastly, click on OK.

Thus, you can create a connection between these 2 tables.

Step 2: Using Append Queries Feature

Now, we will use the Append Queries feature to append data of the two tables.

To start with, click on any of these 2 tables from the Queries and Connections toolbox and open the Power Query Editor again.
After that, click on Combine >> click on Append Queries >> click on Append Queries as New.

Now, the Append box will appear.
Then, select the Two tables option.
Next, select Shop_1 as the First table and Shop_2 as the Second table.
Lastly, click on OK.

Now, you will see that data from the two tables have been appended together.

Further, set All_Stores as the Query Name.

After that, click on Close & Load >> click on Close & Load To.

Now, in the Import Data box, select the Table option.
Then, select the New worksheet option.
Finally, click on OK.

That’s it. This is how you can create a table by appending data from multiple sheets.

Download Practice Workbook

You can download the workbook to practice yourself.

Creating Table from Multiple Sheets.xlsx

Conclusion

So, in this article, we have shown you 4 ways to create a table from multiple sheets in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. Thank you!

<< Go Back to Make a Table | Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!