This article describes in detail why excel takes so long to open and how can you get around it.

If your excel takes forever to open, try out the following solutions…atleast one of them is sure to work.

1) Add more RAM:
Excel consumes a lot of memory so make sure you have enough RAM. For XP, you need atleast 512Mb of RAM.

2) Delete temp files
Make sure you have enough space on your system Drive. Empty out your recycle bin and delete all your temp files. Sometimes when we close our excel files, it’s temporary file (.tmp) gets created and whenever we open the excel next time it first checks if it needs to restore a crashed file.
To delete your temp files, go to start, then in the run command type in the following command :
%userprofile%\Local Settings
Then empty out both the temp and temporary internet files folder.

Also, remove Programs  you no longer use. Go to start and then settings and then in the control panel there is a Add/Remove Programs option and from the list simply remove the programs you feel are reduntant.

3) Avoid formulas that consume lots of space and adds more size to an excel file.
Following formula’s increase the excel file size:

a) Macro’s and codes in VBA.
b) Array formulae
c) Filter: Whenever you use filter, make sure you set it to “All” before saving your excel file and don’t leave it with filters on.

4) Lastcell used Issues:
Make sure your last cell in each tab is what it should be. Sometimes by mistake we press some key on cells we don’t use. Like if we hit spacebar on some cells it will increase the file size tremendously and the worst part is you won’t even be able to see it as it looks like a blank cell.

To get around this problem, use Ctrl+End to find your last cell used. And simply delete that cell using the “Delete” key.
Plus, we should avoid using too much (”") condition in our if formulas. Sometimes to avoid “NA” or “Ref” error we say that if cell A1 has no value, then cell B1 which is dependent on cell A1 should also be blank. So, we might enter the following formula in cell B1 = if(A1= “”, “”, A1) and if we drag it to the last cell in our spreadsheet it will make the file too heavy. As you can see from the figures below:

last-cell.JPG
In our first figure we have used a simple formula and in the second we have used “” in our if condition and dragged it down to the last cell.

last-cell1.JPG
It increased the size from 13 Kb to 3.5 Mb.

5)Close all other applications if you are trying to open a huge excel file.

6)Set recalculation to manual.This can be done using the “options” under the tools tab.

calculations-manual.JPG
And whenever you change any formula, hit F9.
F9 will calculate all workbooks. If you only wish to calculate active workbook press CTRL+ALT+ F9. And to calculate only active worksheet press Shift +F9.

7) Broken Links

Excel slows down immensely when there are broken links.

Broken links can arise under following situations:

1) Links to External Files:
Excel slows down calculations when some formulas have links to external files. That is still OK if there are not too many linked cells. However sometimes we delete those files.And when you open excel it asks you if you would like to update the links.

 links3.JPG

If you hit don’t update then it will retain the previous number. This can be very risky because if there are some internal broken links and you delete some cells which feed others then it will start giving you reference error. However when you have external links then if you delete the file it will not give you reference error and will keep the old number.
To get around this, you should keep track of the external files you are using and delete others that you no longer use. To check the list of external files, go to “links” under the “Edit” tab.

links2.JPG

2) Define Names
Although Naming cells in excel is a very handy feature. But it has disadvantages too.
If you delete the cells or range of cells that you have named then in your define name list, you will see a reference error.

 links6.JPG

And lots of such broken names can slow down excel a lot.
So we should keep checking our define name list to see if there are any broken links.
Moreover, when we move or copy some worksheet(tab) from one workbook to another, then all the defined names also move alongwith the worksheet. This is fine as long as names are defined in the same worksheet. However, if we have defined names in other worksheet and used it the one that we copied then it will keep referencing to the original workbook and link will be created between the two workbooks and users wouldn’t even realise this.
For e.g. in workbook “Book1″ we have two worksheets “sheet1″ and “Define Names”. And in sheet1 we have given a name “Brokenlinks” to some range of cells.

 links8.JPG

 And then in the “Define Names” tab we have used brokenlinks in our formula. Then if we only move and copy “Define Names” tab to some other workbook “list” then all the names will also move along with the worksheet but will keep referencing to the original worksheet i.e Book1.

links7.JPG
Hence it is very important that we keep updating our define name list.

 8)Defrag: It is recommended that you run a disk defragmenter every month.
Go to “start menu” and then “Programs”, then “Accessories” and then “under “System tools” you will find “Disk Defragmenter”.

links10.JPG
9)Disable Norton antivirus just for MS Office

In order to disable Norton antivirus for MS Office applications, open Norton antivirus and then select options under “other”. This will give you the option to disable the “office plug in”, just un-check it and when you open your excel file next time you will see it will open up so much faster than before.

10) Instead of double clicking on the excel file directly, open it through the “open” option on the “File” menu. You will notice your file will load much faster.

links9.JPG

Share This

Popularity: 21%



Reader's Comments

  1. vmancer | December 3rd, 2009 at 1:56 am

    wow .. I never thought the quotation marks can enhance the capacity of excel data

  2. Wild down | May 14th, 2010 at 4:17 pm

    FIne, this is a good post for new & experence user.

Leave a Comment

Close
E-mail It