'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Combine Multiple Files from a Folder in Excel for Mac (Power Query)
Question details
Users need a workflow to import and combine multiple files from a specific folder into a single dataset, as the native connector is absent on macOS.
- Product
- Excel
- Device & OS
- Mac
- Scenario
- Attempting to consolidate data by importing multiple source files stored in a single folder into an Excel workbook.
- Observed behavior
- The standard Power Query 'Get Data From Folder' connector available on Windows is missing in the Mac version of Excel, blocking users from combining files natively.
Verify that you have the latest version of Excel for Mac installed, as Microsoft frequently updates Power Query functionality for Microsoft 365 subscribers.
Use VBA Macros to Automate Importing and Combining Files
Since the native Power Query folder connector is missing, you can use a VBA macro to loop through a directory and merge your files.
VBA (Visual Basic for Applications) provides a powerful workaround to automate repetitive tasks. By writing a script that targets a specific folder on your Mac, you can instruct Excel to open each file, copy the data, and paste it sequentially into a master worksheet.
Note that macOS file path structures differ from Windows, so any existing Windows VBA scripts will need their file paths updated to the Mac format (e.g., using Macintosh HD or POSIX paths).
Launch Excel on your Mac, open your destination workbook, and press Option + F11 to open the Visual Basic Editor.
In the Project Explorer pane on the left, right-click your workbook name, select 'Insert', and click 'Module'.
Paste a VBA script designed for Mac that loops through a folder to copy and append data. Close the editor, go to the 'Developer' tab, click 'Macros', select your new macro, and click 'Run'.
Submit a Feature Request to Microsoft
Requesting the missing connector directly from the developers helps prioritize bringing full Power Query parity to macOS.
Discover a Lightweight and Highly Compatible Spreadsheet Alternative
If feature discrepancies between Mac and Windows versions of Office disrupt your productivity, consider trying WPS Office. It delivers a fast, seamless, and familiar spreadsheet experience with robust compatibility across platforms, saving you from heavy subscription fees.
- 1. Download and Install: Visit the official WPS Office website to download the macOS version and follow the simple installation prompts.
- 2. Open WPS Spreadsheets: Launch the application and select 'Spreadsheets' from the main workspace.
- 3. Edit Your Excel Files: Drag and drop your existing .xlsx files directly into the program to continue your work without formatting loss.
Frequently Asked Questions
Is Power Query fully supported in Excel for Mac?
Power Query is partially supported in Excel for Mac. While you can refresh many queries and use the Power Query Editor to transform data, several data connectors—including the 'From Folder' connector—are currently unavailable compared to the Windows version.
Can I use Power Automate instead to combine files on Mac?
Yes, as a cloud-based workaround, you can use Power Automate or Office Scripts online to merge data from multiple Excel files hosted on OneDrive or SharePoint, completely bypassing the limitations of the Mac desktop app.
Will Microsoft ever add the 'From Folder' connector to Mac?
Microsoft continuously rolls out updates to improve feature parity between Mac and Windows. Submitting feedback through the Excel Help menu is the best way to let the development team know this feature is highly requested.
Does WPS Office on Mac support combining multiple files?
WPS Office offers robust spreadsheet capabilities and data manipulation tools. While advanced data modeling is handled differently, its lightweight interface and VBA support provide excellent alternatives for users looking to manage multiple datasets effortlessly.
