How to Combine More Than 100 Excel Files on a Mac
Question details
The user needs a method to quickly append or merge more than 100 Excel workbooks into a single worksheet on a Mac computer.
- Product
- Excel for Mac
- Device & OS
- Mac
- Scenario
- Consolidating a large number of separate Excel files into one master worksheet for reporting or data analysis.
- Observed behavior
- The 'Get Data' feature in Excel for Mac restricts the user to selecting only one workbook at a time, preventing bulk import and forcing manual copy-pasting.
Ensure all the Excel files you want to combine are saved in the same local folder on your Mac and share the exact same column structure to prevent mismatched data.
Use macOS Automator to Combine Excel Files
If you have older Excel components installed on your Mac, you can use the built-in Automator app to create a workflow that merges multiple Excel files without any coding.
This method utilizes a specific 'Combine Excel Files' action that was included with older versions of Office for Mac (such as Excel 2011). If this action is present in your system library, it is the fastest native way to append files.
Open the Automator app on your Mac. Select 'New Document' and then choose 'Workflow' from the document types.
In the left sidebar, navigate to 'Library' > 'Files and Folders'. Drag and drop the 'Ask for Finder Items' action into the workflow space on the right. Check the box for 'Allow Multiple Selection'.
Navigate to 'Library' > 'Documents' in the sidebar. Locate 'Combine Excel Files' and drag it below your previous action in the workflow.
Click the 'Run' button in the top right corner. A Finder dialog will appear; hold down the Command key to select your 100+ Excel workbooks, then click 'Choose' to merge them into one file.
Use a VBA Macro to Loop and Merge Files
If Automator actions are unavailable, you can write a VBA script in Excel to automatically loop through a folder and copy the data from each workbook into a master sheet.
Easily Combine Multiple Excel Files with WPS Office
Skip the complex Automator setups and VBA coding. WPS Office includes a dedicated built-in tool specifically designed to merge multiple workbooks into a single file in just a few clicks. It's fully compatible with Microsoft Excel formats and handles large numbers of files effortlessly.
- 1. Open WPS Spreadsheets: Launch WPS Office on your Mac and open a new blank spreadsheet.
- 2. Access the Merge Tool: Navigate to the 'Data' tab on the top ribbon, click on 'Merge', and select 'Merge Multiple Workbooks into a Workbook'.
- 3. Add Your Files: Click 'Add Files' or 'Add Folder' in the prompt window and select all 100+ of the Excel files you wish to combine.
- 4. Start Merging: Review your selection and click 'Start Merging'. WPS Office will automatically extract and combine the data into your master spreadsheet.
Frequently Asked Questions
Why does Excel for Mac restrict selecting multiple files in Get Data?
The Power Query (Get Data) functionality in Excel for Mac has historical limitations compared to the Windows version. While Microsoft has been updating it, full 'From Folder' queries and bulk selections are often restricted or missing, preventing users from importing multiple workbooks simultaneously.
What should I do if my Mac doesn't have the 'Combine Excel Files' Automator action?
If the action is missing in Automator's library under Documents, it means your system lacks the older Office components that originally provided it. You will need to rely on VBA macros, terminal scripts (for CSVs), or use alternative software with built-in merging capabilities like WPS Office.
Can I use the Terminal to combine these files instead?
If your files are in .csv format, you can easily combine them using the Mac Terminal with the command 'cat *.csv > combined.csv'. However, this Terminal command will corrupt standard .xlsx workbooks, so it should only be used for plain text CSV data.
