How To Reduce Size Of Excel File

Excel workbooks have been known to become painfully deadening and heavy as more data/formulas/images are added to it.

Sometimes, it's easy to become rid of the extra flab and go on the excel file size in cheque, and sometimes, it isn't.

And since Excel is non a spider web-based tool, most of us piece of work and interact by sending Excel files via e-mail. And a big Excel file that weighs 50 MB can exist hard to share with your colleagues/clients.

In my last task, we used Lotus Notes email client, which is snail-slow, and sending heavy Excel files over that was torture.

While at that place is no magical style to reduce the size of an Excel workbook, at that place are a few techniques that you lot can use to cut down some flab.

Annotation: The impact of these techniques to reduce Excel file size would depend on your data. For some, information technology may help reduce the size by 50% and for some, information technology may only finish up reducing the size past five%.

• Tips to Reduce Excel File Size
  • Remove Unnecessary Worksheets/Data
  • Convert to Binary Format (XLSB)
    • What is XLSB?
    • XLSB Vs XLSX/XLSM
  • Remove Unnecessary Formulas
  • Compress Images (Before and After Uploading)
  • Remove Pin Cache to Save Space
    • Keep the Source Data and Delete Pin Cache
    • Go along the Pin Cache and Delete the Source Information
  • Compress the File (ZIP Information technology)
  • Remove Data formatting
  • Remove Provisional formatting
• Excel Performance Booster Tips

Tips to Reduce Excel File Size

Beneath are the techniques you can apply to reduce the file size of your Excel workbook.

For each tip, I have washed some testing and have added the snapshots to show you the impact on file size. The results you go would vary depending upon your data and files.

Based on your workbook, you tin can utilize one or more of these tips.

Remove Unnecessary Worksheets/Information

The best solutions are often the easiest ones.

And this technique is more than of common sense and less of any Excel wizardry.

If you accept an Excel workbook that has some worksheets that are not needed (and are not being used in any formulas), see if you can go rid of those.

This has a straight correlation with the Excel file size. Then as soon as yous delete some of the worksheets and save the workbook, you will instantly see a reduced file size.

Examination Results:

I created an Excel file with ten worksheets where each worksheet has 2.half dozen million data points (26 columns with 100,000 data points in each cavalcade).

