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. Show
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 <div style="padding:1em 0"> <style> .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%; } </style> <div class="social-sharer-container"> <span class="social-sharer-label">Share this:</span> <ul class="social-sharer"> <li class="social-share facebook"><img src="https://chrismenardtraining.com/images/icons/social/32/facebook.png" alt="Share Page on Facebook"></li> <li class="social-share pinterest"><img src="https://chrismenardtraining.com/images/icons/social/32/pinterest.png" alt="Pin on Pinterest"></li> <li class="social-share twitter"><img src="https://chrismenardtraining.com/images/icons/social/32/twitter.png" alt="Share Page on Twitter"></li> <li class="social-share linkedin"><img src="https://chrismenardtraining.com/images/icons/social/32/linkedin.png" alt="Share Page on LinkedIn"></li> <li class="social-share googleplus"><img src="https://chrismenardtraining.com/images/icons/social/32/googleplus.png" alt="Share on Google+"></li> </ul> </div> <script type="text/javascript"> 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 + "&description=" + tweet; socialWindow(url); }); jQuery(".social-share.twitter").on("click", function () { url = "https://twitter.com/intent/tweet?url=" + pageUrl + (tweet !== '' ? "&text=" + tweet : ""); socialWindow(url); }); jQuery(".social-share.linkedin").on("click", function () { url = "https://www.linkedin.com/shareArticle?mini=true&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: 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: Workbook Statistics shows useful info about your Excel workbook Start cleaning up your Excel fileThe 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 ExcelIf 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:
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!): 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:
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:
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:
This should take care of it. Start a selection after the data rows end Check if the file has shrunkAfter 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 Chris MenardChris 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
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.. |