I have a large workbook (14MB) that contains 3 months of 1 minute data in
three tabs that is cut and pasted from another spreadsheet that does the
calculations on the monthly data and another tab that calculates 1 hr
averages from the three monthly tabs. The workbook has been set to manual
calculation. When I add a simple line chart of the 1 hr average data (2 y
variables, date as text as the x variable) to the workbook, excel wants to do
"something" EVERY time I click the chart with a mouse. I look at Windows
Task Manager, and the excel process shows 25% usage (1 of the 4 CPU's maxes
out).

Thinking that the issue was the number of calculations occurring in the
workbook, I went thru a painstaking process of of converting all the
calculated data to values, eliminating all calculation in one version of the
spreadsheet. This did not resolve the problem.

Can anyone explain why this is happening, and what can be done to prevent
this from happening?

Dale,
First question: what version of Excel are you using?

First thing I'd suggest doing is taking the one sheet that has the data to
be charted and copying that sheet into its own workbook and graphing in there
and see if there's an improvement.

Quick Tip for future use: when you have a large spreadsheet and want to
convert ALL formulas and linked data to their values, first select all of the
cells on a sheet and use Edit | Copy; then, without unselecting the cells,
turn right around and use
Edit | Paste Special and choose the [Values] option. That will do it
quickly and painlessly for you. Repeat on any other sheets in the book you
need to do the same thing for.

"Dale" wrote:

I have a large workbook (14MB) that contains 3 months of 1 minute data in
three tabs that is cut and pasted from another spreadsheet that does the
calculations on the monthly data and another tab that calculates 1 hr
averages from the three monthly tabs. The workbook has been set to manual
calculation. When I add a simple line chart of the 1 hr average data (2 y
variables, date as text as the x variable) to the workbook, excel wants to do
"something" EVERY time I click the chart with a mouse. I look at Windows
Task Manager, and the excel process shows 25% usage (1 of the 4 CPU's maxes
out).

Thinking that the issue was the number of calculations occurring in the
workbook, I went thru a painstaking process of of converting all the
calculated data to values, eliminating all calculation in one version of the
spreadsheet. This did not resolve the problem.

Can anyone explain why this is happening, and what can be done to prevent
this from happening?

JLatham - Am I missing something or was your post blank?

"Dale" wrote:

I have a large workbook (14MB) that contains 3 months of 1 minute data in
three tabs that is cut and pasted from another spreadsheet that does the
calculations on the monthly data and another tab that calculates 1 hr
averages from the three monthly tabs. The workbook has been set to manual
calculation. When I add a simple line chart of the 1 hr average data (2 y
variables, date as text as the x variable) to the workbook, excel wants to do
"something" EVERY time I click the chart with a mouse. I look at Windows
Task Manager, and the excel process shows 25% usage (1 of the 4 CPU's maxes
out).

Thinking that the issue was the number of calculations occurring in the
workbook, I went thru a painstaking process of of converting all the
calculated data to values, eliminating all calculation in one version of the
spreadsheet. This did not resolve the problem.

Can anyone explain why this is happening, and what can be done to prevent
this from happening?

JLatham,

The spreadsheet was developed in 2003, then saved in 2007. I also tried
cutting and pasting the data into a new spreadsheet in 2007.

Your quick tip was how I converted the formulas to values. It was
painstaking in that I could only perform one mouse click at a time with 5
minutes of the spreadsheet being "locked up" because of the problem described.

Depends on which post you're speaking of - the board has really been acting
NUTZ!! for a couple of days - sometimes not showing post contents, sometimes
not even showing entire day's worth of postings in IE7.

Anyhow, I hate to tell you this, but the problem is Excel 2007. Period.
The graphic engine for 2007 was rewritten and not brought forward from
earlier version of Office/Excel. I encountered the problem very shortly
after the release of 2007 when trying to migrate a 2003 file over to 2007 for
a client so that we could take advantage of 2007's 1-million+ rows of data
storage to meet his data collecting and charting desires. It took the 500K+
rows of data he had and tried to make 52 charts from it. It failed to do so
on a single worksheet. We had to split the data up across 52-worksheets and
do a single chart on each one. It took over 10 minutes to create the charts
and, as you've experienced, making any change to any single item in any one
chart took an equally long time to take effect.

We went back to 2003, kept the split across the 52 sheets and the entire
chart building process was completed in about a minute (on a single core
system with less RAM and a slower clock than the dual-core systems running
Excel 2007).

This slowdown in charting in Excel 2007 has been documented and/or commented
on numerous times. I personally sent the files I'd encountered the problem
with in to Microsoft for analysis, so I suppose I've done all I can in that
area: provided them with problem samples. Charles Williams, at
www.decisionmodels.com noted that charting in 2007 was about a magnitude
(i.e. 10x) slower than in earlier versions - I definitely agree with him.

Wish I had better news for you - but the bottom line at this point in time,
IMHO, is that if you are heavy into charting/graphing it's best to stick with
Excel 2003 rather than moving on up to 2007.

"Dale" wrote:

JLatham - Am I missing something or was your post blank?

"Dale" wrote:

I have a large workbook (14MB) that contains 3 months of 1 minute data in
three tabs that is cut and pasted from another spreadsheet that does the
calculations on the monthly data and another tab that calculates 1 hr
averages from the three monthly tabs. The workbook has been set to manual
calculation. When I add a simple line chart of the 1 hr average data (2 y
variables, date as text as the x variable) to the workbook, excel wants to do
"something" EVERY time I click the chart with a mouse. I look at Windows
Task Manager, and the excel process shows 25% usage (1 of the 4 CPU's maxes
out).

