From data to report in one minute or less. Ajelix BI is launched! 🚀 Get started for free.

10 Tips on How To Reduce Excel File Size

  • Author:
    Agnese
  • Published on:
    January 10, 2023
  • Category:
  • Last updated:
    April 6, 2024
  • Tags:
Compress Excel File Size - 10 Tips on How To Compress Excel File Size - Ajelix Blog

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

Reduce Excel file size - infographic ajelix
Infographic with key elements to reduce the file size of your Excel file.

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:

  1. Select the data you want to consolidate.
  2. On the Data tab, click Consolidate.
  3. In the Consolidate dialog box, select the function you want to use to combine the data (e.g., Sum, Average, etc.).
  4. In the Reference box, enter the cell references for the data you want to combine.
  5. Click Add to add the references to the All References list.
  6. Repeat steps 4 and 5 for all the data you want to consolidate.
  7. Select the location where you want the consolidated data to appear.
  8. 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

consolidate data
Consolidate your data in Excel from other spreadsheets. Screenshot from Excel worksheet.

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

how to insert table in excel
Screenshot from Excel on how to insert the table in your spreadsheet.

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:

svae your excel file in xlsb format to minimize file size. Excel Binary Workbook
Save your Excel file in .xlsb format

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.

save your excel file in ZIP format to compress file size
Save your Excel file in ZIP format to reduce the file size.

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

What is the maximum size of the Excel file?

The maximum size of the Excel file is 2 GB (gigabytes).

Can Excel handle more than 1 million rows?

Excel can handle 1,048,576 rows. If you need more rows you should consider separating the data into different worksheets.

At what size Excel file starts crashing?

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.

Struggling with your Excel formulas?
Looking for a faster and easier way to write Excel formulas? Try AI Excel Formula Generator and turn your text into formulas with just a few clicks.

Try for free

New Articles

Speed up your spreadsheet tasks with Ajelix AI in Excel

Excel formula generator from Ajelix - more than 15 ai tools for spreadsheets

Similar posts

How to analyze data in Excel spreadsheet banner by author

How To Analyze Data In Excel Spreadsheet

April 4, 2024
Excel is the workhorse of data analysis in countless businesses. It can unlock valuable insights from spreadsheets, but for many, its pow…
How to add drop down list in Excel banner

How To Add Drop Down List in Excel

November 2, 2023
Let’s take a look at how to add drop down list to make your spreadsheets more professional and user-friendly. We’ll take a lo…
How to add checkbox in Excel banner

How To Add Checkboxes In Excel

October 31, 2023
This article will cover steps to add checkboxes in Excel with screenshots and tips to help you tackle this task. Learn how to enable the …

Online Calculators

Free future value calculator online banner

Free Future Value Calculator Online: Calculate FV

June 13, 2024
A future value calculator helps you estimate how much an investment will be worth in the future, taking into account factors like interes…
Free IRR calculator online: calculate your internal rate of return banner

Free IRR Calculator: Calculate Internal Rate of Return

June 11, 2024
IRR formula in Excel screenshot from spreadsheet for IRR calculator
Net present value banner

Free NPV Calculator Online: Calculate Net Present Value

June 6, 2024
An NPV calculator is a tool that helps you assess the profitability of an investment by calculating its Net Present Value (NPV). Calculat…
Calculate markdown metric with free online calculator banner

Free Markdown Calculator Online

June 4, 2024
A markdown calculator is used by businesses to maximize profits. They track how much you discount inventory, helping you identify areas t…
free clt calculator: calculate customer lifetime value online banner by ajelix

Free CLV Calculator: Calculate Customer Lifetime Value

May 30, 2024
Customer lifetime value calculator, also referred to as CLV calculator or lifetime value (LTV), is a metric that businesses use to estima…
Free debt to asset ratio calculator online banner

Free Debt To Asset Ratio Calculator Online

May 28, 2024
The debt to asset ratio calculator is a tool to assess a company’s financial health by indicating the proportion of its assets fina…

Business Intelligence

Hoe to calculate business growth rate with examples and formula banner by ajelix

How to Calculate Business Growth Rate? Formula & Examples

April 16, 2024
Whether you’re a seasoned entrepreneur or just starting, measuring progress is vital to identify areas of strength, pinpoint opport…
How to calculate average revenue per user formula & meaning example banner

Calculate Average Revenue Per User: ARPU Formula & Meaning

April 11, 2024
The metrics are king, there’s no doubt about it. They tell the story of your company’s health, customer base, and overall suc…
How to calculate revenue growth in Excel with examples and formulas banner by ajelix

How To Calculate Revenue Growth Rate: Formula & Examples

April 9, 2024
Revenue growth rate is a performance indicator, that helps understand if your sales efforts are yielding positive results. Tracking it ov…
What is collaborative business intelligence (BI) and analytics blog article banner by ajelix

Collaborative Business Intelligence (BI) & Analytics

April 2, 2024
Imagine a world where everyone in your organization, from sales reps to executives, can easily access, analyze, and discuss data together…
top 7 business intelligence trends (bi) 2024 banner ajelix

7 Business Intelligence Trends (BI) 2024

March 28, 2024
This year, Business intelligence trends are all about harnessing the power of technologies like Generative AI and Natural Language Proces…
Financial business intelligence meaning and examples banner by ajelix team

Financial Business Intelligence Meaning & Examples

March 26, 2024
Measuring business financial health is vital for any manager or owner to understand what is happening in your company. However, you can’t…