How to Combine Two Excel Workbooks on Mac for Pivot Tables
Question details
The user needs a method to merge data from two separate Excel workbooks on a Mac so that the combined data can serve as a dynamic source for a PivotTable.
- Product
- Excel for Mac
- Device & OS
- Mac
- Scenario
- Creating a unified PivotTable from multiple external data sources when standard Power Query features might be limited.
- Observed behavior
- Excel for Mac frequently lacks the full suite of Power Query features found in the Windows version, preventing standard workbook combination techniques.
Ensure both of your source Excel workbooks are saved in the standard .xlsx format and check if your version of Microsoft 365 for Mac supports dynamic array functions like VSTACK.
Use Get Data and VSTACK to Combine Workbooks
Import the data from both workbooks using available Get Data features and stack them dynamically using the VSTACK formula to create a single PivotTable source.
While full Power Query is limited on Mac, you can leverage the Get Data feature alongside dynamic array functions to consolidate your tables. The VSTACK function takes multiple arrays and stacks them vertically into a single spilled range.
Open a new master workbook. Go to the Data tab, click on Get Data (or Get Data (Power Query) if available), select Excel Workbook, and load the first query into your workbook.
Repeat the previous step to import the second workbook's data. Load this second query into a separate sheet or designated area.
In a clean worksheet, select a starting cell (e.g., I2) and enter the formula =VSTACK(Query1, Query2), replacing 'Query1' and 'Query2' with the actual table names or ranges you imported. Press Return to spill the combined data.
Go to Insert > PivotTable. In the Table/Range field, reference your spilled array by typing the starting cell followed by a hashtag (e.g., Sheet1!$I$2#). Click OK to build your dynamic PivotTable.
Consolidate Data Manually into a Master Table
If dynamic arrays or Get Data are completely unavailable on your older Mac version, manually appending the data is the most reliable fallback.
Seamlessly Analyze Data with WPS Spreadsheet
If Mac Excel's feature limitations and missing Power Query tools are slowing down your workflow, WPS Office offers a powerful, free alternative. It provides high compatibility with Microsoft formats, a familiar user interface, and robust data consolidation and PivotTable features.
- 1. Download and Install WPS Office: Visit the official WPS website to download the Mac version. Follow the prompts to install and launch the application.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and easily open your existing .xlsx files to begin working on your data.
- 3. Generate a PivotTable: Highlight your consolidated data, navigate to the Insert tab, and select PivotTable to quickly build dynamic data reports.
Frequently Asked Questions
Why is Power Query missing from my Excel for Mac?
Microsoft has historically limited full Power Query functionality on the Mac version of Excel. While recent updates have introduced more 'Get Data' capabilities, full feature parity with Windows usually requires updating to the latest Microsoft 365 version or joining the Office Insider program.
What does the hashtag (#) mean in a cell reference like Sheet1!$I$2#?
The hashtag denotes a spilled range in modern Excel. It tells the program to dynamically reference the entire array of data generated by formulas like VSTACK. If the underlying data expands or shrinks, the PivotTable source updates automatically.
Can I combine more than two workbooks using the VSTACK function?
Yes. The VSTACK function supports multiple arrays. You can append as many tables or ranges as needed by separating them with commas, for example: =VSTACK(Table1, Table2, Table3).
