Combine Multiple Sheets into One Sheet

 



Topic: Combine Multiple Sheets into One Sheet:

Using Power Query (No Formula)

✅ Pre-Requisites

  • Your Excel workbook contains multiple structured sheets with the same column headers and layout.

  • Each sheet contains data starting from row 1 with proper headers.

🧭 Step-by-Step Process Flow

🔹 Step 1: Go to the Data Tab

  • Open your Excel workbook.

  • Click on the “Data” tab on the ribbon at the top.

🔹 Step 2: Get Data from File

  • Click on “Get Data” → Hover over “From File” → Select “From Workbook”.

🔹 Step 3: Browse and Select Workbook

  • A file dialog will appear.

  • Select the same workbook you're working in (or another Excel workbook containing the sheets you want to combine).

  • Click Import.

🔹 Step 4: Navigator Window

  • In the Navigator window, you will see a list of all sheets and tables in that workbook.

  • Select the checkboxes for the sheets you want to combine (e.g., Sheet1, Sheet2, Sheet3...).

  • Click “Transform Data” (this opens Power Query Editor).

🔹 Step 5: Power Query Editor Opens

  • You are now in the Power Query Editor.

  • You will see all selected sheets loaded as separate queries in the Queries pane (on the left).

🔹 Step 6: Append Queries as New

  • In the top ribbon, go to the “Home” tab.

  • Click on “Append Queries” → Select “Append Queries as New”.

🔹 Step 7: Append Multiple Sheets

  • In the dialog box that opens:

    • Select “Three or more tables”.

    • Use the interface to add all sheet queries to the list.

    • Click OK.

🔹 Step 8: Review the Combined Table

  • A new query called something like “Append1” will appear.

  • This is your combined data from all selected sheets.

  • You can preview, rename, or filter as needed in this window.

🔹 Step 9: Close and Load

  • In the Power Query ribbon, click “Home” → “Close & Load”.

  • The combined data will now be loaded to a new sheet in your workbook.

✅ Final Output

A new worksheet with a single table that combines all selected sheets from your Excel file.

🔄 To Refresh Later

  • If you change data in the original sheets, go to the Data tab and click “Refresh All” to update the combined sheet.