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.

Deleting 'Names' defined in the workbook

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.

Name Manager

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.

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.

Macros

These are few solutions which I found out to be working for such an issue and hope it solves your issues too.

Author Bio

More Under CA, CS, CMA

One Comment

Leave a Comment

Your email address will not be published. Required fields are marked *

Search Posts by Date

November 2020
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30