When preparing your work, sometimes the excel file might turn out to be big due to the complexity of the work you are dealing with, among other factors. Therefore, this will generate a lot of problems when transferring it from one person to another, especially when the document is above 50 MB. Thus you should try and minimize it to increase the speed. This article will discuss the various ways to make Excel file smaller.
1. Get rid of extra data and sheets
This is often the easiest and simplest way to reduce the size of an excel file. The more the worksheet and data, the larger the file. Therefore eliminating unnecessary worksheets will be of great value since the information we won’t need will be erased.
In short, the procedure is “Ctrl + Alt+ V+V.”
2. Remove formatting
As much as formatting enhances the visibility of the document and enables it to be neat and organized, it also increases the size of the document. Such formatting features are usually borders and highlights, among others.
- To clear the formats of selected cells, use the “CLEAR FORMATS” option, which is in the “EDITING” tab under the common “HOME TAB.”
- For conditional formatting, you’ll use the “CLEAR RULES” option,
3. Save in binary format. (xlsb)
Often files are saved without specifying in excel. Choosing the binary format can help greatly reduce the size of the excel document.
You’ll choose the Excel binary format when saving the file “( .xlsb).” The goal is usually to remove it from how they are often saved without specifications, in the format, XML, “(.xlsx or xlsm)
What is the .xlsb extension?
xlsb stands for Excel File Format Binary Extension. The default extension for saving Excel files is .xlsx, which saves the file in XML format. Excel allows you to save into different formats such as xlsb, xls, xlsm, PDF, Html, and many more.
Note that when you save the file in this format, other sofwares such as Libre cannot edit the file. Apart from reducing the size of an Excel format, the .xlsb extension offers the following advantages.
- It supports macros
- Useful for large files because .xlsb can be read and written faster. (.xlsb is four times faster than xlsx.)
- The file loads faster
- It is secure. (When someone tries to change the file to zip, it becomes unreadable)
4. Compress to make Excel file smaller
Compressing the file of a document usually reduces the size of the document. Research has proved that is the truth, as it will reduce it up to 10%- 15%. However, this does not speed the rate of working with the document, like other features, but at least it does help with the ease of sharing, as you won’t have difficulties sharing the document with your colleague due to its large size.
- You’ll “RIGHT CLICK” the excel file.
- Then select “COMPRESSED FOLDER, which is under the “SEND TO section.”
And you’ll be done, as you will have achieved your goal of compressing the excel worksheet, and your file will be smaller. You can check it out in the size sector to ensure that it’s reduced to below 50MB. With this size, you can send the document as fast as possible from one person to another without difficulties. Trust me. This procedure will deliver.
5. Remove Pivot Cache
A cache is a collection of data stored in an inaccessible or hidden place. You can reduce the size of an Excel file significantly by deleting this data. When you create a Pivot Table, Excel automatically creates a pivot cache.
You can delete the pivot cache in the PivotTable options.
- Open PivotTable options
- Navigate to the Data tab
- Untick the checkbox for “save source data with file.“
6. Compress images in Excel
Images can make an Excel file very large. If you don’t need to have them, then it’s better to remove them.
But if the images are important, you can compress them to reduce the size of the Excel file.
- Select the picture you wish to compress
- Navigate to the picture format tab
- Click ‘compress’
- Uncheck the check box that says ‘Apply only on this picture.’ This will help compress all the pictures at once hence saving time.
- Choose a resolution.
- Click ok
7. Remove hidden sheets, columns, and rows
Hidden data can make an Excel file large. If you remove them, then the file size will decrease significantly. Note that you should try this fix as the last option as it can break formulas. Always create a backup file of the original file as you fix it.
How to unhide sheets
- Select a sheet
- Right-click and select unhide
- Do the same for all sheets
How to unhide columns
- Highlight the whole sheet by pressing CTRL +A
- Right-click on the columns
- Click on Unhide
How to unhide rows
- Highlight the whole sheet by pressing CTRL +A
- Right-click on rows
- Click on Unhide
Now that you have unhidden all the data, you can delete the sheets, rows, and columns you deem unnecessary.
8. Use an online tool to make Excel file smaller
If you are in a hurry, you can use a one-click online tool. However, make sure it is a trustworthy website. You do not want your data to land in the wrong hands.