How to Automatically Import Values from Monthly Excel Workbooks
Question details
The user needs to automatically import and consolidate cell values from multiple similarly structured monthly Excel workbooks into a main summary file.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating monthly reports or data sets into a master summary sheet without manually copying and pasting.
- Observed behavior
- Hyperlinks alone only open the files but do not pull cell values. Automated extraction methods like formulas, Power Query, or VBA are required to retrieve the data.
Ensure all your monthly workbooks have an identical structure (same column headers and data types) and are saved together in a single dedicated folder before attempting to combine them.
Use Power Query to Combine Monthly Workbooks
The most robust and automated method for importing data from multiple monthly files saved in the same folder.
Power Query allows you to point Excel to a specific folder and automatically combine all files within it. When a new monthly report is added to the folder, you can simply refresh the query to update your summary.
Place all the monthly Excel workbooks you want to import into a single folder on your computer.
Open a blank workbook. Go to the Data tab on the ribbon, click 'Get Data' (or 'New Query'), select 'From File', and then choose 'From Folder'.
Browse to your dedicated folder and click 'Open'. In the preview window that appears, click the 'Combine' dropdown at the bottom and select 'Combine & Transform Data'.
Select the specific sheet or table format that matches across all your monthly files, then click 'OK' to open the Power Query Editor.
Click 'Close & Load' in the top-left corner of the Home tab. The consolidated data from all monthly workbooks will now populate in your master sheet.
Use External Reference Formulas
Best for a small, unchanging list of monthly files where you only need to extract specific cell values.
Use VBA Macros for Dynamic Sheet Generation
Ideal if you need to dynamically generate subsidiary sheets or loop through files based on variable conditions.
Consolidate Multiple Workbooks Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data consolidation features, extensive formula support, and seamless compatibility with Microsoft Excel files, allowing you to merge monthly reports effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank workbook to serve as your master summary file.
- 2. Access the Consolidate tool: Navigate to the Data tab on the top ribbon and click the 'Consolidate' button.
- 3. Add your monthly files: Click 'Browse' to select your monthly workbooks, highlight the data ranges you want to import, and click 'Add' to include them in the consolidation list.
- 4. Merge the data: Choose your preferred function (like Sum or Average), check 'Create links to source data' if you want automatic updates, and click 'OK' to finish.
Frequently Asked Questions
Can a standard hyperlink pull data from another workbook automatically?
No, inserting a hyperlink only provides a clickable shortcut to open the target file. It cannot extract or display cell values. You must use formulas, Power Query, or VBA to import actual data.
Do I need to keep the monthly workbooks open for my formulas to work?
It depends on the formula used. Basic external references (e.g., =[January.xlsx]Sheet1!A1) will update even if the source file is closed. However, certain volatile functions like INDIRECT require the referenced workbook to be open to return a value.
What happens if I change the folder name containing my monthly files?
If you used Power Query or hard-coded VBA paths to connect to a specific folder, changing the folder's name or location will break the connection. You will need to open the Data Source Settings in Power Query or update your VBA script to point to the new directory.
How can I ensure my summary updates automatically when new monthly files are added?
The easiest way is to use Power Query's 'From Folder' method. Once configured, you simply place your new monthly file into the target folder, open your main summary workbook, go to the Data tab, and click 'Refresh All'.
