Today we will discuss about a very common problem of Microsoft Excel File i.e. Huge File Size of Excel file and try several techniques to reduce the Microsoft Excel File Size without deleting data.
There are many causes that can help an excel file to become huge, sometimes Excel spreadsheet become so huge while it actually containing very little information.
A large sized Excel workbook may generate lots of issues while dealing with the file, such as file will respond very slow, take more time to save any changes, take time to upload and more.
The main question is “What part of an Excel file causes the big file size. Some of them are,
Huge information- Generally a worksheet having more than 100,000 rows and many columns demands large memory to store.
Media files- If your excel file containing images/pictures/logo then it can turn your small excel file into a big one.
Pivot Tables- Yes, pivot table may also affect the file size. Pivot Tables store the original data hence in each of the Pivot Table have its own data set.
Formatting- In case the sheets are formatted though cell ranges aren’t in use then the file size might increase.
Sometimes excel file’s Meta-data and other hidden contents may also boost the file size.
So, there are several causes of a big MS excel file size hence we have to apply different tricks to reduce it. The solution may depend on the contents of Excel workbook if you are not sure that which one is affecting your excel file then you should apply them one by one.
Let’s start applying the below given tricks one by one to convert a large excel file into a compatible size and reduce the size excel file.
Remove Cell Formatting
While creating an excel file we apply several formatting over the spreadsheet, hence deleting formatting will help you to save file size and boost the spreadsheet performance.
Steps to clear the excel formatting
Step 1: First, choose the range over which you want to clear the formatting.
Step 2: Then go through the: Home Tab > Editing Group > Clear > Clear Formats
If the excel spreadsheet containing lots of text then it is the best option.
Reduce the size of the image file added in Microsoft Excel
Steps to compress or change the resolution of the image. Compressing doesn’t affect the quality of your excel file. If you don’t want every single pixel in a picture then you can go with the compress option and make the file size smaller.
Step 1: Open your excel file that contains image or logo and then choose the picture or pictures for compressing.
Step 2: Now, go to the Picture Tools -> Format tab, and in the Adjust group you have to click on the Compress Pictures.
Note: If you are unable to see the Picture Tools – Format tab then confirms that you have selected a picture. Double-click on the image to choose it and open the Format tab.
If you only seeing the Adjust group then the Compress Pictures button may appear without a label.
In order to decrease the size of a specific image and leave all of the other images present in the document, mark the Apply only to this picture check box.
If you unmark the Apply only to this picture checkbox then it will override any previous modifications you have done for other images present in this document.
Now, go to the Resolution and then select the resolution that you want and click OK.
Also Read: 6 Tricks to Avoid Errors in MS Excel
Convert File type to Binary Format (XLSB)
By converting an excel file to the binary format can reduce size of excel file with ease. So, follow the given steps and save an Excel file in the XLSB format to decrease the file size.
Step 1: Click on the File tab and then go to the Save As option.
Step 2: Now, click on the Browse button to change the file type.
Step 3: From the Save As dialog box, you need to pick the Excel Binary Workbook (.xlsb) file type. Click Save.
Remove Unnecessary Formulas
Having lots of complicated formulas can affect the file size of an excel file, so in this method, we will try to remove unwanted formulas from an excel workbook.
Sometimes when you have a data set and don’t need the formulas in it then it is better to replace these formulas into values. Follow the below given steps to do this:
Step 1: Open the excel file and press the ctrl + A keys to select the entire worksheet/dataset then press the F5 key.
Step 2: Open the Go To option and click on the ‘Special’.
Step 3: Select the Formulas and then click OK.
Now, you have selected all the cells that have a formula.
After it, follow the below steps to convert the selected formulas into values:
Step 1: Press the ctrl + C to copy the selected cells.
Step 2: Then click on the Home tab, and from the Clipboard group, click on the Paste.
Step 3: Then click on the Paste Value icon.
So, all the cells with formulas now get converted into the values.
Optimize the used formulas
After removing the unwanted formulas, now we will optimize the used formulas by using the following steps:
Skip using the volatile or unstable function such as –
TODAY, NOW, INDIRECT, RAND, RANDBETWEEN, CELL, OFFSET, INFO.
You need to replace the VLOOKUP, HLOOKUP and LOOKUP function with INDEX & MATCH
You should also replace the nested IF functions with any of the below given:
REPT, INDEX & MATCH, SUMIF(s) or Boolean logic
Compress the File (ZIP IT)
This method is little different, using the zip file option you can compress the excel file with ease. By compressing an Excel file, you can reduce a file size about 10-15%.
Steps to Zip an Excel file
Step 1: First you have to right-click on the Excel File that you want to zip
Step 2: Now, take your mouse cursor to the Send to option and click on the ‘Compressed (zipped) folder’ option.
That’s it, after creating a zipped file share it with others and the receiver will unzip the file to access the excel file.
Delete the unused cells
In order to delete the visible unused table, columns and formatted cells follow the below-given step.
First, you have to select all the rows to present below your last row having content.
Then Press Ctrl + ‘–’ from the keyboard to delete the rows.
After it, click on the rubber button present at the right-hand side of the Home ribbon.
And then click on ‘Clear All’.
You have to repeat the above steps on every worksheet as well as for the columns.
At last, save the workbook and check that the file size is reduced or not
Reduce Condition Formatting from Excel File
To do this, you have to choose the entire worksheet by pressing the Ctrl + A keys from the keyword.
Now, go to the Home / Conditional Formatting / Manage Roles
At last, select the irrelevant roles and erase the Rule
Compacting Excel File
This method is different from the zip compressing,
This trick is not present on the Microsoft Excel but this option is present on the every Windows system to compact the size of various files that include Microsoft Excel.
In order to compact your Excel file, you have to find your excel file using Windows Explorer and then right click on the file.
Choose Properties from the context menu and then select the Advanced Button
At last, choose the “compress contents to save disk space…”
Reduce the size of Pivot Tables
As we have already discussed that the Pivot Tables consume a lot of memory and may increase the file size. So, we will try to reduce the size of pivot tables.
There are mainly 3 options to reduce the Size of Pivot Tables:
Option 1: Sometimes, Pivot Tables are created to check data or seeing an overview. If you don’t need these Pivot Tables in your workbook, then remove the unused pivot table.
Option 2: Instead of creating several Pivot Tables having the same source code you should copy and use them where you need. Through this, you can share the same data cache.
Option 3: Avoid saving the source data of Pivot Table with the file. To do this, right click on the Pivot Table and then go to the Pivot Table Options. Now, from the data tab unmark the tick from ‘Save source data with file’.
Check and Delete unnecessary hidden cells
Hidden cells may also add some extra burden to your excel file. Unwanted hidden cells can boost the size of excel file. Microsoft cells get hidden accidentally while working on the rows or columns of the excel worksheet.
If you find out that your file contains unwanted hidden elements then you need to unhide the rows and columns of your excel worksheet and analyze you need them or not. Delete the unwanted hidden cells.
All the above-given methods are very effective and will help you to reduce the size of a Microsoft Excel file.
So, it is hoped that after applying all the above discussed 11 methods your excel file become small and optimized.
If you have any query or suggestion about this article or suffering from any other kind of issue while using the Microsoft Excel file then kindly contact us at http://ask.repairmsexcel.com