Thinking that the issue was the number of calculations occurring in the
workbook, I went thru a painstaking process of of converting all the
calculated data to values, eliminating all calculation in one version of the
spreadsheet. This did not resolve the problem.

Can anyone explain why this is happening, and what can be done to prevent
this from happening?

Thank you for your response! Definitely not the news I wanted to hear! I
too upgraded to Excel 2007 hoping to take advantage of the 1-million+ rows of
data and the larger array size. Pretty disappointing that Microsoft can not
upgrade their products without causing issues. Hopefully, this will get
fixed!

"JLatham" wrote:

Depends on which post you're speaking of - the board has really been acting
NUTZ!! for a couple of days - sometimes not showing post contents, sometimes
not even showing entire day's worth of postings in IE7.

Anyhow, I hate to tell you this, but the problem is Excel 2007. Period.
The graphic engine for 2007 was rewritten and not brought forward from
earlier version of Office/Excel. I encountered the problem very shortly
after the release of 2007 when trying to migrate a 2003 file over to 2007 for
a client so that we could take advantage of 2007's 1-million+ rows of data
storage to meet his data collecting and charting desires. It took the 500K+
rows of data he had and tried to make 52 charts from it. It failed to do so
on a single worksheet. We had to split the data up across 52-worksheets and
do a single chart on each one. It took over 10 minutes to create the charts
and, as you've experienced, making any change to any single item in any one
chart took an equally long time to take effect.

We went back to 2003, kept the split across the 52 sheets and the entire
chart building process was completed in about a minute (on a single core
system with less RAM and a slower clock than the dual-core systems running
Excel 2007).

This slowdown in charting in Excel 2007 has been documented and/or commented
on numerous times. I personally sent the files I'd encountered the problem
with in to Microsoft for analysis, so I suppose I've done all I can in that
area: provided them with problem samples. Charles Williams, at
www.decisionmodels.com noted that charting in 2007 was about a magnitude
(i.e. 10x) slower than in earlier versions - I definitely agree with him.

Wish I had better news for you - but the bottom line at this point in time,
IMHO, is that if you are heavy into charting/graphing it's best to stick with
Excel 2003 rather than moving on up to 2007.

"Dale" wrote:

JLatham - Am I missing something or was your post blank?

"Dale" wrote:

I have a large workbook (14MB) that contains 3 months of 1 minute data in
three tabs that is cut and pasted from another spreadsheet that does the
calculations on the monthly data and another tab that calculates 1 hr
averages from the three monthly tabs. The workbook has been set to manual
calculation. When I add a simple line chart of the 1 hr average data (2 y
variables, date as text as the x variable) to the workbook, excel wants to do
"something" EVERY time I click the chart with a mouse. I look at Windows
Task Manager, and the excel process shows 25% usage (1 of the 4 CPU's maxes
out).

Thinking that the issue was the number of calculations occurring in the
workbook, I went thru a painstaking process of of converting all the
calculated data to values, eliminating all calculation in one version of the
spreadsheet. This did not resolve the problem.

Can anyone explain why this is happening, and what can be done to prevent
this from happening?

Dale,
The board is still messed up - I couldn't read your last reply at all (blank
content). Hopefully you'll be able to read this, if so, if you have any
further questions about this, try emailing me direct at:
(remove spaces) Help From @ jlatham site.com


"JLatham" wrote:

Depends on which post you're speaking of - the board has really been acting
NUTZ!! for a couple of days - sometimes not showing post contents, sometimes
not even showing entire day's worth of postings in IE7.

Anyhow, I hate to tell you this, but the problem is Excel 2007. Period.
The graphic engine for 2007 was rewritten and not brought forward from
earlier version of Office/Excel. I encountered the problem very shortly
after the release of 2007 when trying to migrate a 2003 file over to 2007 for
a client so that we could take advantage of 2007's 1-million+ rows of data
storage to meet his data collecting and charting desires. It took the 500K+
rows of data he had and tried to make 52 charts from it. It failed to do so
on a single worksheet. We had to split the data up across 52-worksheets and
do a single chart on each one. It took over 10 minutes to create the charts
and, as you've experienced, making any change to any single item in any one
chart took an equally long time to take effect.

We went back to 2003, kept the split across the 52 sheets and the entire
chart building process was completed in about a minute (on a single core
system with less RAM and a slower clock than the dual-core systems running
Excel 2007).

This slowdown in charting in Excel 2007 has been documented and/or commented
on numerous times. I personally sent the files I'd encountered the problem
with in to Microsoft for analysis, so I suppose I've done all I can in that
area: provided them with problem samples. Charles Williams, at
www.decisionmodels.com noted that charting in 2007 was about a magnitude
(i.e. 10x) slower than in earlier versions - I definitely agree with him.

Wish I had better news for you - but the bottom line at this point in time,
IMHO, is that if you are heavy into charting/graphing it's best to stick with
Excel 2003 rather than moving on up to 2007.

"Dale" wrote:

JLatham - Am I missing something or was your post blank?

"Dale" wrote:

I have a large workbook (14MB) that contains 3 months of 1 minute data in
three tabs that is cut and pasted from another spreadsheet that does the
calculations on the monthly data and another tab that calculates 1 hr
averages from the three monthly tabs. The workbook has been set to manual
calculation. When I add a simple line chart of the 1 hr average data (2 y
variables, date as text as the x variable) to the workbook, excel wants to do
"something" EVERY time I click the chart with a mouse. I look at Windows
Task Manager, and the excel process shows 25% usage (1 of the 4 CPU's maxes
out).

Thinking that the issue was the number of calculations occurring in the
workbook, I went thru a painstaking process of of converting all the
calculated data to values, eliminating all calculation in one version of the
spreadsheet. This did not resolve the problem.

Can anyone explain why this is happening, and what can be done to prevent
this from happening?