In this post, we will discuss a really general difficulty of MS Excel File like large file size of MS Excel file and apply some ways to decrease the MS Excel File Size without removing data. There are so many reasons that can make an excel file large, sometimes MS Excel spreadsheet become so large while it really carrying very few data. You can ask for the solutions to the experts at office.com/setup.
A huge sized MS Excel file may cause many problems while working with the file like file will respond very late, need more time to store any modifications or upload and more. The main question is which part of an MS Excel file creates the large file size issue like:
- Large Data: Usually an excel file owning 100,000+ rows and multiple columns requires ample memory to save.
- Media Files: If your MS Excel file holding images or other media then it can turn your small excel file into a big one.
- Pivot Tables: Pivot table also impact the file size. It stores the original information so Pivot Table holds its own data collection.
- Formatting: If the MS Excel file is formatted though cell arrays aren’t in use then the file size might grow.
Sometimes MS excel file’s Meta-data and other unknown contents may also increase the file size. So, there are various problems of a large MS excel file size, therefore, we have to use different methods to decrease it. The solution may vary as per the contents of MS Excel if you are not certain that which one is concerning your excel file then you should implement them one by one or get support at office.com/setup. Let’s going through some of the methods one by one to turn a large excel file into a compatible size and decrease the size excel file.
- Clear Formatting: While formulating an excel file we use some formatting on the spreadsheet, therefore removing formatting will benefit you to store file size and increase the excel file performance. To remove the cell formatting,
- First, select the area over which you need to remove the formatting.
- Then go to the Home >> Editing Group >> Clear >> Clear Formats
If the excel files having many texts then it is the best way.
- Save File to Binary Format (XLSB): By saving an excel file to the binary format can decrease the size of excel file easily. So, go through the provided steps and store an Excel file in the XLSB format to reduce the file size.
- From the File menu, click on Save As and from the Save As dialog box, select the Excel Binary Workbook (.xlsb) file type and click on the Save button.
- Optimize the Applied Formulas: After excluding the unnecessary formulas, now we will optimize the applied formulas by applying the given steps:
- Avoid using the changeable or variable function like TODAY, NOW, INDIRECT, RAND, RANDBETWEEN, CELL, OFFSET, and INFO.
- And replace the VLOOKUP, HLOOKUP and LOOKUP function with INDEX & MATCH and nested IF functions with REPT, INDEX & MATCH, SUMIF(s) or Boolean logic
- Compress the File: Using the zip file option you can reduce the excel file size easily. By compressing an MS Excel file, you can decrease 10-15% of a file size. To Zip an Excel file,
- First of all, Right-click on the MS Excel File that you need to zip
- Now, go to the Send >> Compressed (zipped) folder option.
Remove the Extra Cells: To delete the visible extra table, columns, and cells,
- Select all the areas to show below your last row with content
- Then Press the Ctrl key with the ‘–’ button to delete the rows.
There are so many other ways also to decrease the MS Excel file size without deleting it, you can ask for it to Microsoft Office expert at office.com/setup.