Excel 2007 Plotting Pathetically Slow

tfjield

New Member
Joined
Aug 29, 2007
Messages
3
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!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Aug 29, 2007
Messages
3
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

New Member
Joined
Jul 29, 2004
Messages
14
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

New Member
Joined
Aug 29, 2007
Messages
3
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

New Member
Joined
May 1, 2006
Messages
16
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top