Charting non-contiguous data

G

Guest

Hi,

Can someone please tell me how to create a dynamic line chart using non-contiguous data points.

Your assistance is appreciated.


HAPPY NEW YEAR
 
J

Jon Peltier

Neal -

Select the points, holding Ctrl while selecting points after the first
one is selected. Now run the chart wizard and make your chart.

If you have too many points, the chart series formula gets too long,
however, and Excel chokes on it. You're only allowed about 255
characters each for the X and Y part of the formula. Here's what I
mean. A series formula for a chart with a contiguous set of cells looks
like this:

=SERIES(,,Sheet1!$B$2:$B$4,1)

A chart of every other cell has a formula that looks like this:

=SERIES(,,(Sheet1!$B$2,Sheet1!$B$4,Sheet1!$B$6,Sheet1!$B$8,Sheet1!$B$10,Sheet1!$B$12,Sheet1!$B$14,Sheet1!$B$16,Sheet1!$B$18,Sheet1!$B$20,Sheet1!$B$22,Sheet1!$B$24,Sheet1!$B$26,Sheet1!$B$28,Sheet1!$B$30,Sheet1!$B$32,Sheet1!$B$34),1)

If the sheet name is longer, or if you add more points, the chart will
not be able to show all the points. In this case, you will have to
copy-paste the data, or use formulas, to get the data into a contiguous
range.

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top