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

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

How to solve this issue?

Thanks

Jacques

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? 

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

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

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

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.

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

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