Getting the Axes Right in Google Sheets

I think I finally figured it out.

Getting charts and graphs created in Google Sheets (or Excel for that matter) has always been somewhat of a wrestling match between what I want the software to do and what the software thinks I want it to do. Predictive and suggestion-based user interface is nice, but not when I want to plot some data. Let me dictate what happens.

Excel isn't as bad - you can create a chart and then manually set the Y and X axis series. No such workflow in Google Sheets.

The Task

This week, my students were collecting data for position and speed of an object as it accelerated down an incline. We put the data on the board and then I went to make a chart of it the following day. As usual, I selected the two columns to plot and inserted the scatter plot.

Here's a sample table: 2015-09-26_14-18-59

No matter how I selected the data - left to right, right to left - Google always put the velocity data on the X axis and time on the Y (in Excel, it remembers which direction you select data, so it's an easy fix).

The Fix

I realized, after several minutes of trial and error, that Google defaults the X axis to data in the leftmost column. Seriously. It was that small of a distinction. So, you have two options: 1) Swap the columns, or 2) Select data manually. Because this sheet was dependent on some formulas I'd already written, I chose to do the latter.

  1. In your spreadsheet, click on Insert > Chart or click on the chart icon.
  2. In any tab, click on the data select tool to close the chart dialog and choose your data.
  3. 2015-09-26_14-26-10
  4. Select the range you would like to be on the X axis. Then, click on Add another range and select the Y axis range of data.
  5. 2015-09-26_14-29-13

If all goes well, your new chart should have data on the correct axis in the graph.

Leave a Reply

Your email address will not be published. Required fields are marked *