Published: 2015-09-26 02:31 |
Category: Tips | Tags: google sheets, charts, google workspace
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:
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.
In your spreadsheet, click on Insert > Chart or click on the chart icon.
- In any tab, click on the data select tool to close the chart dialog and choose your data.
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.
If all goes well, your new chart should have data on the correct axis in the graph.
I am having a similar trouble. My data sheet is supposed to be set up for the y axis to reflect the dependant variable. For some reason, google sheets is not recognizing this and is choosing its own data set up from 0-500, which makes no sense because its supposed to go from 0-100%. Also, when I change the min and max values from 0-100, the graph does not reflect the true data set. For example, for the x axis if its 100 people, the corresponding y axis should show 75%, but it has the bar up to 100%. I don’t know how to fix this and am sure it is user error. Please help
Bummer. Without seeing your sheet, it’s hard to say what exactly is happening. I would suggest making sure you’re selecting the top-left corner of the range (as opposed to the top right, bottom, etc). You can also try swapping the rows and columns using the checkbox option in the chart settings sidebar.