How to Create a PivotTable for Multiple Sheets in Excel

A PivotTable in Excel creates a single table of information from two or more tables in separate sheets or files. You can use PivotTables to see all your data together and gain new insights.

While you can use several methods, the Power Query add-in is the best option to combine tables with the same column structure. Follow these steps to make a single PivotTable from different sheets:

1. Convert Plain Text Tables to Excel Table Format

Complete this process for each of the tables you want to combine:

1. Click on the table and press the “Control” key (Ctrl) and the “T” button.
2. The “Create Table” window will pop up — select “OK.”
3. In the “Table Design” ribbon, click on the “Table Name” page field and give your table a recognizable name.

2. Bring the First Table into the Power Query

Use Power Query to append — or stack — the two tables into a single PivotTable. Click into the first table, then click the “Data” tab and the “From Sheet” option in the “Get & Transform Data” section. A Power Query Editor window should appear.

3. Track Information from Each Table

You may want to add another column to the PivotTable to label those items with tracking information. When you’ve created the PivotTable, it will help you identify which items came from which table.

Follow these steps to add tracking information:

1. Click “Add Column,” then “Custom Column.”
2. In the dialog box, give your new column a meaningful name. Create an identifying label in the large box.
3. In Query Settings, change the data type for the new column to “text.”

4. Bring the Second Table into the Power Query

You have two options to get your second table in Power Query. Repeat Step Two or make a copy of your first table query and change the identifying information to fit the second table.

5. Close the Queries

Load your table queries into Excel. Click “Close & Load,” then “Close & Load To…” Select the “Only Create Connection” option in the dialog box.

6. Append the Tables into a Single PivotTable

Move these two tables into one that drives the PivotTable:

1. Click “Data,” then “Get Data.”
2. Under “Combine Queries,” click “Append.”
3. In the dialog box, select “two tables” and your table names in the dropdown box.
4. Click “OK” to complete, and you should see “Append1” in your Queries list.

7. Output to an Excel Table

Output the appended data to an Excel table by:

1. Clicking “Close & Load,” then “Close & Load…”
2. Selecting “Table” and “New worksheet.
3. Clicking “OK” to complete.

Learn More About Our Managed IT Services

Agio has managed IT services for proactive monitoring and 24/7 user support. Ready for professional IT solutions? Contact us today.