Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm getting my ass kicked trying to plot simple data into a simple chart in excel (2010 beta).

My data is a very simple one:

Value    Year  
-----    ----
1        1900
5        1902
3        1912

etc. In the chart, I want year to be the X axis and the value to be the Y axis, and have a single line mapping the change in value over years.

When I select my data, Excel wants to map both at the same time, rather than plotting each pair as a point on the graph.

How the heck do you do this?

share|improve this question
up vote 5 down vote accepted

I don't understand quite. What kind of graph do you want ? This ?

To get this, choose your chart as a linear type (xy scatter group). After that go to select data, and select x and y values by hand from series 1. After that, fix up a little your x axis properties, so the year shows every year, and not every two or so ... Might want to fix up the default look of the graph too.

alt text

share|improve this answer
    
That looks like an inverted hockey stick graph ;) Hooray for the end of global warming! – menns Dec 10 '09 at 17:55
    
i could do with some ... it's -5 at my place ... <freezing> :( – Rook Dec 10 '09 at 18:04
    
Actually, I was plotting some long term temperature data.... – Will Dec 21 '09 at 17:02

For MS Excel 2010, I struggled with same issue i.e. instead of X-Y chart it was considering two columns as two data series. The catch to resolve it is after you select cells including all data points and column headers, you should insert -> chart -> Scatter chart.

Once the chart is created (it will be in X-Y format), you may choose change chart type option to change scatter chart to column chart/histogram etc.

If you select all data points and try to create column chart at first time, excel 2010 always consider two columns as two data series rather than x-y axes.

share|improve this answer

In Excel 2003 you can change the source data series in step 2 of the chart wizard on the "Series" tab. Just change all the references from column A to B, and B to A.

You can also modify source data on an existing chart in the same way. Right-click a blank area on the chart and look for "Source Data..." in the context menu.

share|improve this answer

Plot only values in the first column. Then add the values in the second column as labels for X axis.

share|improve this answer
  1. Select the cells containing the data you want to graph
  2. Select Insert -> Chart
  3. Select graph style you want
  4. Next
  5. In the "Chart Source Data" dialog, select the "Series" tab
  6. Remove the X axis data column. Series 1 is the leftmost column, so in your example, you'd remove Series 2
  7. Enter Names(legends) for the other Series(lines) -- in your example, you'd enter "Value"
  8. Select the spreadsheet icon at the right side of the "Category (X) axis labels" text box -- this will minimize the wizard and bring up the spreadsheet
  9. Select the X axis data from spreadsheet -- in your example, the data in the "Year" column
  10. Click the X at the upper right of the minimized wizard -- this will maximize the wizard, not close it
  11. Next
  12. Change/add chart, X, and Y titles
  13. Make any other format changes
  14. Select Finish

Is this intuitive? Hell, no! It's Microsoft!

share|improve this answer

A little preparation saves a lot of hassle.

If you put the X values in the first column and the Y values in the second column, and create an XY chart, Excel will get it right.

share|improve this answer
    
if you prepare your graph, then put it as a default, then select xy data, then press F11 you can save a lot of time in the long run ... – Rook Dec 24 '09 at 1:18

Just remove the header from the first column, and it will auto-format as the axis.

share|improve this answer

Make a graph with all columns

Go to Select Data for your Excel graph. Then, in the second column are the current x axis points. Click Edit and select the x Axis values.

On the left, there will be the different columns of y values. (For each line on a graph). Delete the data that belongs to the column with the x axis values.

You are all set.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .