How do i open an excel file that is too large?

Might be worth trying to change the file type from .xlsx to .xlsb. Before trying anything, make a copy of the Excel file and make any adjustments to the copy to preserve your original. I looked into file size and general optimization last year and the most impactful thing you can do for file size also happens to be the easiest. I got one file from 120 MB down to 19 MB without doing anything other than changing to .xlsb.

Obviously you're in more of a bind than normal because you can't go into the file itself and save as a new extension. You're going to need to find a way to change the file type from within file explorer. Here's a quick google search that might work. If not, that should be pretty easy to figure out by looking it up online elsewhere.

The theory here is that your computer simply doesn't have the power to open such a large file. Dramatically cutting down the file size might help you open it. Let me know how it goes, hopefully that helps!

Posted on:  07/13/2021 &lt;div style="padding:1em 0"&gt; &lt;style&gt; .social-sharer-container { padding: 5px 0; margin: 0; } .social-sharer-label { font-weight: bold; color: #4d4d4d; } .social-sharer { list-style-type: none; margin: 0; padding: 0; } ul.social-sharer { /*display: inline-block;*/ margin: 0; padding: 0; } li.social-share { display: inline; } li.social-share:hover { cursor: pointer; } li.social-share img { border-radius: 10%; } &lt;/style&gt; &lt;div class="social-sharer-container"&gt; &lt;span class="social-sharer-label"&gt;Share this:&lt;/span&gt; &lt;ul class="social-sharer"&gt; &lt;li class="social-share facebook"&gt;&lt;img src="https://chrismenardtraining.com/images/icons/social/32/facebook.png" alt="Share Page on Facebook"&gt;&lt;/li&gt; &lt;li class="social-share pinterest"&gt;&lt;img src="https://chrismenardtraining.com/images/icons/social/32/pinterest.png" alt="Pin on Pinterest"&gt;&lt;/li&gt; &lt;li class="social-share twitter"&gt;&lt;img src="https://chrismenardtraining.com/images/icons/social/32/twitter.png" alt="Share Page on Twitter"&gt;&lt;/li&gt; &lt;li class="social-share linkedin"&gt;&lt;img src="https://chrismenardtraining.com/images/icons/social/32/linkedin.png" alt="Share Page on LinkedIn"&gt;&lt;/li&gt; &lt;li class="social-share googleplus"&gt;&lt;img src="https://chrismenardtraining.com/images/icons/social/32/googleplus.png" alt="Share on Google+"&gt;&lt;/li&gt; &lt;/ul&gt; &lt;/div&gt; &lt;script type="text/javascript"&gt; function socialWindow(url) { var left = (screen.width - 570) / 2; var top = (screen.height - 570) / 2; var params = "menubar=no,toolbar=no,status=no,width=570,height=570,top=" + top + ",left=" + left; window.open(url, "NewWindow", params); } function setShareLinks() { var pageUrl = encodeURIComponent(document.URL); var tweet = encodeURIComponent(jQuery("meta[property='og:description']").attr("content")); jQuery(".social-share.facebook").on("click", function () { url = "https://www.facebook.com/sharer.php?u=" + pageUrl; socialWindow(url); }); jQuery(".social-share.pinterest").on("click", function () { url = "https://pinterest.com/pin/create/button/?url=" + pageUrl + "&amp;description=" + tweet; socialWindow(url); }); jQuery(".social-share.twitter").on("click", function () { url = "https://twitter.com/intent/tweet?url=" + pageUrl + (tweet !== '' ? "&amp;text=" + tweet : ""); socialWindow(url); }); jQuery(".social-share.linkedin").on("click", function () { url = "https://www.linkedin.com/shareArticle?mini=true&amp;url=" + pageUrl; socialWindow(url); }); jQuery(".social-share.googleplus").on("click", function () { url = "https://plus.google.com/share?url=" + pageUrl; socialWindow(url); }); } $(document).ready(function () { setShareLinks(); }) </div> <img src="https://chrismenardtraining.com/_CMT/images/blogs/posts/featured/1192.jpg" class="post-featured-image-main" alt="Excel file size out of control? Learn how to reduce Excel spreadsheet file size"> <p> <strong>Sometimes working in Excel, moving data around, creating worksheets and removing, copy pasting, all that work can lead to huge file sizes, without realizing. I've had a situation like this recently, with an Excel spreadsheet that didn't have a lot of data, but the file size on disk was more than 15 times larger than it should have been.</strong><span id="ezoic-pub-ad-placeholder-117" class="ezoic-adpicker-ad"></span><span class="ezoic-ad ezoic-at-0 box-3 box-3117 adtester-container adtester-container-117" data-ez-name="chrismenardtraining_com-box-3"><span id="div-gpt-ad-chrismenardtraining_com-box-3-0" ezaw="728" ezah="90" style="position:relative;z-index:0;display:inline-block;padding:0;min-height:90px;min-width:728px" class="ezoic-ad"><script data-ezscrex="false" data-cfasync="false" type="text/javascript" style="display:none">if(typeof ez_ad_units != 'undefined'){ez_ad_units.push([[728,90],'chrismenardtraining_com-box-3','ezslot_4',117,'0','0'])};__ez_fad_position('div-gpt-ad-chrismenardtraining_com-box-3-0');

Reducing the file size of your Excel worksheets is easy, all you have to do is identify what is causing the problem. Sometimes you work on an Excel file with one or more Worksheets and when you save, you notice the saving is slower, takes longer to move around or open the right-click menus, etc. All these are symptoms that the file has a size problem - in-memory or on disk. 

Let's have a look at how you can check the size of your Excel workbook, how to inspect the worksheets to figure out which worksheet is too large or is causing the size issue, then look at a few cool keyboard shortcuts to help you move around and diagnose the problem and make your excel spreadsheet smaller. Finally, with a few keystrokes, I show you how you can clear the problem, reduce your excel file size and delete unnecessary or blank rows and columns in your Excel workbook so that it is optimized.

How to check the file size of your Excel spreadsheet?

With your spreadsheet open, go to File > Info, and on the right-side you should see the size on disk of your workbook:

How do i open an excel file that is too large?

Check your Excel file size easily, from Excel

To further investigate the problem and see some data about your entire workbook, you can go to the Workbook statistics in the Review tab in Excel, and look at the workbook statistics. This should show you what's going on in your current worksheet as well as how many cells with data are in your workbook, how many worksheets you have and other useful info:

How do i open an excel file that is too large?

Workbook Statistics shows useful info about your Excel workbook

Start cleaning up your Excel file

The first thing I would do is check which worksheets I don't need and delete them. Whether they are blank or you think they are blank, if you don't need them, remove them. 

Then save your document again and once the Excel file has saved, check the size again in File > Info, as described above. Maybe the file size went down. If not, keep reading...

Useful keyboard shortcuts to find problem areas in Excel

If removing unnecessary worksheets doesn't help, here are a few keyboard shortcuts to help you move around your worksheet faster and see what the problem is:

CTRL + HOME This shortcuts takes you to the first cell in your worksheet. Or the top-left cell in case you have some frozen panes at the top, like headers. This shows where your data range STARTS in your current Excel worksheet.
CTRL + END This should take you to the bottom of the worksheet. This helps to see if your worksheet ends where the data ends or if you have invisible data or cells that Excel thinks have data in them.

In my example problem file, the CTRL + END shortcut took me all the way to more than 1 million rows (I don't have that much data!):

How do i open an excel file that is too large?

Empty cells that Excel thinks are filled with data can be the cause of an over-inflated file

This is what makes the file so large: Excel is picking up a lot of empty cells and thinks there's data in them, which then it tries to save into the XLSX file on disk, and because it needs to keep references to all those hundreds or thousands of cells, the file size gets overblown.

So we've identified the problem, but how can we optimize the Excel file?

I have two more shortcut combinations for you, and this time, these will show you where the data in your Excel spreadsheet ACTUALLY ends:

CTRL + DOWN ARROW Go to the last row that has data in it.
CTRL + RIGHT ARROW Go to the last column with data in it.

So these keyboard shortcuts help identify the extent of your data and not what Excel thinks is data.

To remove all empty cells, follow these steps:

  1. Go to the column next to the last one with data in it, in my example it's column M, and select that column.
  2. Press CTRL + SHIFT + RIGHT ARROW key to select all the columns after this one, up to the end of the empty columns
  3. Then press right-click and select Delete.
How do i open an excel file that is too large?

Select the first column without data at the end of your data range

This should have removed all the 'data' in those empty columns.

Now repeat the same procedure for the rows with no data:

  1. Use the shortcut CTRL + DOWN ARROW to find the last row with data in it
  2. Select the row immediately AFTER that row
  3. Press CTRL + SHIFT + DOWN ARROW to select all the empty cells up to the end of the sheet
  4. Right-click and select Delete on these cells to remove them (this might take a while, for me on a powerful computer it took a while because I had more than 1 million rows!)

This should take care of it.

How do i open an excel file that is too large?

Start a selection after the data rows end

Check if the file has shrunk

After the procedures above, save your file again. This time the file size should have gone way down, if all the empty cells have been removed.

If the file size doesn't reduce as much as you think it should, make sure you don't have other worksheets in the same situation and repeat the steps above on those ones too.

If this was useful, let me know and check out my YouTube channel for more Excel tutorials!

Related articles

How do i open an excel file that is too large?

Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 800 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 12 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube

Categories

  • Blog » Microsoft » Microsoft Excel » Basic

Why large Excel files are not opening?

Another possible reason that causes Excel files to not open could be that your Microsoft Office application is corrupt and need to be repaired or reinstalled. But since reinstalling is a little more work, let's try the repair option first.

How do I make a large Excel file smaller?

Want to know how to reduce your Excel file size? ... .
How to audit a large Excel file. ... .
Tip #1: Remove conditional formatting. ... .
Tip #2: Crop each worksheet to its used range. ... .
Tip #3: Remove unnecessary formulas. ... .
Tip #4: Compress pictures in Excel. ... .
Tip #5: Clear data formatting. ... .
Tip #6: Delete watches..