Confused with a simple excel/graph issue 3

Without seeing your actual spreadsheet(!) it's a bit of a guess, but the coloured highlighting on your table containing your source data seems to suggest something is awry in the data ranges you have selected.[ ] Your two extraneous zeroes are coming from the fact that you have your column HEADINGS as one of your DATA ranges.[ ] (And the fact that the other highlighted range is 2x2 rather than 1x2 is also probably not helping much.)

That's often common; Excel split your data into two series, with each column in its own series. One of the two series will have its cell references in the correct place; you need to copy the cell references from the other series and add it to the one with the correct references. Then delete the extraneous series

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list

Right-click the chart, select Select Data, Delete series 2, Edit series 1.

Change the references to the X & Y values accordingly (easiest way is to DELETE the contents and click on the cells to create the series cell reference)

OK and vola!


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

This is an example of an Excel bug that has been there for ever, and for some reason many people don't even see it as a bug. You want to plot an XY graph (known to Excel as a scatter graph, even when displaying the data as lines), but if Excel sees any hint that the data is actually a "line graph" (i.e. the X values are not plotted to scale, but with equal spacing for each point), it will convert it to a line graph for you without asking. Also if the number of columns is equal to or greater than the number of rows it will treat the data series as being in rows rather than columns. The screenshot below illustrates these problems:


The top left graph was generated by selecting a "Line Chart" as the graph type, with range C46 selected.

Top right had the same selected range, but I selected "Scatter (XY)" as the graph type. It has converted it into a Line Chart, but with the series names converted into sequence numbers (but with values displaying as decimals rather than integers!).

For lower middle I selected range C56 and chose Scatter (XY). This has now plotted as an XY graph, but with the data in rows rather than columns.

For lower right I added an extra row of data and selected C57 and Scatter. The data is now plotted correctly, but it has decided that the text in cell D4 is the graph title. There is actually no need to copy data into the third row. It works the same if the third selected row is blank.

For the lower left chart I selected C56 and chose Scatter (XY), then reselected the data. If you don't select the header rows when you generate the chart it leaves it as an XY graph with one data series, and you can re-select by drag and drop, or through the select data menu (if you prefer).

Doug Jenkins
Interactive Design Services

Thanks, Doug.[ ] I have frequently observed the occasional weird behaviour when creating XY Plots, but had never noted any "rules" determining when it might or might not rear its ugly head.[ ] It all makes (a bit) more sense now.[ ] I shall upgrade my mental view of this behaviour from "occasional quirk" to "definite bug".

that is good info, Doug. I'll add one more thing I have noticed

> if Excel sees any hint that the data is actually a "line graph" (i.e. the X values are not plotted to scale, but with equal spacing for each point), it will convert it to a line graph for you without asking

another thing that can cause this is when the values in the first column (x axis values) are formatted in such a way that excel is not sure whether they are numbers or text. sometimes this happens for imported data.

you can correct it if you know the intricate rules of number formatting, but sometimes I give up and use a workaround to make excel reformat it for me. I do this by building a copy of the data from the original using +0 on each cell. that forces excel to format it as a number without changing the value. the new block of data's will plot x-y fine. if you are have only values (no formulas), then you can copy/paste-special-values from that new block of data to overwrite your original data to reduce the clutter.

=====================================
(2B)+(2B)' ?

"i.e. the X values are not plotted to scale, but with equal spacing for each point"

That's called a Category Axis.

Right click that axis and you can usually select a numeric option rather than text.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

Excel also cannot deal with more than one row of header.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list

Skip Vought said:

"i.e. the X values are not plotted to scale, but with equal spacing for each point"

That's called a Category Axis.

Right click that axis and you can usually select a numeric option rather than text.

Click to expand...

The point is that when you include the headers in the selection the x-axis is automatically switched to a "category axis", even though that is not appropriate for an XY graph, and the numbers are already in numeric format, not text.

Doug Jenkins
Interactive Design Services

Sometimes, aren't the old hand-drawn graphs the best? And with soul.