Large Excel file performance issue

[vandehey]

Hello, a user I'm working with has a pretty big Excel file he's
working with (12MB). It takes about 4 minutes to open. Once open,
there are some performance problems. We are using Excel 2007 SP2, and
the file is local. When we go to an empty row for example and delete
it, it takes about 15 minutes to complete the task. My CPU actually
hits 25% (it's a 4 core system).

Couple questions. 2007 has multithread support, so why doesn't this
hit 100% on all 4 of my cores? Any idea why a simple row deletion (row
2800) would cause a 15 minute stall, at which time Excel reports 'not
responding'? I didn't create this worksheet, so I don't know all the
details. Hyperthreading is enabled in the 'formula' section of
advanced in the prefs.

Any suggestions on increasing performance? Thanks for any help.

 

[Charles Williams]

Its difficult to say without looking at or analysing the workbook but here
are some comments:

Make sure you have Calculation set to manual and that the Google COM addin
is not installed

4 minutes to open:
assuming you have calculation set to manual its probably taking 4 minutes to
build the dependency tree.
using .XLSB is usually a bit faster than .XLSX

15 Minutes to delete a row:
- the only part of Excel 2007 that is multithreaded is the calculation
engine: try Ctrl-Alt-F9 to trigger a full calculation - you should hit 100%
on all 4 cpus, also this will give you an idea how long it takes to
calculate all the fomulae
- the 15 minutes is probably caused by Excel having to change all the
formulae in the workbook that contain references to the row that you are
deleting, and then update the dependency tree etc.

If a full calculation (ctrl/alt/F9) takes about the same time as a
recalculation (F9) then it may be worthwhile switching on
Workbook.ForceFullcalculation because this stops Excel updating the
dependency trees

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[vandehey]

Its difficult to say without looking at or analysing the workbook but here
are some comments:

Make sure you have Calculation set to manual and that the Google COM addin
is not installed

4 minutes to open:
assuming you have calculation set to manual its probably taking 4 minutesto
build the dependency tree.
using .XLSB is usually a bit faster than .XLSX

15 Minutes to delete a row:
- the only part of Excel 2007 that is multithreaded is the calculation
engine: try Ctrl-Alt-F9 to trigger a full calculation - you should hit 100%
on all 4 cpus, also this will give you an idea how long it takes to
calculate all the fomulae
- the 15 minutes is probably caused by Excel having to change all the
formulae in the workbook that contain references to the row that you are
deleting, and then update the dependency tree etc.

If a full calculation (ctrl/alt/F9) takes about the same time as a
recalculation (F9) then it may be worthwhile switching on
Workbook.ForceFullcalculation because this stops Excel updating the
dependency trees

Charles
___________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

Hi Charles, thanks a bunch for the reply. I know it's hard to guess
what is going on without looking at the file. Thanks for explaining
the multithread. Hitting cntrl + alt + F9 does indeed hit all of my
proc cores at 100%, so that is great. That took about 4 minutes to
finish. It makes sense that deleting a row is causing all of the
references to update all over the worksheet. I actually just did this
test again to verify my numbers. This will sound a bit silly, but
deleting a row took 39 minutes (on an older P4 3.2GHz proc). Just to
delete a row. Hitting F9 and Ctl + Alt + F9 takes about the same time
(4 minutes or so). You mentioned turning on
Workbook.ForceFullcalculation. I'm a newbie, and have no idea what
this means. Can you elaborate?

Unfortunately I don't know a ton about the file. Another user created
it, and I'm trying to support him and see what I can dig up. Is there
anything else I could look for to speed things up? Thanks again for
the help.

 

[Charles Williams]

Workbook.ForceFullCalculation:

Open the workbook
Hit Alt-F11 to get to the Visual Basic Editor

Hit Ctrl-R to get the project Explorer
in the Project - (VBAProject) window expand (hit the + sign) VBAProject
(your workbook name)
then expand (hit the +sign) Microsoft Excel Objects
then select ThisWorkbook
then hit F4
this should give you a Properties - This workbook window
in the window should be ForceFull Calculation
select it and set it to TRue
Go back to Excel by hitting Alt-F11
SaveAs something.xlsb
exit Excel
restart excel and reopen Something.xlsb

To work on reducing the 4 minutes calculation time see
http://msdn.microsoft.com/en-us/library/aa730921.aspx?ppud=4

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Its difficult to say without looking at or analysing the workbook but here
are some comments:

Make sure you have Calculation set to manual and that the Google COM addin
is not installed

4 minutes to open:
assuming you have calculation set to manual its probably taking 4 minutes
to
build the dependency tree.
using .XLSB is usually a bit faster than .XLSX

15 Minutes to delete a row:
- the only part of Excel 2007 that is multithreaded is the calculation
engine: try Ctrl-Alt-F9 to trigger a full calculation - you should hit
100%
on all 4 cpus, also this will give you an idea how long it takes to
calculate all the fomulae
- the 15 minutes is probably caused by Excel having to change all the
formulae in the workbook that contain references to the row that you are
deleting, and then update the dependency tree etc.

If a full calculation (ctrl/alt/F9) takes about the same time as a
recalculation (F9) then it may be worthwhile switching on
Workbook.ForceFullcalculation because this stops Excel updating the
dependency trees

Charles
___________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

Hi Charles, thanks a bunch for the reply. I know it's hard to guess
what is going on without looking at the file. Thanks for explaining
the multithread. Hitting cntrl + alt + F9 does indeed hit all of my
proc cores at 100%, so that is great. That took about 4 minutes to
finish. It makes sense that deleting a row is causing all of the
references to update all over the worksheet. I actually just did this
test again to verify my numbers. This will sound a bit silly, but
deleting a row took 39 minutes (on an older P4 3.2GHz proc). Just to
delete a row. Hitting F9 and Ctl + Alt + F9 takes about the same time
(4 minutes or so). You mentioned turning on
Workbook.ForceFullcalculation. I'm a newbie, and have no idea what
this means. Can you elaborate?

Unfortunately I don't know a ton about the file. Another user created
it, and I'm trying to support him and see what I can dig up. Is there
anything else I could look for to speed things up? Thanks again for
the help.

 

[vandehey]

Workbook.ForceFullCalculation:

Open the workbook
Hit Alt-F11 to get to the Visual Basic Editor

Hit Ctrl-R to get the project Explorer
   in the Project - (VBAProject) window expand (hit the + sign) VBAProject
(your workbook name)
  then expand (hit the +sign) Microsoft Excel Objects
  then select ThisWorkbook
    then hit F4
      this should give you a Properties - This workbook window
        in the window should be ForceFull Calculation
            select it and set it to TRue
               Go back to Excel by hitting Alt-F11
               SaveAs something.xlsb
exit Excel
restart excel and reopen Something.xlsb

To work on reducing the 4 minutes calculation time seehttp://msdn.microsoft.com/en-us/library/aa730921.aspx?ppud=4

regards
Charles
___________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com










Hi Charles, thanks a bunch for the reply. I know it's hard to guess
what is going on without looking at the file. Thanks for explaining
the multithread. Hitting cntrl + alt + F9 does indeed hit all of my
proc cores at 100%, so that is great. That took about 4 minutes to
finish. It makes sense that deleting a row is causing all of the
references to update all over the worksheet. I actually just did this
test again to verify my numbers. This will sound a bit silly, but
deleting a row took 39 minutes (on an older P4 3.2GHz proc). Just to
delete a row. Hitting F9 and Ctl + Alt + F9 takes about the same time
(4 minutes or so). You mentioned turning on
Workbook.ForceFullcalculation. I'm a newbie, and have no idea what
this means. Can you elaborate?

Unfortunately I don't know a ton about the file. Another user created
it, and I'm trying to support him and see what I can dig up. Is there
anything else I could look for to speed things up? Thanks again for
the help.

Wow, this was a HUGE help. Deleting rows now is almost instant, and
opening the file now just takes about 30 seconds. This was great,
thanks a bunch for the help.