You receive transaction records or log data as an Excel file, and the moment you open it Excel shows "Not Responding", or after a long wait you get an out-of-memory error. Here is why Excel gets heavy and what to do in each situation.
Common reasons Excel gets heavy
1. Data close to the row and column limits
An Excel sheet holds up to 1,048,576 rows × 16,384 columns. Beyond a few hundred thousand rows, sorting, filtering and formula recalculation slow down dramatically.
2. A hidden "used range"
The data may be only 1,000 rows, but if someone applied formatting to entire columns, Excel treats all one million rows as used. If Ctrl + End takes you far below your actual data, this is the cause.
3. Formulas and conditional formatting
Tens of thousands of formulas such as VLOOKUP and SUMIF are recalculated every time the file opens. It is also common to find hundreds of duplicated conditional formatting rules created by copy and paste.
4. Images and objects
Screenshots, hidden shapes and pivot caches can make a file much larger.
Check the content first when it will not open
If you just need to see the data, you do not have to use Excel. A viewer that only reads values, such as the Excel Viewer, skips recalculation and formatting, so it opens much faster. Find the rows you need with search, then work only on the part you need.
Practical ways to reduce file size
Clean up the used range
- Click the row number just below the last row of real data.
- Press Ctrl + Shift + Down Arrow to select to the end and delete those rows.
- Do the same for columns, then save and reopen the file.
Replace formulas with values
If results will not change anymore, copy them and use Paste Values to remove the formulas. Keep a backup if you still need the original formulas.
Save in binary format
Choosing Excel Binary Workbook (.xlsb) in Save As often makes the same data smaller and faster to open.
Working with CSV
If you only need the data itself, CSV is the lightest and most compatible format.
- Pros: no formatting or formulas, so files are small and readable by Notepad, databases, Python and R.
- Cautions: each sheet must be saved separately, and all formatting and formulas are lost.
- Korean text: choose CSV UTF-8 (Comma delimited) so Korean text breaks less in other programs. See the CSV encoding guide.
If it is still too big
Data beyond a million rows is outside what Excel is designed for. Load only the columns and rows you need with Power Query (Data > Get Data), or consider a database or analysis tool. For development, you can turn just the sheets you need into JSON with the Excel to JSON converter and work with them in code.