What Is Reduce File Size In Excel?
To Reduce the File Size in Excel, save the spreadsheet in the default file format “.xlsx,” rather than storing the file as a Binary Excel Workbook in the format “.xlsb.” Compressing images, converting irrelevant formulas to values, and clearing the Pivot Cache can also help reduce the file size. By implementing these strategies, users can optimize their Excel files for improved performance and efficiency.
Table of contents
Key Takeaways
- The XLSB file format is known for its speedy opening and saving capabilities. However, it is important to note that certain macro-enabled and query-based Excel files should not be converted to XLSB, as they may cause functional errors when operating as XLSB.
- If an Excel file in XLSX format is between 5MB to 7MB, it can be left as is. However, if the file size increases to double or triple digits in MBs, it is best to consider reducing it using one of the best-suited approaches.
- If there is a high usage of formulas, changing the calculation mode from “Automatic” to “Manual” in the “Formulas” tab can significantly speed up the process of opening, making changes, updating, and closing the Excel file.
Top 4 Methods To Reduce The File Size Of Excel
There are four simple methods to reduce the file size of an Excel workbook. These methods include:
- Saving the file in binary format
- Converting unnecessary formulas to values
- Compressing images
- Deleting pivot cache
Now, let us delve into each of these methods in detail.
#1 – Save The File In Binary Format
Saving a file in binary format is crucial in data processing, particularly for software developers and computer engineers. This format represents data using only two digits – 0 and 1 – allowing it to be easily transferred between different systems, regardless of their architecture or operating system. Binary files are smaller than other formats, such as text files, making them ideal for storage and fast transfer via networks. Furthermore, saving files in binary format ensures data integrity by maintaining accuracy during transfer. In addition, binary encoding enables high-level access to the underlying machine language code of programs and applications, facilitating analysis, debugging, and optimization processes. Properly saving the file in binary format can save invaluable resources for businesses seeking homogeneous information distribution conveniently and at scale.
To save the file in Binary Format, follow the below steps;
- Step 1: Select Save As from the File tab menu list.
- Step 2: Select the Browse option from the list.
- Step 3: The window called Save As pops up.
- Step 4: Select the Excel Binary Workbook option from the Save As Type drop-down list.
- Step 5: Select OK to save the file in binary format.
#2 – Convert Unnecessary Formulas To Values
To eliminate any potential errors or discrepancies that may arise when recalculating cells at a later point in time. Additionally, converting formulas to values also increases the speed of your spreadsheet as it reduces the calculation time required for each cell on your worksheet. Converting formulas to values also makes data easier to analyze and interpret since the data becomes static instead of dynamically changing with each spreadsheet recalculation. Converting unnecessary formulas to values allows professionals like yourself to work more efficiently while maintaining high accuracy and consistency within their worksheets.
To convert unnecessary formulas to values, follow the below steps;
- Step 1: Enter the data containing the formula.
- Step 2: Select the column containing the formula or the entire worksheet.
- Step 3: Click fn + f5 key from the keyboard.
- Step 4: The Go To window pops up. Select the Special button.
- Step 5: The window called the Go To Special opens.
- Step 6: Select the Formulas radio button and click OK.
- Step 7: Select the Paste Value by clicking the Paste drop arrow button from the Clipboard group of the Home tab.
- Step 8: Press shortcut keys CTRL + ALT + V to open the Paste Special window.
- Step 9: Select the Values radio button from the Paste list and click OK.
- Step 10: The column containing the formula will have values.
#3 – Compress the Image
The process of reducing the file size of an image without compromising its quality. This is particularly important in today’s digital era, where online platforms and web pages demand high-quality images that load quickly. Compressing an image can make it more manageable for uploading and downloading purposes while minimizing bandwidth usage, loading time, and storage requirements. It involves minimizing redundant data, removing unnecessary information that may not be visible or relevant to the image’s overall composition, and performing smoothing techniques on imperfections in the picture. Several tools are available online, and built-in features in various software packages allow for different levels of compression based on output intentions.
To Compress the Image into an Excel file, follow the below steps;
- Step 1: Select the image in the Excel file.
- Step 2: Select the Compress Pictures button from the Adjust group of the Picture Format tab.
- Step 3: The window known as the Compress Pictures opens.
- Step 4: Deselect the Apply only to this picture check box under the Compression options list so that every image of this worksheet can get compressed.
- Step 5: Select the E-mail (96 ppi): minimize document size for sharing radio button under the Resolution section.
- Step 6: Select the OK button.
The image will be compressed as shown.
#4 – Deleting Pivot Cache
Pivot Cache stores duplicate copies of data in memory, making it easier and faster for users to sort, filter, and analyze large datasets using Pivot Tables or Pivot Charts. The size of the cache can grow significantly and affect the response time and stability of the spreadsheet. Deleting Pivot Cache deletes all cached data associated with a pivot table or chart, forcing Excel to retrieve fresh data from the source database or worksheet. This operation could also reduce the file size if several pivot tables exist in a single workbook. Deleting Pivot Cache can be done manually or programmatically using VBA code or macros. It is crucial to note that deleting a cache will result in its recreation next time it is required, which may take longer than expected at the first run post deletion.
To delete Pivot Cache from the Excel File, follow the below steps;
- Step 1: Select the Pivot table.
- Step 2: Go to the Pivot Table Analyze tab.
- Step 3: Select the Options from the drop-down list of the Options section under the Pivot Table Name of the Pivot Table drop-down button.
- Step 4: The Pivot Table Options window pops up.
- Step 5: Select the Enable Show details and the Refresh data when opening the file check boxes under the Data menu bar.
- Step 6: Select the OK button.
Important Things to Note
- The zipped folder can be done by right-clicking the Excel file, selecting “Send,” and then clicking “Compressed folder.” This will reduce the file size while sharing. Multiple files can also be selected and compressed in a zipped folder.
- For larger data sets, using PivotTables over formulas to summarize data is recommended, as it will have less impact on the file size.
Frequently Asked Questions (FAQs)
To Reduce Excel File Size without opening, we should.
• Format Files from File Properties – This is an essential process that professionals must undertake to organize and manage their files easily. This feature enables the modification of file attributes, such as size, date modified, and author name, to identify group-specific information quickly. It also helps track file changes and control access to sensitive data by setting the appropriate permissions.
• Compress Excel File to ZIP / RAR – This is a useful strategy for professionals who need to reduce the size of their documents without compromising quality. This technique saves disk space and speeds up file sharing, particularly when working with large datasets containing many formulas, graphs, and other complex spreadsheet features.
The Excel File Size is too big due to the following reasons.
• Huge Information
• Pivot tables
• Add-ons and Plugins
• Formatting
• Hyperlinks
• Media files
• Compatibility issues
The FILELEN function is a pre-existing feature in Excel that falls under the File Function category. We can also utilize it as a VBA function within Excel. This means it can be input into macro code through the Microsoft Visual Basic Editor.
The FILELEN function is particularly useful for those who work with large amounts of data and need to determine the size of a file quickly. Using this function, we can easily obtain the file size in bytes. We can then convert it into a more readable format.
Syntax of FILENEN function in VBA is;
FileLen (file_path)
Download Template
This article must help understand the Reduce File Size in Excel methods and step-by-step guide. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Reduce Excel File Size. Here we explain how to reduce file size of excel using top 4 along with examples & downloadable template. You can learn more from the following articles –
Leave a Reply