Excel file too large to open

In this post I will explain various ways to reduce Excel file size. Large Excel files slow down your system, increase the likelihood of the file crashing as well as obviously use more drive space.

Microsoft Excel was designed to be a tool for managing relatively small datasets for Finance & Accounting purposes, today we see that Excel is used often for large data analysis, prototyping of complex solutions (built often with VBA Marcos). You probably wondered here struggling with bloated Excel file, frustrated with having to wait for calculations to complete or constantly crashing Excel file.

Reduce Excel file size… or change the tool

Before we start working to reduce the Excel this is a good moment to ask yourself the question…

Is Excel the right tool for the job?

If you are convinced so proceed further, otherwise consider that Microsoft Excel has built several tools dedicated to managing with challenges that appear when working with larger data sets:

• PowerQuery – this Microsoft AddIn for Excel was created specifically for pulling and running queries on large datasets in Excel. You can easily merge worksheets, csv files or run complex relationship queries across your worksheets
• PowerBI – Microsoft PowerBI is a free application you can download from the Microsoft Store that allows you to create custom dashboards, run complex analytics… and easily publish your dashboards and reports online

Save file as Binary (XLSB)

Since Excel 2007 Microsoft was pressured to open up the Excel format so other applications could use it. This meant moving away from the binary file format which was storage friendly and would open efficiently, to the more heavy file format (XMLs files compressed to a ZIP file). In effect the XSLX file format will always be larger than the XLSB.

To save a file in binary format go to FILE click on Save As and select Excel Binary Workbook (*.xlsb) as shown below.

Remove Used Cell Range

A troublesome thing that can increase your Excel file size is Used Range. Imagine the worksheet below. Let us assume at some point I added some data into cell G16, and afterwards I deleted it. Although de facto I am only now using A1:C4, de jure Excel will keep in memory the entire range of A1:G12 expanding the file size.

How to check your Excel Used Range? Use the CTRL+END key combo. You will be moved to the last cell in your Worksheets Used Range

Removing Unused Ranged

So what to do to reduce your Used Range only to the actual range you are using? Follow the steps below:

Remove data and formatting from unused cells

Select all unneccesary cells that contain data or formatting and go to Home->Editing->Clear and select Clear All:

Reset Worksheet Used Range

Now we need to update the Used Range property in your Worksheet. Go to the Developer Tab and open the VBE. Next add the following VBA Macro to any VBA Module.

Sub ReduceRangeActiveWorksheet()
     Debug.Print ActiveSheet.UsedRange.Rows.Count
End Sub

Next run the Macro (or use the F5 shortcut). The Used Range should be now updated.

Remove Hidden / Unused Worksheets

Another reason for large file size is having many unused or hidden worksheets in your Workbook. Each Excel Worksheet has it’s own share of metadata, more importantly, however, you may be keeping sheets with similar datasets, copies or unnecessary Pivot Tables that also take up a lot of space. Follow steps below to show and delete unnecessary hidden worksheets.

Unhide hidden worksheets

To unhide hidden worksheets right-click on a Worksheet and select Unhide:

Delete unused Worksheets

To delete an unused Worksheet right-click and select Delete:

Remove Formatting

Formatting adds additional kilobytes to your Excel file size. A good approach is to remove any formatting from cells that don’t need formatting.

To remove cell formatting you can read this MSDN article or follow below:

Select Cells and click Clear Formats

Select Cells for which you want to remove formats. Look for the Editing section in the Home ribbon and select Clear Formats:

Compress Images

Another reason for Excel to have an unreasonable file size is due to media, especially Images. Although you may think cropping and Image and resizing reduce its size, in fact Excel still keeps the entire image in memory. Hence the only way to free Image memory is to Compress Pictures.

Crop and Compress Images in Excel

To Compress your Images click on your Image(s) in Excel and go to Format->Adjust->Compress Pictures. Next select your preffered options. Select Delete cropped Areas of pictures to remove the unseen cropped areas of your images as well as the preffered resolution (the lower the more pixelated the image will seem):

Other options

Other honorable mentions to reduce your Excel file size may be:

1. Remove unused Pivot Tables
2. Remove Pivot Cache
3. Replace Formulas with Data (Copy Paste as Data)

Feel free to comment below and add your ideas!