Excel hangs for minutes opening large CSV files — fix

You're not alone — Excel choking on a CSV is infuriating

You double-click a 200 MB CSV file, Excel pops up, shows "Not Responding" for 90 seconds, then finally loads but is sluggish. Or worse, it never recovers and you kill it via Task Manager. What's actually happening here is that Excel is trying to parse the entire file as a single worksheet using its built-in text import engine — which is single-threaded and memory-inefficient for anything over maybe 30 MB.

The fix: stop double-clicking, use Power Query or the Text Import Wizard

The real fix is not to open the CSV directly. Instead, import it properly. Here's the exact steps for Excel 2019 and Microsoft 365:

1. Open a blank workbook.
2. Go to the Data tab → Get Data → From File → From Text/CSV.
3. Select your CSV file. Power Query will preview the first 200 rows — that's fast.
4. Click Load (or Load To if you want a PivotTable or just the connection).

That's it. The file loads in seconds instead of minutes. Why? Power Query doesn't load the entire file into memory at once. It streams data into a compressed columnar store (the Data Model) or loads only what's needed. It also auto-detects delimiters, encodings, and data types, so you avoid Excel guessing wrong.

Why the double-click method is broken

When you double-click a .csv file, Excel calls its legacy .xls-based importer. That importer reads the file line by line, splits on commas, and guesses data types for each column. For a 200 MB CSV with 500,000 rows, that's 500,000 iterations of type-guessing logic — and it's all single-threaded. Plus, Excel has to load the entire sheet into RAM before it shows anything. If your CSV has mixed types (e.g., "N/A" in a column of numbers), Excel scans the whole column twice: once to guess types, once to convert. That doubles the time.

The Power Query approach skips the guesswork: it reads the file in chunks and builds a preview incrementally. The actual load happens in the background, and the worksheet only holds the result. This is especially important on 32-bit Excel (still common in some orgs), which has a 2 GB memory ceiling. A 500 MB CSV can trigger OOM crashes there.

Less common variations of this problem

1. Excel still hangs after Power Query import

If the import finishes but Excel is sluggish afterward, check if the CSV has thousands of columns. Excel 2019 and 365 support 16,384 columns per sheet, but performance tanks past 200 columns. The fix: only load the columns you need. In Power Query's preview, use Choose Columns to drop irrelevant ones.

2. The CSV has UTF-8 BOM or weird line endings

Some CSVs from Linux or Mac apps use LF instead of CRLF. Excel's old importer sometimes misdetects the encoding as ANSI, mangling non-ASCII characters. Power Query handles this: in the import dialog, you can set File Origin to UTF-8 and Line breaks to LF. If you're stuck on an old Excel version without Power Query, use Notepad++ to convert line endings first.

3. The CSV is over 1 GB

Excel (even 64-bit) starts struggling hard beyond 1 GB. At that point, don't load into Excel at all. Use a dedicated tool like CSVed or R or Python pandas to filter and aggregate, then export a smaller CSV for Excel. I've seen people try to open a 4 GB server log in Excel — it's not going to work.

4. Excel hangs during "Preparing workbook" after CSV import

This usually means Excel is calculating formulas or refreshing external data. If your CSV import included a load to the Data Model, disable automatic recalculation under Formulas → Calculation Options → Manual. Or uncheck Refresh this connection on file open in the query properties.

Prevention: set up your imports right from the start

Don't make a habit of double-clicking CSVs. Instead:

• Keep a blank template workbook with Power Query queries already configured for your common CSV sources. Save it as a .xltx file. Then you just double-click that, refresh, and go.
• For CSVs you receive regularly (daily exports, logs), set up a Data → Get Data → From File → From Folder to combine all files, then load only summary stats — this avoids loading raw data entirely.
• If you must share the CSV externally, consider converting it to an Excel binary format (.xlsb) first — that's 5x faster to open than .csv because Excel stores it in native columnar format.
• And if you're on a team, push back on whoever generates those huge CSVs. Ask for filtered exports or pre-aggregated reports. Most database tools can output a summarized table in seconds. A 5 MB CSV is always better than 500 MB.

Bottom line: Excel's CSV handling is a legacy bottleneck. Power Query is the bypass. Use it.