Excel 2007 Plotting Pathetically Slow

[tfjield]

Hi Guys,

I was surprised not to see more questions regarding this here, but... Here goes.

My plots in Excel 2007 (typically scatter plots) take FOREVER. Usually I will have several thousand data points. Let's say 5k, for example.

When I create the chart, it comes up OK at first, even though the painting is much, much slower than the older versions of excel. But then if I try to modify the chart, Excel becomes next to worthless.

Here's how to duplicate the results...

Open a new workbook and in cell A3, enter the number 1. In cell A4 enter '=A3+1' Now drag A4 down until you have numbers 1 to 5000 in column A. In column B, next to each item in A, just enter '=RND()*100'

Nothing complicated here. Just a list of 1 to 5000 in column A, and 5000 random numbers in column B. Select the data, insert scatter plot with lines.

Now, right click on the X axis and change the range to 100 to 4000.

Now, right click on the lines...

Usually at this point Excel becomes unusable... It hangs for minutes, I get "(not responding)" in the title bar, etc.

I have duplicated this behavior on several machines. All running XP SP2, Excel 2007.

Again, this problem DOES NOT EXIST in the older versions of Excel.

Any solutions out there? Thanks in advance!

 

[SydneyGeek]

It's a known issue with Excel 2007. Look for posts by Jon Peltier -- he does a lot of work with charting, and also is unimpressed.

When MS increased the worksheet size so dramatically, they opened themselves up to a whole heap of calculation issues. Charting is just one of the more obvious ones.

Denis

 

[tfjield]

Hi Denis,

Will do, thanks for the info.

As far as you know, there is no fix? I figured it was probably adding all the little graphical doodads, like rounded ends for the lines and all that stuff...

Thanks!
Todd

 

[MarianoMcCoy]

Hi. Maybe I solved an issue like this some time. I think the problem was the automatic recalculation of too much formulas. I solved deactivating the automatic recalc. entering the new values into the cells and then activating the recalc again. All with VBA scripts. Hope to be useful.... Best Regards, Mariano

 

[tfjield]

Hi Mariano,

I got a patch from Microsoft that seems to help:

http://support.microsoft.com/kb/938538

Unfortunately, you need to call them and contend with their customer service to get it. It increases the speed of the repaints for reasonably sized charts (several thousand data points) but on the big charts, it still becomes unusable.

I'll try your suggestion and see if it makes a difference.

Thanks!
Todd

 

[Justin L]

Mariano,
Thanks for that post! I had a problem upgrading to 2007 when it sorted & filtered 500 rows with formulas. It would take a couple minutes in Excel 2007 when it only took 2 seconds in Excel 2000. It's actually faster than my 2000 version now.

I entered the code to switch to manual recalc, run the sort, then switch back to auto recalc. It works perfect!

On Error GoTo Finish
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("B2")
If Intersect(Target, rng) Is Nothing Then GoTo Finish
ActiveSheet.unprotect
Application.Calculation = xlManual
Range("B4:t501").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("B4:B497").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B1:B2"), unique:=False
Application.Calculation = xlAutomatic
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("B2").Select
Finish:

End Sub

I underlined the lines for calculation

Justin