How to reduce file size in excel

How to reduce file size in excel

• 3.
  Financial modelling shouldbe collaborative. Collaboration reduces error, speeds up development time and lowers cost. The Financial Modelling Handbook is a collaborative, crowd-sourced guide to building better financial models using the FAST Standard.
• 4.
  Mayank Jain isa Financial Modeller with F1F9. He’s part of the VBA development team and is particularly interested in data visualisation and presentation.
• 6.
  Check the stylespresent in the model. Use shortcuts: Alt, H, J (in Excel 2010) Alt, O, S (in Excel 2003). Delete all the unwanted styles. Right click on the style and select “Delete” (or press “l” to delete it).
• 7.
  When removing styles,do not delete default styles or those styles which are being used in the model. For example, in step 3 above - ‘DateLong’ formatting is used for ‘1st model column start date’ so should not be deleted. If a style is deleted, cells using that style will revert to the default number style.
• 8.
  Note that thereis sometimes unintended information contained in styles data. In this case we can see that this model came from “XYZ company” (a dummy name in this case) There may also be external links to style information. In this case the style is linked to“c:winnt32system32command.com”
• 9.
  Excel tracks the“last cell” that is used. i.e. the cell that sits at the bottom right hand side of any calculations. Even if data or calculations are then deleted from that cell, Excel will still consider it the end cell. Therefore deleting unused content will not always reduce file size. You also have to reset the end cell.
• 10.
  First unhide allthe columns. To do this select all columns (hidden and unhidden) using Ctrl + a. Ctrl + Shift + 0 will unhide any hidden columns. (To hide them again use Ctrl + 0). Sometimes cells have been “hidden” by setting the column width to 0. In this case Control + Shift + 0 won’t work.
• 11.
  To deal withzero width columns: Press F5 Type the cell reference of the last column: “XFD5” (in 2007 and beyond) and “IV5” (in 2003) Select the column (Ctrl + Spacebar) Shift + Ctrl + Left arrow to select all the invisible columns.
• 12.
  After selecting allthe invisible columns we need to make them visible. To do so, we will increase the column width using Alt,O,C,W (in 2003) or Alt,H,O,W (in 2010). Enter a number in ‘increase column width’. In this case we are using 11 as the column width to make them visible.
• 13.
  Check the currentend cell in the model using ‘Ctrl + END’. Check if there is any data present outside the expected model timeline If it is required then the model timeline will need to be expanded. Otherwise it can be removed.
• 14.
  Delete all theadditional columns by using Ctrl + ‘-’ Save the workbook. Close it and reopen it. The end cells are now reset. These steps need to be repeated for all worksheets. You can also manually check this (using Ctrl + END) to see if the end cell has been successfully reset or not.
• 15.
  Conditional formatting increasesthe file size and calculation time. Functions like SUMPRODUCT and SUMIF also increase file size and calculation time. Name ranges should not contain errors, such as ‘#REF!’. Also check for hidden name ranges in the model. This can also increase calculation times. Any external links can be present in calculations, name ranges, conditional formatting, data validation, objects, macros, or charts. These may be hidden or “very hidden”. If they are not being used they should be removed to reduce file size and calculation time.