Two sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to chart similar data on two worksheets. The data structure is
Dates in Column "A" and percent change in column "H", on both sheets. I am
having a hard time doing this, is it possible?
 
David -

The trick is in selecting the data, right?

One way: Select the dates in column A (including a header if any), then hold the
CTRL key and select the percent change values in column H (again including the
header, but only if it was included for the dates). You should have two shaded areas
in the sheet, and both ought to be the same length. Run the chart wizard, select the
chart type you want. This is for one sheet's data. Switch the the other sheet,
select the two-area range as above and copy it, select the chart, choose Paste
Special from the Edit menu, and paste the data as a new series, categories in first row.

Other way: Start the chart wizard. On step two, Source Data, click on Series tab,
and for series 1, select the categories (dates) and values (percentages) from sheet
one. Add a series, and select the categories and values from sheet two. Continue to
the end of the Wizard.

In both cases, you're using dates for the categories. If the dates are not the same
on the two sheets, only the dates for the first series will be used to chart both
series, if you have selected a LINE chart. Instead, select an XY Scatter chart, and
the X and Y values of each series are independent of the other.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thank you very much John. I like the first meht od best. Now I need to see if
I can program those steps.
 
Hi Jon,
The graph works, but the problem I am having now is the labeling. Dates (Col
A), both sheets, Data(Col H), which is a percent. This works, but I do not
see a way to label the Percentages. The percentages are associated with week
to week change in a stock prices. The columns currently says "%Change", but
both labels say that and it is impossible to to know what you are looking at.
Which series represent what data ie. which stock? Given that I am going to
try and program this process and the label problem, does it make sense to
simply copy the data out of the sheets and make the %Change label a more
descriptive label?

On the first graph I was able to change the data label, prior to adding the
second series, but I was unable to do that with the copied/pasted series. Any
ideas? The process was pretty complicated and looks like it might be pretty
hard to program in VBA.
 
You have dates in column A and your % change in Column H. I'm guessing you
have the stock name in one of the columns as well. Can you confirm and we
can go from there.
 
Hi Barb,
Thanks for your help. Source Data/Data Series/Name, think I found it for
the second series. The process became complicated enough that I copied the
data out to a separate worksheet and graphed it from there.
What I am trying to do next is preserve the the graph and lose the data now.
Is it possble to do some thing similar to Copy/Paste Special/Values for a
graph? I have not yet been able to find that as an option. I am putting the
graph back on one of the original worksheets, but would like to delete the
newly created worksheet, which is the source.
Thanks again.
 
In Excel 2007 you can do a paste special with a chart and choose to paste it
as a picture, it doesn't maintain its connectivity to the orginial data that
way.

Tami N.
 
Also in Excel 2003 and on back at least to Excel 97. You have to hold Shift
while selecting the Edit menu, then choose Paste Picture.

- Jon
 
Jon

I wondered if that was available any earlier than 2007...noticed that paste
special was not available when I tried it in 2003, but didn't know about
holding down the shift key. Thanks!
 
Back
Top