How to reduce a huge excel file

Question

I have a small and simple file in   XLS with only one sheet  on this sheet just many cells with small text on number   file size 24Kb   But I made a lot of changes  copy and paste  extend formula  save    afterwards I deleted most of these changes and make 4 duplicates of this sheet with few data   Now my new file is VERY huge   2 5Mb    Where is the hidden data and how can I delete it    I have the same problem on real file with 300 sheets and 1 picture on each sheet   file size 280Mb

User · Answer

I have worked extensively in Excel and have found the following 3 points very useful

Find if there are cells which apparently do not hold any data but Excel considers them to have data

You can find this by using the following property on a sheet

ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Columns.Count

If this range is more than the cells on which you have data, delete the rest of the rows/columns

You will be surprised to see the amount of space it can free

Convert files to .xlsb format

XLSM format is to make Excel compliant with Open XML, but there are very few instances when we actually use the XML format of Excel. This reduces size by almost 50% if not more

Optimum way of storing information

For example if you have to save the stock price for around 10 years, and you need to save Open, High, Low, Close for a stock, this would result in (252*10) * (4) cells being used

Instead, of using separate columns for Open,High,Low,Close save them in a single column with a field separator Open:High:Low:Close

You can easily write a function to extract info from the single column whenever you want to, but it will free up almost 2/3rd space that you are currently taking up

User · Answer

I wrote a VBA file to add a tool cleaning these abnormally biggest file  This script clear all columns and rows after the last cells realy used to reset the last cells    Ctrl   End     and it also provides enable images compression   I dev an AddIns with auto install  just run it with macro enabled  to include in context menu many new buttons    Optimize Optimize and Save Disable Optimizer     This is based on KB of Microsoft office 2003 and answer of PP  with personals improvement     add compression of images fix bug for Columns feat compatibility with excel 2007 - 2010 -      more than 255 columns    SOLUTION   you can download my   xlam file ToolsKit  the main code is  Sub ClearExcessRowsAndColumns       Dim ar As Range  r As Double  c As Double  tr As Double  tc As Double     Dim wksWks As Worksheet  ur As Range  arCount As Integer  i As Integer     Dim blProtCont As Boolean  blProtScen As Boolean  blProtDO As Boolean     Dim shp As Shape     Application ScreenUpdating   False     On Error Resume Next     For Each wksWks In ActiveWorkbook Worksheets       Err Clear        Store worksheet protection settings and unprotect if protected        blProtCont   wksWks ProtectContents       blProtDO   wksWks ProtectDrawingObjects       blProtScen   wksWks ProtectScenarios       wksWks Unprotect          If Err Number   1004 Then          Err Clear          MsgBox      amp  wksWks Name  amp     is protected with a password and cannot be checked    vbInformation       Else          Application StatusBar    Checking    amp  wksWks Name  amp     Please Wait              r   0          c   0            Determine if the sheet contains both formulas and constants          Set ur   Union wksWks UsedRange SpecialCells xlCellTypeConstants   wksWks UsedRange SpecialCells xlCellTypeFormulas             If both fails  try constants only          If Err Number   1004 Then             Err Clear             Set ur   wksWks UsedRange SpecialCells xlCellTypeConstants           End If           If constants fails then set it to formulas          If Err Number   1004 Then             Err Clear             Set ur   wksWks UsedRange SpecialCells xlCellTypeFormulas           End If           If there is still an error then the worksheet is empty          If Err Number  lt  gt  0 Then             Err Clear             If wksWks UsedRange Address  lt  gt    A 1  Then                ur EntireRow Delete             Else                Set ur   Nothing             End If          End If           On Error GoTo 0          If Not ur Is Nothing Then             arCount   ur Areas Count              determine the last column and row that contains data or formula             For Each ar In ur Areas                i   i   1                tr   ar Range  A1   Row   ar Rows Count - 1                tc   ar Range  A1   Column   ar Columns Count - 1                If tc  gt  c Then c   tc                If tr  gt  r Then r   tr             Next              Determine the area covered by shapes              so we don t remove shading behind shapes             For Each shp In wksWks Shapes                tr   shp BottomRightCell Row                tc   shp BottomRightCell Column                If tc  gt  c Then c   tc                If tr  gt  r Then r   tr             Next             Application StatusBar    Clearing Excess Cells in    amp  wksWks Name  amp     Please Wait                 Set ur   wksWks Rows r   1  amp       amp  wksWks Rows Count                   Reset row height which can also cause the lastcell to be innacurate                 ur EntireRow RowHeight   wksWks StandardHeight                 ur Clear              Set ur   wksWks Columns ColLetter c   1   amp       amp  ColLetter wksWks Columns Count                    Reset column width which can also cause the lastcell to be innacurate                 ur EntireColumn ColumnWidth   wksWks StandardWidth                 ur Clear          End If       End If        Reset protection        wksWks Protect     blProtDO  blProtCont  blProtScen       Err Clear     Next     Application StatusBar   False       prepare les combinaison de touches pour la validation automatique de la fenetre       Application SendKeys    oe   TAB           ouvre la fenetre de compression des images     Application CommandBars ExecuteMso  PicturesCompress      Application ScreenUpdating   True End Sub   Function ColLetter ColNumber As Integer  As String     ColLetter   Left Cells 1  ColNumber  Address False  False   Len Cells 1  ColNumber  Address False  False   - 1  End Function

User · Answer

Look at posts like  http   www officearticles com excel clean up your worksheet in microsoft excel htm or http   www contextures on ca xlfaqApp html Unused  Basically  try Googling

User · Answer

i Change the format of file to   XLSX this change compress my file and reduce file size of 15

User · Answer

I had an excel file 24MB in Size  thanks to over a 100 images within  I reduced the size to less than 5MB by the following steps    Selected each Picture  cut it  CTRL X  and pasted it in special mode by ALT E S Bitmap option To find which Bitmap was still large  One has to select one of the files per sheet  then do CTRL A  This will select all Images  Double Click on any one image and the RESET Picture option appears on top  Click on reset picture and all Images that are still large show up  Do a CTRL Z  UNDO  and now again paste these balance images in BITMAP    BMP  like step 1    It took me 2 days to figure this out as this wasnt listed in any help forum  Hope this response helps someone  BR Gautam Dalal  India

User · Answer

I stumbled upon an interesting reason for a gigantic  xlsx file  Original workbook had 20 sheets or so  was 20 MB I made a new workbook with 1 of the sheets  so it would be more manageable  still 11 5 MB Imagine my surprise to find that the single sheet in the new workbook had 1 041 776  count  em   blank rows  Now it s 13 5 KB

User · Answer

I save files in  XLSB format to cut size  The XLSB also allows for VBA and macros to stay with the file  I ve seen 50 meg files down to less than 10 with the Binary formatting

User · Answer

If your file is just text  the best solution is to save each worksheet as  csv and then reimport it into excel - it takes a bit more work  but I reduced a 20MB file to 43KB

[excel] How to reduce a huge excel file

The answer is

Find if there are cells which apparently do not hold any data but Excel considers them to have data

Convert files to .xlsb format

Optimum way of storing information

Examples related to excel

Examples related to file

Examples related to vba

Examples related to size

Tags