We all have came across than one excel file which does not contain that much information but still taking up a space of several MBs and makes our PC standstill while opening or editing it. You might have wondered what is the extra data that is stored in that file. Here are few tips that I found useful to troubleshoot and “clean” those files of all those junks.
1. Deleting ‘Names’ defined in the workbook
Whenever we define a name, that information gets stored in the Workbook. A common example will be Editing the default ‘Print Area‘ of a sheet which creates a new ‘Name’ for that area. You can see those defined names from the Name manager in Formulas Tab.
A normal sheet might have few which you have defined such as Print Area of sheets. But in certain cases the list contains thousands of names defined which have no meaning to us and refers to unknown sources such as given below.
The way to delete them is click on one and pressing page down key while pressing shift key until all is selected and then hitting Delete. Try avoiding the Print Area if you have defined or else delete all and then adjust the Print Area again sheet by sheet.
2. Hidden Sheets
One obvious reason for huge size is that your workbook contains many hidden sheets which of no use to you. Right click on any sheet name in the sheets tab and then choose unhide to view all hidden sheets. The unhide option will be disabled in case there are none. Unhide sheets one by one and then delete them if they are unnecessary.
3. Unnecessary formatting
Sometimes you apply formats to cells by selecting the entire row or columns. Depending on the formats applied, excel needs to store the format of individual cells which may increase the file size. So only apply format to the cells needed rather than that entire row or column.
4. Outside Links (Enable Contents)
You might be used to seeing and ‘Enable Content‘ message popping up while opening sheets. They are shown when the workbook has a link to an outside source or if the Excel thinks it’s unsafe. Trying to update such unnecessary links can increase the loading time. So in order to view all such links given in you workbook, select Edit Links from data tab and you can see all those instances where there is a reference to and outside data.
In case those are unnecessary, select them and choose break link. It won’t cause a #Ref error as it automatically saves the current value in the cell.
5. Macros
There is also a possibility that you Workbook contains Macros and is saved as Workbook.xlsm instead of normal .xlsx. Incase you did not create any such macros, go to ‘Save As’ option and select ‘Excel Workbook’ instead of ‘Excel Macro-Enable Workbook‘ and save a new copy and it won’t be containing any Macro.
These are few solutions which I found out to be working for such an issue and hope it solves your issues too.
Author Bio
Loved the article. We all use Excel often and this is a valuable article