Copying a chart and changing the source data

Question

I have a 2003 excel work book with charts and normally I select it and make a copy of it and then change the source data.

I have now up graded to 2010 excel and can't seem to do this.

Have tried to start afresh but just seem to get everything except what I want.

Thanks

ChrisK

Answer 1

OK, I see what you mean now.  I don’t think you can change the chart data range without over-writing the series names in Excel 2007/10 (i.e. unless you have a simple chart data range that includes references to series names (column headings)) .  You have a few options (other than redefinining each series via the bottom half of the Select Data Source window), including:

a)     Move the chart into the same worksheet as the data, click on the chart or individual series and drag the coloured ranges down to the required cells.

or

b)    Make your chart dynamic by pointing it at dynamic named ranges.  To do this, set up the following three named ranges by going Formulas tab > Define Name:

Name:      Scope:           Refers to:

Cats        Workbook       =INDIRECT("Sheet1!A" & Sheet1!$F$1 & ":B" & Sheet1!$G$1)

Series1    Workbook       =INDIRECT("Sheet1!N" & Sheet1!$F$1 & ":N" & Sheet1!$G$1)

Series2    Workbook       =INDIRECT("Sheet1!P" & Sheet1!$F$1 & ":P" & Sheet1!$G$1)

replacing Sheet1!$F$1 and Sheet1!$G$1 with references to any two single cells where you can enter start and end row numbers for your plot - they could be on another sheet (and you can call the named ranges anything you like, as long as they don’t look like a cell reference, e.g. Ser1 could be confused with cell SER1, so is not permitted).

Now change the chart so the Axis label range points to =Sheet1!Cats

and the first series “Series values” points to =Sheet1!Series1

and the second series “Series values” points to =Sheet1!Series2

Now the plot should show data from between the rows specified in Sheet1!$F$1 and Sheet1!$G$1 (or whatever you changed them to in the named ranges).

You said your categories (x) axis contained dates.  If you were to put a date in cell E1 and put the following formula in F1, you could just enter a date in E1 to indicate where the plotted range should begin:

=MATCH(E1,A:A,0)

If the number of rows to be plotted is constant, you could put   =F1+48   in G1.

All of the above assumes your data is on a sheet called “Sheet1”.  Adjust the above accordingly if it isn’t.

Hope that helps.

Cheers

Rich

Answer 2

I think that I have tried both to edit the formula by right clicking and changing the cell numbers but nothing else. And I've tried to start from scratch but don't seem able to have two things on the axis like I did before.

The chart is in a sheet of its own. And only one is selected at a time :).

"Disable automatic update of Workbook Links" was NOT selected.

And yes I always acknowledge the auto updates/enable content prompts.

Sorry still none the wiser.

I used to do right click on the chart tab > Move or copy > select the chart and tick the copy box.

Go into the new chart right click > Select data. and edit it and it all worked fine in Excel 2003.

Now I've got Excel 2010 do the same as above

I do right click on the chart tab > Move or copy > select the chart and tick the copy box.

Go into the new chart right click > Select data.

This gives me a pop-up box and I edit the >Chart Data Range at the top.

but this always seems to be where it dies. So I tried the following.

I edited the legend entry & axis label that seems to work, but then the top >Chart Data Range goes blank and states

'The Data range is too complex to bee displayed. If a new range is selected,it will replace all of the series in the Series panel.'

And that's where it all goes wrong.

Thanks for replys

CK

Answer 3

normally I select it and make a copy of it and then change the source data.

I have now up graded to 2010 excel and can't seem to do this.

How are you trying to change the source data?  By editing a series' formula, or by right-clicking on the chart > Select Data...?

Is the chart embedded in a worksheet or on its own chart sheet?  Are you sure only one chart / chartsheet is selected?

Two other things to check, if you have links to data in external files:

Excel Options > Trust Center > Trust Center Settings (button) > External Content

Ensure that "Disable automatic update of Workbook Links" is NOT selected.

Ensure that you acknowledge any prompts in the message bar when you open the file:

"Security warning  Automatic update of links has been disabled [Options] > Enable this content [OK].

Hope that helps.

Cheers

Rich

Answer 4

Both of them are already ticked, so no luck there...

Answer 5

Try the below steps:

Open Excel->click on file->Excel options->advanced tab->on the right hand site go to the section ‘When calculating this workbook’->ensure you check the boxes for ‘Update links to other documents and Save external link value->Click ok.

Close Excel and now test.