The resulting size of this workbook is 143 MB (and information technology'due south taking quite some time to relieve and open up).

Now when I deleted five sheets, the size reduced past one-half (duh).

As you can run across, data that you have your worksheet has a direct correlation with the file size.

Convert to Binary Format (XLSB)

Practise you lot know that simply by saving an Excel file in the XLSB format can reduce the file size?

Aye, it'south true.

All yous have to do is while saving a workbook, apply the .xlsb format.

Here are the steps to do this:

1. Click the File tab.
2. Click on Save Every bit.
3. Click on Scan.
4. In the Salvage As dialog box, change the file blazon to Excel Binary Workbook (.xlsb)

Exam Results

I had an Excel workbook that took xiv.vi MB of space.

Later on converting the same file to the XLSB format, I was able to reduce the size to 10.4 MB (a size reduction of ~28%).

What is XLSB?

When you save an Excel file (.xlsx or .xlsm), information technology gets saved in the XML format.

For instance, if I change the file extension of the XLSX file to ZIP and and then open it, this is what I get.

And if I further go in the 'xl' folder and then go in the worksheet folder, information technology shows me the worksheets in that workbook (and these worksheets are saved as an XML certificate).

Now let'due south exercise the aforementioned with an XLSB file.

So I showtime change the file extension to ZIP, then open it and locate sheet1. Hither is what yous get:

You'll notice that both XLSX and XLSB saves the worksheet in a different format.

XLSX/XLSM saves it as an XML file and XLSB saves it as a binary (.bin) file.

And without getting too technical, XML files are big in size as compared to a binary file.

So when you save your Excel workbook in the XLSB format, the file size is reduced. In our in a higher place example, the size reduction was ~30%.

XLSB Vs XLSX/XLSM

When it comes to using the XLSB format, size reduction is a huge benefit. Just you need to exist a bit cautious when working with the XLSB format.

Advantages of XLSB format:

1. It reduces the Excel file size. This can be helpful when you have large files and yous desire to share information technology over email. You can quickly reduce the size by saving it in XLSB.
2. XLSB files tend to open and save faster than XML file. Based on the limited tests I did, XLSB files were opening and saving 25%-l% faster. This difference is noticeable in large Excel files only. With smaller files, both XLSB and XLSX are fast.

While XLSB looks keen, in that location are a few reasons you should stick to using XLSX/XLSM files:

1. With XLSB format, you have no way of knowing whether it has a macro or non. This makes it riskier as someone can utilise it to execute malicious code.
2. XLSB files are encoded in proprietary bin file format while XML is an open-source readable file format. This ways that if you're using third-party tools with Excel, it's better to use XML format.
3. Ability Query cannot read data from an XLSB file. So if you employ Power Query, information technology'due south better to accept your data in XLSX/XLSM formats.

As a full general rule, if your file size is small (less than 5 MB), it's better to stick to XLSX/XLSM formats.

Based on what I heard from people and read on many forums, a lot of people prefer to use XLSB equally the file format when it comes to using Excel.

Here is a good commodity on understanding the XLSB format.

Remove Unnecessary Formulas

As well many formulas volition bloat your workbook file size.

And so if you have a data set up that y'all demand, but don't need the formulas in it, it'south improve to catechumen these formulas into values.

Here are the steps to apace convert formulas to values in Excel:

1. Select the unabridged worksheet/dataset.
2. Printing the F5 key.
3. In the Become To dialog box, click on 'Special'.
4. In the Go To Special dialog box, select Formulas.
5. Click OK.

The in a higher place steps would select all the cells that have a formula in information technology.

Now use the below steps to convert these formulas into values:

1. Copy the selected cells (control + C)
2. Click the Home tab.
3. In the Clipboard group, click on Paste.
4. Click on Paste Value icon.

The above steps would convert all the cells with formulas into values.

Test Results

I created an Excel file with 2.7 million data points (1048576*26) and and so added iii columns with formulas (SUM, COUNT and AVERAGE).

And so I converted these formulas into values and saved the file with a unlike proper name.

Below is the result:

The file with no formulas is ~8% less in size (and notation that I only had three columns of formulas).

In example you lot have a lot of formulas, information technology tin can add to the size.

NOTE: Volatile formulas can really bloat your file size. Equally a function of the test, when I replaced the SUM formula with a volatile formula (OFFSET), it atomic number 82 to a jump in the file size (from 186 MB to 198 MB). So if yous have volatile formulas that you can replace with not-volatile formulas, or can convert these into values, it will help you reduce the file size.

Compress Images (Before and Subsequently Uploading)

If you lot work with Excel files that have images in it, yous demand to optimize these images and make sure the size is less.

The trouble with images is that as you compress these, there is besides a reduction in quality, which you may not desire.

When working with images, the best way to proceed the size of your Excel file depression is by compressing the images before you insert information technology into Excel.

A practiced online tool to do this is TinyPNG (or you can use Photoshop or any other like tool).

It's a free tool that can reduce the size of an image by up to 90% and still maintains decent image quality.

Beneath is the screenshot where I was able to shrink an image with the size of 3.9 MB to 331 KB with minimal loss in quality.

Once you lot have uploaded these compressed images to Excel, you can further compress it using the inbuilt option in Excel.

Here are the steps to shrink an image in Excel:

1. Select the image that you want to compress.
2. Click on the Picture Tools Format tab. This is a contextual tab that but appears when y'all click on a moving picture.
3. In the Adjust group, click on 'Compress Picture' option.
4. In the Shrink Pictures dialog box:
   • Deselect 'Utilise only to this picture' if you want to compress all the images in the workbook.
   • Select Email (96 ppi) for maximum compression.
5. Click OK.
6. Save the file.

This will compress all the images and help yous reduce the file size of your Excel workbook.

Test Results:

I used the compress imge pick in Excel using one image.

Below are the results:

By using the compress prototype pick, file size reduced from 445 KB to 43 KB (~90% reduction in file size).

This would depend on the image also. An epitome that is already compressed may not lead to a huge size reduction.

Remove Pivot Cache to Salvage Space

If you piece of work with Pivot tables and struggle with large Excel files, knowing near Pivot cache can be useful.

When you create a pivot tabular array using a data prepare, Excel automatically creates the Pin Enshroud.

Pivot cache is an object that holds a replica of the data source.

While yous can't see it, it is a role of the workbook and is continued to the Pivot Table. When you make whatever changes in the Pin Table, it does not use the information source, rather it uses the Pin Enshroud.

While y'all call back that y'all are straight linked to the source data, in reality, you access the pivot enshroud (and not the source data) when you make changes in the pin table.

Now since y'all're creating a replica of the data set, it takes some space.

So as soon equally you create a Pivot table, you lot will notice that the size of the Excel file increase.

In the above example, equally soon equally I add together the Pivot tabular array, the size of the file increases two-fold.

Now, at that place are two ways y'all tin can reduce the file size when working with Pivot tables.

1. Keep the source data and delete the Pin Cache
2. Keep the Pivot Cache and delete the source data

Let'southward wait at both these techniques.

Keep the Source Data and Delete Pin Cache

When you're done with the Pivot table and are saving it, yous tin can force Excel to merely save the source data and the resulting Pin Table, and not the Pivot Cache.

Doing this will make your Excel file lighter, and you lot have a way to recreate the Pivot cache when you need it the adjacent time.

Below is the difference in size when yous save an Excel file with and without Pivot Cache.

Hither are the steps to Not relieve the Pivot Enshroud:

1. Select any cell in the Pivot Tabular array.
2. Click the Analyze tab.
3. Click on Pivot Table 'Options' icon.
4. In the Pivot Tabular array Options dialog box, click on Data tab.
5. In the Pivot Table Information options, uncheck 'Save source data with file'. This option ensures that the Pivot Cache is non saved when you lot save the workbook (and helps in reducing Excel file size).
6. Check the selection – 'Refresh data when opening the file'. This option ensures that soon as you open up the workbook, the Pivot Table is refreshed and the Pivot Cache is automatically generated. If you lot don't check this selection, you'll have to refresh the Pivot table manually to generate the Pivot Cache.
7. Click OK.

Now when you salvage the file with the Pin tabular array, you'll notice a reduced file size.

Keep the Pin Cache and Delete the Source Data

Another way reduce Excel file size is to delete the source data of a Pivot Table and keep the Pivot Cache only.

This works as a Pivot Cache is a mirror prototype of your data and you don't access the source information when making changes to the Pivot Tabular array, instead, you use the Pivot Cache.

Beneath is are results when I saved the Excel file without the source data.

To do this, just delete the information (either by deleting the data or the worksheet that has the data).

When yous want to get the source data back, double-click on the 'Grand Total' cell of the Pin tabular array and it will instantly generate the entire data set in a new worksheet.

Shrink the File (Zero IT)

This is something you can practice outside of the Excel awarding.

When you zip an Excel file, you'll encounter a size reduction of almost 10-xv% immediately.

You can and so share this zipped file to via electronic mail, and the person receiving this can unzip the file and use the Excel workbook.

Here are the steps to Zip an Excel file (for Windows):

1. Right-click on the Excel File
2. Hover your cursor on the Send to option.
3. Click on the 'Compressed (zipped) folder' option.

This volition create a zipped file for the selected Excel workbook.

You'll besides detect a reduction in file size (as shown below).

Test Results

With this technique, I was able to reduce the file size of the Excel workbook by 8%.

This technique works better for files that are in the twenty-50 MB range. As the file size increases, the percentage reduction is non very loftier.

Remove Data formatting

Data formatting such as applying a background colour, or adding borders, or changing font style can all add together to the file size.

Although, based on my tests, I noticed that information technology doesn't take a huge impact.

Beneath are the sizes of two Excel workbooks, 1 with 2.6 one thousand thousand data points (in one worksheet), and the other with the same data simply formatting applied to it (cell color, border, and italicized).

Examination Results

You can run across the difference betwixt the two files is not that huge (although it does make an impact when yous have a lot of data).

The file with information is 14.half dozen MB and the file with data and formatting is 14.9 MB.

Then if you desire to reduce the file size of your Excel workbook, you can expect for formatting you don't need. Removing information technology can save you some disk space.

Here are the steps to instantly remove all the formatting, while keeping the data intact:

1. Select the entire dataset (from which y'all want to remove formatting).
2. Click the Home tab.
3. In the Editing group, click on 'Clear'.
4. Click on 'Articulate Formats'.
5. Save the workbook.

Remove Conditional formatting

Only like regular information formatting, conditional formatting also adds to the file size.

However, based on my tests, I noticed that the departure is less (unless you lot accept multiple rules applied to multiple sheets with huge datasets).

Nevertheless, it's a good thought to remove conditional formatting from any dataset where you don't need it.

Apart from the size, in that location is another good reason to remove provisional formatting – SPEED.

Conditional formatting is volatile and whenever at that place is any change in the worksheet, it recalculates. This can atomic number 82 to a slower workbook.

Test Results

I created a file with 27.2 million data points and applied 5 provisional formatting rules to it.

The deviation in size seems to be only a few KBs.

          As well Read: Tips to Brand Slow Excel Spreadsheets Fast

Excel Performance Booster Tips

While the above methods will help you reduce Excel file size, here are some additional tips to meliorate the operation of Excel Workbooks:

1.  Avoid using volatile formulas. Apart from increasing the file size, volatile formulas also make you workbooks dull. Examples of volatile functions include RAND, TODAY, OFFSET, INDIRECT, etc.
2. If yous take a need to create summaries of a large dataset, instead of using formulas, see if you can employ a Pivot Table instead. A Pivot table is fast and very intuitive as compared to formulas.
3. If you lot extract information from multiple sources or combine data from multiple files, use Power Query to go and transform the information. You can save the data equally a Power Query connection instead of keeping it in the worksheet. Yous tin can easily create Pivot Table using the connections created using Power Query.
4. Convert your tabular data into an Excel tabular array and use the Excel table in formulas. Information technology makes your workbook perform faster and formulas created using Excel Table structured references are easier to manage (as these are more intuitive).
5. Avoid referencing unabridged row or cavalcade in a formula. This will help your formulas be more efficient.
6. If you have a lot of formulas in your workbook, turn off Automatic calculation and switch to manual calculation. Past doing this, every time there is a change in the worksheets, the formulas wouldn't recalculate. You can brand this change by going to the Formula tab and irresolute the 'Calculation Options'.

Hope you find the method and tips in this tutorial useful.

What else I could add to this tutorial to brand it more useful? Please let me know in the comments section.

Yous May Also Like the Following Excel Tutorials:

• How to Recover Unsaved Excel Files.
• 100+ Excel Interview Questions.
• All-time Excel Books (to Level up your Excel Skills).
• 200+ Excel Keyboard Shortcuts.
• 10 Means to Make clean Data in Excel.
• How to Plough On AutoSave in Excel

Source: https://trumpexcel.com/reduce-excel-file-size/

Posted by: wilsondiscovor.blogspot.com

Share this post