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. There are steps to reduce Excel file size. That makes working with large Excel files easier.
Reduce Your Excel File Size
1. Remove Unnecessary Data.
Examine your data before you begin working on your Excel file. Check if any unnecessary information can be eliminated. 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. You might find valuable our blog on How to delete rows in excel to help you delete unnecessary data.
Related articles: How To Delete Charts in Excel
2. Compress images.
Images can take up a lot of space in an Excel file, so compress pictures them as much as possible. To reduce the image size use “Save as” option in Excel and save the file as a “Web page” format.
You can also use image editing software to compress images before inserting them into your Excel 2007 file.
Or use online tools that can help you compress images, such as compressjpg.com, tinypng.com, iloveimg.com, websiteplanet.com, and others.
Writers Note: Images can be a really big burden not only in Excel spreadsheets. In our experience having pictures in spreadsheets doesn’t provide the best insights to users. Try to replace these images with graphics, tables, or text to replace images.
Related Article: Excel Doesn’t Save Formatting. How To Solve?
3. Use formulas instead of data.
Wherever possible, use Excel functions instead of data. Formulas take up less space than data and can often be used to create the same results. 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 try our suggested Excel formulas or view our function and formula library.
Author’s Note: If you struggle with formulas try using AI-powered tools such as Excel Formula Generator. You can always reach out to Excel experts who can help with managing your spreadsheets and Excel formulas.
Related Article: How To Forecast Cashflow in excel?
4. How To Consolidate Data to Reduce Excel File Size
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. That will 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 see how to correctly fill up all the rows in Excel pop-up window.
Related Articles: Data Validation Tools and Tips For Excel To Prevent Errors
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. That will help you 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 where you want to insert the table. See the example below with the provided screenshot.
Related Articles: How To Use Pivot Tables For Data Analysis
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.
The Microsoft Office editorial team suggests reducing the Excel file size by saving the spreadsheet as a binary workbook (.xslb). This is different from the default Excel format (.xslx).
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:
Related Article: Creating Charts and Graphs in Excel
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 cells, or 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. Create a ZIP file when sending the Excel file.
If you need to send an Excel file via email, create a ZIP file. This will 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 the “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.
Related Articles: How To Change Page Orientation to Landscape in Excel?
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.
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.
New Articles
Speed up your spreadsheet tasks with Ajelix AI in Excel
