{"id":24387,"date":"2021-11-15T08:24:21","date_gmt":"2021-11-15T07:24:21","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=24387"},"modified":"2024-02-06T16:06:45","modified_gmt":"2024-02-06T15:06:45","slug":"reducing-file-memory","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/reducing-file-memory\/","title":{"rendered":"Reducing file memory"},"content":{"rendered":"

\"Reducing<\/a><\/p>\n

When working with Pivot Tables<\/a>, sometimes we do not even notice that our data sources have grown to large sizes already. And if your Excel workbook is running slowly, what should you do?<\/p>\n

I have a couple of quick ways of reducing file memory of your workbook!<\/p>\n

    \n
  1. Save as Binary Workbook<\/strong><\/li>\n
  2. Delete original data table<\/strong><\/li>\n
  3. Delete unnecessary formulas<\/strong><\/li>\n<\/ol>\n

    Let’s look at each of these tips one by one!<\/p>\n

     <\/p>\n

    Don’t forget to download the Exercise Workbook<\/strong> below and follow along with us:<\/p>\n

    \n

    DOWNLOAD EXCEL WORKBOOK<\/strong><\/i><\/a><\/p>\n

    \n<\/div>\n

    \n

    Let us go over our current setup first. Here is our Pivot Table<\/a>:<\/p>\n

    \"Reducing<\/p>\n

    And here is our data source, it’s a big table!<\/p>\n

    \"Reducing<\/p>\n

    Now go to\u00a0File > Info<\/strong><\/em> so that we can quickly check the original file size<\/p>\n

    \"Reducing<\/p>\n

    It’s\u00a045.7 MB<\/strong>. Let’s get to work and start reducing file memory of this workbook!<\/p>\n

    \"Reducing<\/p>\n

     <\/p>\n

    Method 1:<\/span> Save as Binary Workbook<\/span><\/strong><\/p>\n

    One quick way is to convert this into a Binary Workbook<\/strong>.\u00a0 Storing this in the binary format is useful for large spreadsheets<\/a>.<\/p>\n

    Go to\u00a0File > Save As<\/strong><\/em><\/p>\n

    \"Reducing<\/p>\n

    Select\u00a0Excel Binary Workbook (*.xlsb)\u00a0<\/strong>as the\u00a0File type<\/strong>. Click\u00a0Save.<\/strong><\/p>\n

    \"Reducing<\/p>\n

    After you do that, go to\u00a0File > Info<\/strong> and you will be surprised! It is now\u00a025.9 MB!<\/strong><\/p>\n

    \"Reducing<\/p>\n

     <\/p>\n

    Method 2: Delete original data table<\/span><\/strong><\/p>\n

    If you prefer to keep the original .xlsx\u00a0<\/strong>format, there is another workaround! Make sure to use the original Excel workbook file (.xlsx) with the big data table.<\/p>\n

    Right-click\u00a0<\/strong>on the\u00a0data table worksheet and select\u00a0Delete<\/strong><\/p>\n

    \"Reducing<\/p>\n

    Click\u00a0Delete.\u00a0<\/strong>The drawback with this approach is we will not be able to refresh the Pivot Table<\/p>\n

    \"Reducing<\/p>\n

    Even without the data table, the Pivot Table will still work! It is all thanks to the cache.<\/p>\n

    Drag the\u00a0SALES REGION\u00a0<\/strong>to Columns<\/strong><\/p>\n

    \"Reducing<\/p>\n

    It is working as expected, amazing!<\/p>\n

    \"Reducing<\/p>\n

    Make sure to save the workbook. Now go to\u00a0File > Info<\/strong> and it’s now only 11.3 MB!<\/strong><\/p>\n

    Thus, this method easily helps you in reducing file memory.<\/p>\n

    \"Reducing<\/p>\n

     <\/p>\n

    What if you need the data table again? There is a cool trick by double-clicking<\/strong> on the\u00a0Grand Total.<\/strong><\/p>\n

    \"Reducing<\/p>\n

    Now the data table is back again!<\/p>\n

    \"Reducing<\/p>\n

     <\/p>\n

    Method 3:<\/span> Delete unnecessary formulas<\/span><\/strong><\/p>\n

    If the source data contains formulas<\/a> and you can easily convert them into values<\/strong> to reduce the size of the Excel file.<\/p>\n

    To convert formulas to values, follow the steps below:<\/p>\n

    STEP 1:<\/strong><\/span> Select the entire data table.<\/p>\n

    \"Reducing<\/a><\/p>\n

    STEP 2:<\/strong><\/span> Press Ctrl + G<\/strong> to open Go to Special dialog box and click Special<\/strong>.<\/p>\n

    \"Reducing<\/a><\/p>\n

    STEP 3<\/span>:<\/span><\/strong> Select Formulas <\/strong>and click OK.<\/strong><\/p>\n

    \"Reducing<\/a><\/p>\n

    This will highlight all cells containing formulas!<\/p>\n

    \"Reducing<\/a><\/p>\n

    STEP 4:<\/strong><\/span> Press Ctrl + C<\/strong> to copy these cells.<\/p>\n

    \"Reducing<\/a><\/p>\n

    STEP 5:<\/strong><\/span> Right-click and select Paste Special<\/strong>.<\/p>\n

    \"Reducing<\/a><\/p>\n

    STEP 6:<\/strong><\/span> Select Values <\/strong>and click OK.<\/strong><\/p>\n

    \"Reducing<\/a><\/p>\n

    This will quickly convert all formulas in your worksheet into values. Thus,\u00a0reducing file memory of your Excel workbook and making your file work faster!<\/p>\n

     <\/p>\n

    Make sure to download our FREE PDF on the\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

    When working with Pivot Tables, sometimes we do not even notice that our data sources have grown to large sizes already. And if your Excel workbook is running slowly, what should you do? I have a couple of quick ways of reducing file memory of your workbook! Save as Binary Workbook Delete original data table […]<\/p>\n","protected":false},"author":2,"featured_media":25175,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[4],"tags":[807,808,809],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24387"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=24387"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24387\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25175"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=24387"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=24387"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=24387"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}