asked on

Excel 2010: Assigning X/Y Values to Chart Series

I need to plot two sets of values against one another. The values are located in various uncontiguous cells. There are a very large number of cells.

I tried using the Union() function to create the two ranges of cells that I could then assign to .Xvalues and .Values. This method supports creating very large combinations of cells, however, after unioning the cells, Excel no longer remembers the order in which I added cells to the range so when I run the plot, the lines do not connect in the correct order from point to point. Most of my data is organized in rows, but Excel seems to pick data in order by column first.

I also tried creating strings like this: "Sheet1!$A1:$A1,Sheet1$B5:$B5, ..." and assigning those to .Xvalues and .Values. This method respects the order of the cells so the lines plot as expected, but it generates an error when the string gets around 8000 characters so it won't work for large data sets.

Is there some other way to assign a large number of uncontiguous cells to .Xvalues and .Values for plotting?

I am running Excel 2010 on Windows 7.

Professor J

have you considered trying X Y chart labeler http://www.appspro.com/Utilities/ChartLabeler.htm it is free

Saqib Husain

Why don't you try building a new range with the data organized as required?
You might create a new (Hidden) sheet.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

@professorjimjam, that add-in adds labels to already existing charts. The question is about a very different issue.

Professor J

thanks teylyn.

ASKER CERTIFIED SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

koughdur

ASKER

regmigrant,

I did some simple experiments and it appears your method will probably work. I shouldn't take me that long to integrate your method into my code and see if it works.

Thanks.

koughdur

ASKER

This worked! Most of my data goes across in rows so I created a named range for each row of data. Then I created a master named range combining all of those named ranges for each of my X and Y data sets. Then I set .XValues and .Values to the master ranges and VOILA it worked!

All my plot lines are being connected in the correct order.

Thanks a million.