How to Combine Excel Tables Without Power Pivot in Microsoft 365
Question details
The user needs to merge multiple Excel data ranges with varying column structures into a single PivotTable without utilizing Power Pivot or Power View.
- Product
- Microsoft 365 Excel
- Device & OS
- not provided
- Scenario
- Preparing consolidated data from disparate tables (where matching columns appear in different positions and lack unique IDs) to generate summarized PivotTable subtotals for specific categories like Fund, Asset Class, and Currency.
- Observed behavior
- Power Pivot and Power View are unavailable, preventing the standard approach of building relationships in the Data Model.
Ensure your standard data ranges are converted into official Excel Tables (select the range and press Ctrl+T) to guarantee seamless integration and automatic updates when imported into Power Query.
Use Power Query (Get & Transform) to Append Tables
Utilize Excel's built-in Get & Transform feature to consolidate tables with varying column layouts into one normalized dataset for your PivotTable.
Power Query automatically aligns columns by their header names, even if they appear in different sequences across your tables. This makes it the ideal workaround for merging tables without unique IDs or identical layouts.
Click anywhere inside your first Excel Table, navigate to the 'Data' tab on the ribbon, and select 'From Table/Range' to open the Power Query Editor.
From the Power Query Editor, click the 'Home' tab, select 'New Source' > 'File' > 'Excel Workbook', and import the remaining tables.
On the Home tab of the Power Query Editor, click the dropdown arrow next to 'Append Queries' and select 'Append Queries as New'. Choose 'Three or more tables' and add all necessary tables to the 'Tables to append' list.
Once the data is combined, click 'Close & Load To...' from the Home tab. In the Import Data dialog box, select 'PivotTable Report' and choose where you want it placed.
Use the PivotTable Fields pane to drag your required fields (Fund, Asset Class, Currency) into the Rows and Values areas to generate your subtotals.
Experience Seamless Data Consolidation in WPS Office
If you're dealing with missing add-ins or overly complex data modeling tools in Microsoft 365, consider switching to WPS Office. It provides robust built-in data consolidation and PivotTable features without requiring heavy extensions.
- 1. Download and Install: Get WPS Office for free from the official website and complete the quick installation.
- 2. Open Your Workbooks: Open your existing Excel workbooks containing the disparate data tables effortlessly in WPS Spreadsheet.
- 3. Analyze the Data: Utilize WPS Spreadsheet's native Data Consolidate and Insert PivotTable features to quickly summarize your financial categories.
Frequently Asked Questions
Do the columns need to be in the exact same order to append them in Power Query?
No, Power Query matches columns based strictly on their header names, not their position. As long as the column names are spelled identically, they will align perfectly in the appended table.
What happens if a column exists in Table B but is missing from Table A?
When appending the queries, Power Query includes all unique columns from all tables. For any rows originating from Table A that lack that specific column, Power Query will automatically insert a 'null' value.
Can I combine regular data ranges instead of formatted Excel Tables?
While it is technically possible using named ranges, it is highly recommended to convert your data into official Excel Tables (Ctrl+T) first. This ensures any newly added rows in the future are automatically detected during a refresh.
How do I update the combined PivotTable when my source tables change?
Simply right-click anywhere inside the generated PivotTable and select 'Refresh'. Power Query will automatically re-run the append process in the background and update the PivotTable with the latest data.
