Why does double-clicking a .zip file in Excel just show an error? Why does dragging it into a workbook do nothing? Why did your IT colleague say ‘just use Power Query’ but not tell you how?
The answer is simple: Excel doesn’t read ZIP files. It never has. Not in 2007, not in Microsoft 365, not even with VBA add-ins. That’s not a bug—it’s by design. And once you accept that, everything else clicks.
The Problem
You get a weekly email from Procurement with Q3_Vendor_Data.zip. Inside are five CSVs: suppliers.csv, invoices_2024-09.csv, returns_q3.csv, payments_oct.csv, and notes.txt. You need to combine them into one dashboard in Sheet1. But when you try to open the ZIP in Excel, you get:
"We can't open this file because it's in a format that Excel doesn't recognize."
So you manually extract each file, rename them, drag them in—and forget notes.txt. Then you realize two CSVs have inconsistent headers. By 10:47 a.m., your pivot table breaks because payments_oct.csv has no date column (it uses payment_dt instead of date). Sound familiar?
| File Name | Size | Last Modified | Status in Excel |
|---|---|---|---|
| suppliers.csv | 24 KB | 2024-10-02 | Opened (but missing header row) |
| invoices_2024-09.csv | 187 KB | 2024-10-02 | Opened (extra blank rows at top) |
| returns_q3.csv | 62 KB | 2024-10-02 | Failed — encoding error |
| payments_oct.csv | 113 KB | 2024-10-02 | Opened (wrong column order) |
| notes.txt | 3 KB | 2024-10-02 | Ignored — Excel won’t touch it |
The Solution
Forget opening the ZIP. Instead, use Excel’s built-in Get Data → From File → From Folder — and let it handle extraction *and* parsing in one go. This works even if the ZIP contains mixed formats (CSV, TXT, XLSX). Here’s exactly what to do:
- Right-click the ZIP file in File Explorer → Copy (or press Ctrl+C)
- In Excel, go to the Data tab → click Get Data → From File → From Folder
- Paste the folder path where the ZIP lives (e.g.,
C:\Reports\Q3\) → click OK - In the Navigator pane, find your ZIP file in the list — don’t click the checkbox yet. Click the small arrow ▸ next to it
- Select Content → click Transform Data
- In Power Query Editor, filter the
Namecolumn to include only*.csvand*.txt— uncheckFolder PathandDate accessed - Select all rows → right-click → Combine Binaries → choose CSV as the file type (even for TXT — it works)
- Click Close & Load. Done.
This loads every file into one table, auto-detected headers, consistent encoding, and proper delimiters—even notes.txt appears as plain text in column A.
| Supplier ID | Name | Invoice No | Amount | Payment Date |
|---|---|---|---|---|
| SUP-782 | Acme Corp | INV-9281 | $14,250.00 | 2024-09-15 |
| SUP-319 | Nexus Labs | INV-9282 | $8,760.50 | 2024-09-18 |
| SUP-782 | Acme Corp | INV-9283 | $22,100.00 | 2024-09-22 |
| SUP-504 | Stellar Systems | INV-9284 | $11,430.75 | 2024-09-25 |
| SUP-319 | Nexus Labs | INV-9285 | $6,980.20 | 2024-09-29 |
Going Further
You don’t need to extract anything manually—but sometimes you want *only* one file from the ZIP. Say you only need returns_q3.csv. In step 4 above, instead of clicking ▸ and choosing Content, double-click the ZIP file name itself. Then in the list of extracted files, filter Name to returns_q3.csv → right-click → Extract Files. It drops the raw CSV into your temp folder. Use that if you need to hand-edit before loading.
Another trick: If the ZIP contains Excel files (.xlsx), skip Combine Binaries. Instead, after expanding Content, add a custom column: =Excel.Workbook([Content]). Then expand the Data column to pull sheets directly—no manual copy-paste.
And here’s the counterintuitive part: Don’t unzip first. Windows Explorer’s built-in ZIP handler adds hidden metadata that can break Power Query’s binary detection. Let Excel handle the decompression internally—it’s more stable.
When NOT to Use This
This method fails silently in three cases:
- Password-protected ZIPs: Excel won’t prompt for a password. You’ll get “file access denied” or blank content. Extract manually using 7-Zip or WinZip first.
- Nested ZIPs (ZIP inside ZIP): Power Query only extracts one level deep. If
Q3_Vendor_Data.zipcontainsraw_data.zip, you’ll see it as a binary blob—not files inside it. - Files over 2 GB: Excel’s binary parser chokes around 2.1 GB. Split large archives externally before importing.
- Non-UTF-8 encoded text files: If
notes.txtuses Shift-JIS or ISO-8859-1, you’ll get garbled characters. In Power Query, select the column → Transform → Text Encoding → choose the correct one *before* combining.
Also—don’t try this on network drives mapped as Z:\. Use full UNC paths like \\server\share\Q3\ instead. Mapped drives often timeout during binary reads.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Power Query Editor | Alt → A → M → P | Faster than hunting through the Data tab |
| Refresh all queries | Alt → A → R | Essential after updating the ZIP |
| Filter column in PQ | Ctrl + Shift + F | Works in Power Query Editor only |
| Close & Load | Ctrl + Alt + L | Saves 3 clicks every time |
