Create line chart with first column as X axis

[Rastaman]

This would seem like a simple thing, but I haven't found a solution. I am creating a line chart based on two columns of data. First column is the year / work week in the format of 201001, 201002, 201003 ... 201052. Second column is the values I wish to plot. When I create a line chart, Excel makes a chart series out of both column A and column B instead of using column A as the x-axis values. It seems that as long as the values in column A look like numbers to Excel, Excel puts them into a series. I could force all col A to be text ('201001, '201002,...) but this is cumbersome and more work than just fixing the chart. I tried to just format the cells as text and then create the chart, that didn't work either. I also tried to create a template after fixing the chart to use column A as the x-axis values and applying it when creating a new chart, this didn't work either. Is there no way to get Excel to recognize that I want column A as my axis instead of a series?

Rick

 

[Rastaman]

Any ideas out there on this one? With Excel 2007 and no chart Wizard, seems like Excel now gives you a way to very quickly create a chart that you don't want, and you end up spending the same amount of time as before fixing it.

 

[Blade Hunter]

After the chart is created, click on the line and check out the formula.

Notice the commas at the start.

I added the reference as the second series (with the data being third) and it did as you wanted.

I used a simple set of data, A1 to B12

The chart generated this:

=SERIES(,,Sheet1!$A$1:$A$12,1)

I changed it to this

=SERIES(,Sheet1!$A$1:$A$12,Sheet1!$B$1:$B$12,1)

So the range it selected needs to be after the FIRST comma and the second range needs to be what you want to plot.

Hope that helps.

Cheers

Dan

 

[Rastaman]

Thanks Dan, that's a good trick. I can also fix the chart by opening the Select Data window, clearing the series associated with column A and changing the axis range reference for column B. It's not a particularly difficult task, but my other motivation for this is that I'm putting together an informal class on chart basics to some relatively inexperienced Excel folks. This is one of those annoyances that I fear a newbee creating a chart will get hung up on. I've browsed through the online Microsoft training on charts as well as Peltier's site but didn't find anything related to this 'feature'.

Rick

 

[pgc01]

Hi Rick

You have to convert the values in the first column to text, but that's just a few clicks away

- Select the 52 values in the first column and press Text to Columns
- in the first panel choose Delimited and press Next
- in the second panel clear the delimiters boxes and press Next
- in the third panel choose in the Column data format: Text, and press Finish

All the values are converted to text.

I tried to just format the cells as text and then create the chart, that didn't work either.

Notice that that's the same as doing nothing because excel doesn't care about the format, it cares about the type, which is still number.

 

[Rastaman]

That's another good trick, thanks. I guess each solution has it's own pro's and con's. Converting to text works well for creating the chart. On the other hand, if I were to apply other functions to this data like say, VLOOKUP, I'd need to structure the VLOOKUP to look for text instead of number. The work week in this YYYYWW format is very commonly used at my job. It's a common issue of having them in either number or text type depending on the source of the data.

I seem to recall that Excel 2003 chart wizard had a check box to indicate the data in the first column was a series or not. Maybe I'm mis-remembering...

Thanks all for the helpful tips.

Rick

 

[pgc01]

I see. You really prefer not to change the 1st column to text (although you could change it back no numbers immediately after you create the chart).

Another way, that allows you to keep the first column as numbers, is to create the chart in 2 steps.

- select the second column and create the chart. The values will be ok, just the X-Axis will have the default values

- select the first column. Copy. Select the chart. Paste Special.
In the form check Add cells as new series, Values(Y) in columns,
"Categories (X Values) in the first column" and "Replace existing categories".

Press ok and you should have your chart.

 

[Rastaman]

Nice! I've learned another new trick. I've use the copy/paste to add a new series to a chart, but never looked at the options for paste special.

Thanks,
Rick