I just learned something new about Excel this morning via trial and error. Apparently the chart functions aren’t designed to naturally take two columns and plot one against the other on an X-Y axis. The default charting function for two columns is to display the data from each row side by side.

I had a student in our library computer lab this morning who needed to plot the points on the chart with the first column of data going across the bottom and the second column of data going up the Y-axis. As I think about it, this is more of a GRAPH function as opposed to a CHART function, which is probably why it’s not natural for Excel’s “Chart Wizard.”

Anyway, I did manage to get this to work, but it wasn’t intuitive. I’m writing about it here in case anyone is wanting to learn or is searching the web as I was, trying to find out how to do this.

Step 1: Use the Chart Wizard to create the chart with the chart type being an XY (Scatter).
Step 2: When the chart is completed, right-click on the chart and select Chart Type, changing it to Column. For some reason, this will save the XY treatment of the data while converting the points that were plotted into column bars.

That’s it! It’s not particularly complicated, but it’s not particularly intuitive either. You just have to create the chart as XY chart or “scatter graph,” and then convert it to a bar graph or column graph. That avoids the clustering that Excel wants to do by default.

Voilà!