Switch x-axis and series entries

[bran]

I have Excel produce a chart off data consisting of weeks and of sequences of a process. I need the x-axis to be weeks, the y-axis to be the costs, and the legend (series) will consist of the different sequences, so that there will be a couple lines on the graph. The week numbers are contained in one columns and the sequence data with corresponding costs is in multiple columns next to the week column.

Week ...Seq1 ...Seq 2 ...Seq 3 ...Seq 4 ...Seq 5 ...Seq 6
W1 .......324 ......33 ........32 .......34 .......43 ........2
W2 .........34 ......44 ........55 .......23 .......43 .......53
W3 .........44 ......22 ........55 .......45 ........65 ......25
W5..........32 ......44 ........43 ........2 ........12 .......99

The problem is that when I have more sequences than weeks (such as above), Excel formats the x-axis to be the sequences and the legend will contain the weeks. Otherwise, when there are more weeks than sequences, the graph comes out fine.

How can I tell excel to always have weeks (which are contained in a column) be the x-axis values? I know how to do it for one series but for multiple dynamic series, I am unsure. I'd rather not do a loop either but I will if that is all that works.

 

[Jon Peltier]

In 2003 when creating the chart, on the Data Range tab of Step 2 of the Chart Wizard (Chart Source Data), choose the Series in Columns option.

In 2007 you have to make the chart incorrectly, then fix it. Select the chart, go to the Chart Tools > Design tab, and click on Switch Row/Column.

 

[bran]

Thanks for pointing out where I can switch them. I am using 2010 but having to save as 2003. The code I was looking for was this:

ActiveChart.PlotBy = xlColumns

I ended up writing an extensive loop for checking the amount of x-axis entries and series entries and going from there. Now I can simplify it.