10 Tips on How To Reduce Excel File Size
Working with large Excel files can be a real pain, especially when it comes to file size. Large files can slow down your computer, cause crashes, and make it difficult to share and collaborate on your data. However, there are some steps you can take to reduce Excel file size and make working with large Excel files much more manageable.
Summary
Reduce Your Excel File Size
1. Remove Unnecessary Data.
Before you even start working on your Excel file, look at the data you have and see if any unnecessary information can be removed. This could be old data that is no longer relevant or blank rows and columns that don’t contain any data. Check if the formatting is applied to relevant rows with data inside them. By removing unnecessary data, you can significantly reduce the size of your file.
2. Compress images.
Images can take up a lot of space in an Excel file, so it’s important to compress them as much as possible. Use the “Save as” option in Excel and save the file as a “Web page” to reduce the size of the images. You can also use image editing software to compress images before inserting them into your Excel file. Or use online tools that can help you compress images, such as compressjpg.com, tinypng.com, iloveimg.com, and others.
3. Use formulas instead of data.
Wherever possible, use formulas instead of data. Formulas take up less space than data and can often be used to create the same results. For example, instead of copying and pasting data into your file, use the “SUM” formula to add up a column of numbers. If you’re working with large datasets view our suggested Excel formulas for this job or view our function and formula library.
4. Consolidate data.
If you’re working with multiple Excel files that contain similar data, consider consolidating the data into one file. Data consolidation can help you improve data organization as you would have all data in one master spreadsheet it would save time, and improve reporting and accuracy as you would work with one spreadsheet instead of many. Here’s a full list of steps that you should take if you want to consolidate data:
- Select the data you want to consolidate.
- On the Data tab, click Consolidate.
- In the Consolidate dialog box, select the function you want to use to combine the data (e.g., Sum, Average, etc.).
- In the Reference box, enter the cell references for the data you want to combine.
- Click Add to add the references to the All References list.
- Repeat steps 4 and 5 for all the data you want to consolidate.
- Select the location where you want the consolidated data to appear.
- Click OK.
View an example below to understand how the pop-up window looks in Excel and how to correctly fill up all the rows.
5. Use tables.
Excel tables are a great way to organize data and make it easier to work with. When you create a table in Excel, it automatically gets a filter at the top of each column, so you can quickly sort and filter the data. This can help you to find the information you need quickly and efficiently. To insert a table, click on the “Insert” button on the top and choose “Table”. In the pop-up window choose the location where you want to insert the table. See the example below.
6. Save the file in the appropriate format.
Excel offers different file formats that you can save your file in, like xlsx or csv. Each format is suitable for different purposes, choose the one that best fits your needs and the amount of data you need to work with. Learn how to export Excel files in different files. Save your file in xlsb format can help you reduce the file size a lot.
What is xslb format in Excel?
XSLB format is an Excel Binary Workbook format used to store Microsoft Excel documents. It is an XML-based file format that stores workbook data in a compressed binary file. XSLB files are smaller in size, which helps to reduce disk space and save time when transferring files over the Internet.
To save your file in .xslb format go to File > Save As > Browse > Find the Excel Binary Workbook (*.xlsb) type and click save. View the screenshot below with these steps:
7. Use data validation to reduce file size.
Data validation is a great way to ensure that only the correct data is entered into a worksheet. You can set up rules for data entry, such as only allowing a certain range of numbers, or only allowing certain text. This can help to minimize errors and reduce the amount of data that needs to be cleaned. Here are Excel data validation tools you might find useful.
8. Keep your spreadsheet organized.
A cluttered spreadsheet can be difficult to work with and can make it hard to find the information you need. Take time to organize your data by adding headings, formatting, and using conditional formatting. Learn more about conditional formatting. We created an article on how to keep your excel worksheets organized to learn more.
9. Use Excel add-ins to reduce file size.
Excel add-ins can help you perform complex tasks easily, and some of them can help minimize file size. There are add-ins available that can compress images, remove duplicates, and even merge multiple worksheets into one. Be sure to research different add-ins and find the ones that will work best for your specific needs. View our recommendation for Excel Add-ins.
10. Create a ZIP file when sending the Excel file.
If you need to send the excel file in an email make sure to create a ZIP file, that will help you reduce the file size. To create a ZIP file when sending an Excel file, first select the Excel file you wish to compress. Right-click the file, then select “Send to” and choose “Compressed (zipped) folder”. This will create a ZIP file with the same name as the original Excel file. You can then send the ZIP file instead of the Excel file, saving time and bandwidth. See the screenshot below with the example of how to save your Excel file in ZIP format.
Conclusion on how to reduce Excel file
Did you know that spreadsheet cluttering is a service that you can request? There are many professionals who are willing to clean your spreadsheets and reduce the loading time for your worksheets. Ajelix team are experienced professionals who can help you organize and clean spreadsheets for maximum performance. Working with large Excel files can be challenging, but following these tips can minimize file size and make the process more manageable. Remember to remove unnecessary data, compress images, use formulas, consolidate data, create tables, use worksheets strategically, save the file in the appropriate format, use data validation, keep your spreadsheet organized, and take advantage of Excel add-ins. These simple steps will not only make your Excel files smaller but also make working with them much more efficient.
Frequently Asked Questions
The maximum size of the Excel file is 2 GB (gigabytes).
Excel can handle 1,048,576 rows. If you need more rows you should consider separating the data into different worksheets.
The exact size at which an Excel file will start crashing depends on the configuration of the computer, including the amount of RAM and processor speed. Generally, files larger than 250 MB may experience performance issues or crashes.
Learn more about Excel and Google Sheets hacks in other articles. Stay connected with us on social media and receive more daily tips and updates.
