“If you can’t describe what you are doing as a process,
you don’t know what you’re doing”
W. Edwards Deming

How to reduce file size in excel

When building models in excel sometimes files become large, slowing down your modelling progress and making simple calculations painstakingly slow.

Size is often a function of the number of calculations and complexity of formulae. There are also causes of excess file size that are not related to model complexity. This modelling guide explains how to reduce file size in excel, by dealing with two such causes – end cells and unused styles.

Comments

  1. Hi Sourav, It is good to know that you find it useful.

    Yes, you are right in saying that it is a very tedious task to delete each style one by one unless we have a VBA code. Therefore, to deal with such situations in our models, we have limited defined styles which we use and all others should be deleted.
    Hence, regardless of whether extra styles are used or not in the model, we remove them and apply our standard styles.

    Hope this helps.

    • Sourav Gangawat says:

      Yes, as a modeller we are always strict with the styles and format, however with the clients spreadsheet, wherein there are lot many cell styles to handle, still you will go ahead and remove them, and apply your standard styles?

      How do you remove them ? using a VBA code, the code I have take ages sometimes to delete the unused styles.

      The idea here is to understand what all are the various techniques to remove the cell style.

      Sourav

      • Hi Sourav,
        Thanks for the sharing your views.

        Yes, you are right, if we are working on client model and needs to turn it as per FAST standard then we usually removes all the extra styles regardless of whether it is used in the model or not.

        For removing the unused styles following are the ways available :-

        1) Remove it manually but it is very tedious task to do.

        2) Using a VBA code (on the basis of procedure used in the code, macro processing time will vary)

        Advantage:-
        i) It will delete all the styles automatically
        ii) It is easy to do (only one time investment of writing code)

        Disadvantage:-
        i) Time taking process (as it will check each cells in the workbook which styles are used)
        ii) Need to ensure any used style should not be deleted

        3) Use Excel – Add ins or Utilities which make our task easier

        Advantage:-
        i) It will delete all the styles automatically
        ii) Take less time as compare to VBA code
        iii) More reliable as compare VBA code

        Disadvantage:-
        i) Utilities or Add ins are quite costly
        ii) Need to check if any used styles are deleted or not

        4) Move model into a new workbook (We cannot move sheets, we have to cut paste the whole sheet data into new file) – Only used styles will move into new workbook.

        Advantage:-
        i) It will delete all the unused styles automatically
        ii) Most reliable
        iii) Only used styles will move into new workbook
        iv) Easy to do it
        v) No technical skills are required.

        Disadvantage:-
        i) Take more time as compare to utilities (but still faster than VBA code)
        ii) Need to check Name ranges in the model.
        iii) Need to re build macro buttons and assign macros if any.

        Now on the basis of our model (Size, Styles present) we have to select the best way out of four above and still we have to review our model after deleting these styles .

        Hope this will help you. In case of any further queries kindly let us know.

        Regards,
        Mayank

        • Sourav Gangawat says:

          Thanks for this..most of the time I end up doing the approach mentioned in the pointer 4.

          However, I used it little differently. Using cut/paste is a tedious task, and you might end up dealing with lot of external links. Instead of cut/paste, I use the below process:

          First I delete all the styles in spreadsheet using a code (deleting all styles is easy and quick, as it does not go to each and every cell!)

          Secondly, using a old model of the version (where all styles are), copy pasting the formats in the new model (where there are no styles). This will only give me used styles.

          Both the steps mentioned above can be done through VBA.

          …and this is it !

          Cheers,
          Sourav

  2. Sourav Gangawat says:

    This is useful. Deleting unused style one by one is a very tedious task, and you never know the style is used or not in the model (unless you have VBA code !).

    I have had issues with the models, where the cell styles were in 1000’s. Would be interested to know you thoughts on how you deal with those spreadsheets.

    Thanks,
    Sourav

Help make the handbook better