It’s 4:47 PM on Friday. Your manager just asked for a consolidated sales report by 5. You have 12 spreadsheets open — Sales_Q1_Acme.xlsx, Sales_Q1_BrightCo.xlsx, Sales_Q1_Nexus.xlsx, plus eight more — all in C:\Reports\Q1_2024\. None use the same tab name. Three have headers in row 2. One uses commas instead of periods in numbers. You’ve never touched Power Query.
Quick Answer
Use Excel’s built-in Get Data > From File > From Folder — then filter for .xlsx, promote headers, and combine. It works even when sheets have inconsistent layouts, and you can refresh with Ctrl+Alt+F5. No add-ins, no coding, no renaming files.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Get Data > From Folder | 1. Point to folder 2. Filter by extension 3. Drill into Content column 4. Combine & load |
10–100 files, mixed sheet names, irregular headers | Fails if files are password-protected or open elsewhere |
| Power Query: Append Queries | 1. Load each file individually 2. Standardize column names 3. Use Append Queries as New |
Files with identical structure but different ranges (e.g., A1:D100) | Manual per-file setup; no auto-refresh across folders |
| Excel 365: =TEXTJOIN + INDIRECT (volatile) | 1. List file paths in A2:A13 2. Build formula referencing '[file.xlsx]Sheet1'!B23. Wrap in TEXTJOIN + ARRAYFORMULA |
Small, static sets (≤5 files), same sheet name & cell layout | Breaks if source files move; recalculates every edit |
| VBA Loop + Consolidate | 1. Open each workbook 2. Copy used range from Sheet1 3. Paste values below last row in master |
Legacy Excel versions (2010/2013), no Power Query access | Fails silently if any file is read-only or missing Sheet1 |
| Microsoft Query (ODBC) | 1. Register folder as data source 2. Write SQL SELECT across .xlsx files 3. Import results |
Advanced users comfortable with basic SQL syntax | Requires 64-bit ACE OLEDB driver; fails on M1 Macs |
Method 1 Deep Dive
We’ll use Get Data > From Folder — the fastest path for messy, real-world files. Start with this folder:
C:\Reports\Q1_2024\Sales_Q1_Acme.xlsx→ Sheet Revenue, headers in Row 1, data from A1:E42C:\Reports\Q1_2024\Sales_Q1_BrightCo.xlsx→ Sheet Data, headers in Row 2, data from A2:E38C:\Reports\Q1_2024\Sales_Q1_Nexus.xlsx→ Sheet Summary, headers in Row 1, data from B1:F51
Go to Data tab > Get Data > From File > From Folder. Browse to C:\Reports\Q1_2024\. Click OK. You’ll see a table with columns: Name, Extension, Date modified, and Content. Filter Extension to show only .xlsx. Right-click the Content column header → Transform > Combine Binaries. In the dialog, choose Combine using first file’s headers. Click OK.
Now — here’s the counterintuitive part: don’t click “Load” yet. Instead, click Transform Data. In Power Query Editor, you’ll see a list of tables — one per file. Click the double-arrow icon next to Content. Check Use original column names as headers. Uncheck Include hidden items. Click OK. You’ll get a merged table with an extra column: Source.Name. That tells you which file each row came from — critical for auditing. Rename it to File_Source. Then go to Home > Close & Load.
The result lands in a new worksheet starting at A1. Column A is File_Source, then your data columns. If Acme’s file has Region, Product, Amount, and Nexus has Area, Item, Value, Power Query aligns them by position — not name. So make sure all files list columns in the same order. If they don’t, use Transform > Use First Row as Headers before combining.
Method 2 Deep Dive
When files share identical structure — same sheet name, same header row, same column order — Append Queries gives tighter control. Say all 12 files have a sheet named Q1_Sales, with headers in Row 1 and data in A1:D100.
Start fresh: Data > Get Data > From File > From Workbook. Select Sales_Q1_Acme.xlsx. In Navigator, check Q1_Sales and click Transform Data. In Power Query Editor, remove blank rows (Home > Remove Rows > Remove Blank Rows). Promote headers if needed (Transform > Use First Row as Headers). Now close and load to Connection Only (don’t create a worksheet). Repeat for Sales_Q1_BrightCo.xlsx — same steps, same connection-only load.
Then: Home > Combine Queries > Append Queries. Choose Three or more tables. Select all 12 query names (they’ll appear as Q1_Sales (1), Q1_Sales (2), etc.). Click OK. You’ll get one merged query. Add a custom column: = [Source.Name] — but wait! You didn’t capture filename earlier. So go back: before appending, edit each query and add a custom column File_Name with value "Acme", "BrightCo", etc. That’s the manual step — but it pays off when you need traceability. Final output loads to Sheet1!A1 with 1,247 rows — clean, sortable, and fully refreshable.
Cheat Sheet
| Action | Keyboard Shortcut | Notes |
|---|---|---|
| Open Power Query Editor | Alt + A + M |
Hold Alt, press A, release, press M |
| Refresh all queries | Ctrl + Alt + F5 |
Works even if Power Query window is closed |
| Promote first row to headers | Alt + H + P + H |
In Power Query Editor only |
| Filter column by extension | Click ▼ next to Extension → uncheck (Select All) → check .xlsx |
No shortcut — but double-clicking the filter icon clears it fast |
| Load merged result to existing sheet | In Close & Load dropdown → Close & Load To… → select Existing worksheet → $A$1 |
Avoids overwriting formulas in adjacent columns |
