Excel 2013 is very slow in loading large workbooks, is there a way to open just the tab I want?

Excel 2013 is extremely slow in opening my large workbook. Sometimes it won't respond at all and freezes my computer.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Hi, sorry but you cannot open only one tab from a workbook
If this post is helpful or answers the question, please mark it so, thank you.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

So does anyone know a way to open it faster? Or better?

Patrick Little

15 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I have exactly the same problem: large workbook opens very slowly in Excel 2013.

 

How to solve this issue?

 

Thanks

 

Jacques

63 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I have benchmarked file opening extensively, comparing Excel 2010 with Excel 2013.  Excel 2013 takes roughly twice as long to open XLS and XLSM workbooks.  eg a workbook that takes 7 seconds with Excel 1010 takes 13 seconds with 2013.  This is massively slower.  

 

What is the solution? 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hello Peter,

 

Can you tell me a little more about what is in the workbook you benchmarked, and how you opened it?

 

1. First of all, are you opening locally or over a network?

2. How large is the book?

3. How many formulas are in it, and does it make a difference if you set calc to manual before you open the book?

4. Does it have external data ranges that are set to refresh on open?

 

I'll try to test myself, but it would really help to know where to start.

 

Thanks,
Anita Oakley

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

As far as I'm concerned (Jacques Deseure), here are my answers to your questions:

 

1. I am opening my workbook locally.

2. Workbook is 2254 kB. 19 Worksheets (3 are "very hidden").

3. Almost everything made with VBA: many User Forms, 24328 code lines, 792 procedures and 1595 controls. No formulas: everything calculated through VBA code. No difference if calc is set to manual.

 

Best regards

Jacques

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks Jaques!

 

Sure wish I had access to that file. If we opened a case for you (no charge) would you be willing to share it with us to try to determine what's going on?

 

Regards,

Anita

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

1. Opened from local hard drive

 

2. I have a number of examples.  The simplest one (in that there is no auto_open or VBA executed on loading and the spreadsheet does NOT recalculate when it is loaded) is 4,175 KB.  I previously saved it in Excel 2013 xls format so it loads cleanly (ie it does not recalculate etc).  When I load Excel 2010, then open the workbook from within Excel 2010 the time is four seconds; When I load Excel 2013 then open the workbook the time to open is 7 seconds.  This spreadsheet has 185 sheets (it's a workbook of examples for a product we sell).

 

3. There are multiple formulas on each sheet and about 20 charts.  HOWEVER, the workbook does NOT recalculate when it is opened.  As a result setting calc to manual before opening it makes no difference at all.

 

4. No external data ranges.  No external links of any type.

 

I can't provide an example as all rely on an Excel add-in being present. However, this makes no difference to the load time and no functions in the add-in are executed.

 

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks Anita!

 

Fortunately it'll be not necessary for me to open a case. It would have been a pleasure for me to share my file with you, but in the meantime I have found the culprit for my workbook myself.

 

In 'ThisWorkbook', 'Workbook_Open' event, I had a reference to a module 'ProtectWorksheets' which password protects all sheets and the workbook itself when loading the workbook.

 

I have 'commented out' this reference and now the file is loading normally.

 

Of course the question remains why that reference to 'ProtectWorksheets' didn't cause any problem in Excel 2007 nor in Excel 2010.

 

I hope you might find the solution.

 

Best regards

 

Jacques

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Jacque,

 

Great - that gives me somewhere to go in testing! In fact, we did have one other report where a VS developer found that his macros were running much slower when they protected sheets. Let me try testing with similar code.

 

Regards,

Anita Oakley

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated January 7, 2020 Views 28,054 Applies to: