41

I'm struggling while 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?

Vomit IT - Chunky Mess Style
  • 42,624

9 Answers9

31

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.

kausik
  • 311
18

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

Gareth
  • 19,080
Rook
  • 24,289
6
  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!

deserthackberry
  • 61
3

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

Abacus
  • 401
2
  1. Select the cells containing the data you want to graph
  2. Select Insert -> Chart
  3. Select graph style you want
  4. For Excel 2010, Click the chart. then go to chart tools, - design
  5. Click "select data". In legend entries column,click "year", then click "remove"
  6. In horizontal axis labels, click "edit". Axis label range appears. Highlight the data cells of year excluding the cell of text "year" to avoid misunderstanding by Excel. Click "ok", than "ok" again.
  7. Go to chart tools, and click layout. Click axis. then primary horizontal axis. then click more primary horizontal axis options. Click on tick marks, then click close. You've gotted it.
user660224
  • 21
  • 1
2

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

Toc
  • 1,821
1

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.

1

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.

1

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.

rassa45
  • 195