Bug in Excel 2007. Impossible to add new data to exsiting chart ??

[dokuplan]

Gentlemen,

I have an existing Excel file (Office 2003) that we opened in Excel 2007. No
compatibility problem were found.
The date page (Salesdata) consist of two columns
A4 to A186 (Date format from April1993 until July2008 ) and B4 to B186
(Sales/normal value data April1993 until July 2008)
Next there are 12 charts (January until December)
Each chart contains the month value of each year.
Example, The March chart contains the data from March 1994 until April 2007.
Problem/Bug: When we try to add the data of March 2008 (A183:B183) to the
chart March it doesn't work ????
We made a copy of (A183:B183), then open the March chart then we did a Paste
Special (new point to the chart) and the data is not added the chart.
If you look then to the formule string you will see that only the A183 is
added and the B value not ???
If we use a normal Paste the data is added right from column March 93 and is
created as a new series.

Via Microsoft support Netherland we got the work around ??? to create a new
chart and add all date by using Select data then Add new data (A value B
value) The result is that you get the 12 month values in the chart but then
as 12 individual series , the big issue is now that you can't create any
trend line on the chart because the trendline works only on one serie.
This problem is a major drawback comparing with to way it pas possible in
Excel 2003

In short we cann't add more date (in the same serie) to and exiting chart.
Microsoft say's that it has to do with the internal data base, the max new
date entries is limitted to 15. In my case 15 months. Why ???

Can somebody help us with this problem otherwise we have to go back to Excel
2003 !!!!!!!!!!!!!!!!!!!!!

Lets pray to have a answer and solution to this bug.

 

[Jon Peltier]

Upgrading to Excel 2003 doesn't look like a bad alternative, other than the
disruption of another change. But I digress.

However, if you are plotting discontiguous data like this you will run into
issues, even in 2003. (I did also have problems adding data in the manner
you describe in 2007, but it's an approach I never ever use).

If you have monthly data by date, and the data are truly dates (1-Jan-2008,
not text for "Jan 2008"), then you can make a pivot table, with date in the
row area and sum of value in the data area. This looks like your original
table. Group the date field by month and year, which now gives you a column
of years and one of months. Move the Months field to the columns area. Now
make regular charts (not pivot charts) using the years as X values and data
under each month column for Y.

To make a regular chart from pivot data, select a blank cell away from the
pivot table and insert a (blank) chart. Click Select Data from the ribbon
(Chart Tools > Design tab). Add a series, click on the Jan month heading for
Series Name and select the January values for Values. Then click on the
Categories Edit button, and select the list of years. Copy the chart as many
times as you need and adjust the series data.

- Jon

 

[dokuplan]

Dear Jon,

Firths of all many thanks for your fast reply on my issue with the chart
problem.
I belive that your solution is valid an will certainly help me to resolve
the problem.
The thing is that I gave no experience wit pivot tables I've never made one.
Would it be possible to create for me an small example so I can follow then
step by step how you made the pivot table and than make a regular chart from
it.
I hope that the answer on my question makes part of your support mission
otherwise please advice me where to find the necessary help.

Thanks in advance.

Dokuplan.

PS : If you want I can always send you file in question.

 

[Jon Peltier]

There's really not any support mission. I answer questions that I am
interested in and which I think I know something about. We're all
volunteers.

I have posted a workbook with a couple sample pivot tables:

http://peltiertech.com/WordPress/wp-content/img200804/SamplePivotByMonth.xls

Sheet1 has some simple monthly data and a simple pivot table of date and
value. Sheet2 shows the same data and the pivot table after the date field
has been grouped by month and year. A second pivot table shows the effect of
moving the months to the column area from the row area. There are two charts
based on this data, January and February.

I made this workbook in 2003, but it is essentially the same in 2007. I
detailed the steps in more detail in my first post.

- Jon

 

[Jon Peltier]

I should also point you to some pivot table resources, from my site and from
around the web:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://peltiertech.com/Excel/Pivots/pivotlinks.htm
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

- Jon