# Charts - Copy chart worksheet problems

#### sparkwoodand21

##### New Member
Hi,

I'm new to charting and having scanned the old posts I can't find any reference to the problem I'm experiencing.

I have set up a simple graph data sheet as follows:

There are 6 rows per data set (one header and five data series).

Each data series contains 14 columns (one series name and then one for each financial period).

I have 170 data sets to graph.

So I thought I'd create the first graph on a separate sheet and then copy this sheet 169 further times and then redirect the source data on each to the required data set on the graph data sheet. As i had carefully laid out the data on the graph data sheet to be equi-distant it is more straightforward than you might think)

Problem 1 - when I redirect the source data, Excel decides that the first 9 columns are in fact part of my data series name and therefore charts only Period 9 to 13 and gives me ridiculous series names!

This I have begun to fix by resetting each series (name and values) in the source data dialog box. This is getting painful particularly if I have to do it 152 more times!

What am i doing wrong or is this a bug in the programme?

Problem 2 - Whilst the first chart initially remains as I had set it with one of the data series starting at a value in period 8 and increasing to period 13 (i.e. without touching the X axis and starting at zero). The duplicated sheets (once corrected) start in period 7 at zero and then rise to the period 8 value. Whilst not being a huge problem it does detract from the point the graph is trying to make. I have set [tools - options - chart] to not show zero values. Interestingly, after I had amended about 10 charts the first one miraculously linked itself to the X axis at zero!

Again - what am i doing wrong?

If anyone can help out I would be grateful, else this weekend will be taken up creating 169 charts individually.

Thanks for reading,

S

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hmm... Sounds as if a VBA guru could give you the answer!

1. Excel finds 9 blanks in the top of the first column of the data range (assuming you're plotting by column), so it decides they are not part of the X data, and treats the top 9 cells of each other column as the series names.

2. I think there's still some confusion between you and Excel about the data range. Make the data range like this:

Code:
``````   A  B  C  D  E
a  1  2  3  4  5
b  2  3  4  5  6
c  3  4  5  6  7
d  4  5  6  7  8
e  5  6  7  8  9``````

The top left cell being blank tells Excel that the top row is series names and the left column is X values (they can be numeric, I used letters in the illustration). If you have a label in the top left cell, Excel may not correctly guess what you were intending.

Replies
2
Views
790
Replies
0
Views
467
Replies
0
Views
529
Replies
7
Views
373
Replies
3
Views
382

Threads
1,203,094
Messages
6,053,507
Members
444,667
Latest member
KWR21

### 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

### 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