How to Combine Multiple Excel Files Using Power Query or VSTACK on Mac
Question details
The user needs to combine five similarly structured Excel files on a Mac, with new files added monthly, but the Power Query folder connector is unavailable.
- Product
- Excel for Mac
- Device & OS
- Mac
- Scenario
- Appending rows from multiple matching spreadsheet files into a single master dataset that updates monthly.
- Observed behavior
- The source data is in rows, but combining them manually is tedious. The user is looking for a dynamic solution using Power Query or the VSTACK function, especially when standard connectors are missing on Mac.
Ensure all the Excel files you want to combine have identical column structures and headers, and save them in the same folder for easier management.
Use the VSTACK Function to Combine Data (Microsoft 365)
VSTACK is an excellent alternative for Mac users who might not have the Power Query 'From Folder' connector, as it vertically appends arrays across different files.
This method requires Microsoft 365. It dynamically stacks the data, but you will need to update the formula manually when a new file is added next month.
Open a new or existing master Excel workbook where you want the combined data to appear.
Type =VSTACK( in the destination cell where the data should start.
Select the header and data range from the first file (e.g., '[File1.xlsx]Sheet1'!A1:H20).
Type a comma, then select the data range (excluding headers) from the second file (e.g., '[File2.xlsx]Sheet1'!A2:H20). Repeat this process for all five files.
Close the parenthesis ) and press Enter to dynamically combine the rows.
Use Power Query 'From Folder' Connector (If Available)
If your version of Excel for Mac supports it, the 'From Folder' connector is the most robust way to handle monthly file additions automatically.
Merge Multiple Spreadsheets Instantly with WPS Office
Skip the complex Power Query setups and VSTACK formulas. WPS Spreadsheet includes a built-in 'Merge Workbooks' tool that lets you combine multiple files into one sheet with just a few clicks, perfect for monthly reports.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and navigate to the 'Data' tab on the top ribbon.
- 2. Access Merge Tool: Click on 'Merge' and select 'Merge Multiple Workbooks into a Single Workbook' from the dropdown.
- 3. Add Monthly Files: Click 'Add Files' to select the five or more Excel files you want to combine from your Mac.
- 4. Execute Merge: Choose whether to merge them into multiple sheets or a single worksheet, then click 'Merge' to finalize.
Frequently Asked Questions
Does VSTACK automatically update when I add a new file to the folder?
No, VSTACK requires you to manually edit the formula to include the new file's data range. Only Power Query's 'From Folder' connector can automatically fetch new files dropped into a designated directory.
Why can't I find the 'From Folder' Power Query option on my Mac?
Excel for Mac does not yet have full feature parity with Excel for Windows regarding Power Query. The 'From Folder' connector is frequently missing or limited depending on your specific Microsoft 365 subscription and update channel.
Can I combine files with different column headers using VSTACK?
VSTACK simply stacks arrays vertically regardless of headers. If your columns are in different orders across the five files, the data will not align correctly. You must ensure identical column structures before using the VSTACK function.
