Create simple x-y chart based on 2 columns of data

This is so simple, I'm obviously missing something. I have a total of 2 columns of data. I want a column chart with one column of the data as the x-axis and the other column as the y-axis. Whenever I select my 2 columns of data, click on Insert->Column Chart->2-D, I end up with some sort of chart with both my columns of data compared on the x-axis and some arbitrary y-axis. How do I get one of the columns of data onto the other axis?

Thanks for your help.

Cheers,

copperberry

Thank you. I tried that, and it's not quite right. For some reason, the Chart is seeing one column of my data as a series, but it's not. It's just one axis of the chart. Y-axis = $, x-axis = year. I want each year's column to be the same color (except a few exceptions that I will call out. The different heights of the columns will demonstrate the trend. (I don't even need a trend line.)

I almost got it by creating a pivot table and converting it to a pivot chart, but I still have this series issue. Am I missing something? Is the data for one axis now called a series?

Thanks for your help!

Cheers,

copperberry

Marvin, I appreciate your difficulty! As it turns out, I did figure it out. Here's what I did:

- highlighted the two columns of data
- inserted simple pivot table with the year as the row labels and the $ as the amount (no column)
- converted it to a pivot chart
- clicked on "switch row/column", as you suggested, and this time it made a bit more sense (even though one side is called "series" and the other is called "row") and got my bars lined up the way I wanted.
- finally, I spent a long time with the layout and format buttons to get my labels to turn out right, and my columns to be the right color.

I've attached the end result. I am surprised at how complicated this ended up being. I would have thought with two simple columns of data, I could click two or three times and the basic chart would have been there, complete with date labels along the x-axis and $ labels along the y-axis. Any steps you can suggest that make this simpler in the future would be most appreciated.

Many thanks,

copperberry

It is real simple if you choose scatter as your chart type. If you choose a line graph, Excel thinks the data chosen are your series data and tries to plot both columns as lines on your graph. A scatter chart plots the two against each other (one as X and the other as Y), which enables you to determine if the two data series are correlated.

Bizmanualz, I tried your suggestion, and the scatter chart does not produce the results I am looking for. See my sample chart attached above. I want a chart that shows a collection of columns moving from left to right along the x-axis. Each column represents a year. The height of each column represents the amount of money for that year, which is scaled along the y-axis. There must be a very simple way to produce this chart, but I could only do it through a pivot table, which seems a cumbersome method.

Let me preface by saying I'm not familiar with the interfaces in the newer version of Excel. In my older version of Excel, one of the steps of the chart wizard (same dialog can also be accessed in the chart menu) is the data range/source data dialog. In this dialog, you can tell Excel that you want to use a specific column for the category/x axis labels rather than the default x-axis labels. It seems to me that the answer to your question should be as simple as finding that dialog and figuring out how to use it.