23

I know it is possible to plot graph or chart in Google Sheets but the problem with current plotting way is that you can only specify the Y, but not the X, and as such there is no way to plot a time series graph (time vs displacement for example).

Any idea on how to do this?

8

Google should now respect X-axis date values for Line, Area, Scatter, and Time Line charts.

For example, if you have the following data*:

|    Date    | Value |
|------------|-------|
| 01/01/2016 |   3   |
| 02/01/2016 |   4   |
| 03/01/2016 |   5   |
| 03/15/2016 |   6   |
| 09/01/2016 |   3   |
| 12/01/2016 |   3   |

*where the date values are stored as dates. If not, you can use =DateValue() to convert a date string to a date value.

You'd should see a lot of data in the beginning followed by a lull of activity in the middle of the year.

Area, Line, Scatter, and Time Line (located in Other) should all do so:

Time Series Charts

By contrast, bar charts continue to treat date values as a simple string and return each of the values sequentially:

Bar Chart

And here's a public spreadsheet with all of the included examples

  • 2
    I am not sure why this is a selected answer. I see your spreadsheet and congratulations... I've been trying for hours and I cannot get the thing to define two different axis from columns of data.. – Aditya Nagrath Apr 27 '17 at 22:45
  • @AdityaNagrath, it seems like you might have a different problem than OP's of trying to treat date values in a time series. In either case, your description isn't much to go off of and debug or help. Can you fork my public sheet and highlight what you're trying to do and why it's not working? – KyleMit Aug 29 '17 at 13:55
  • @KyleMit what if the data are stored in different sheets? can I have one chart with the cumulative results? I have 12 sheets (one each month) with the value, I'd like to show them all in one chart, but I can't figure out how to do. – overkill22 May 25 '18 at 5:58
  • @KyleMit if the data is aggregated, google sheets treats the dates as discrete categories. Is it possible to override this? – sab hoque Aug 3 at 14:52
13

There is now a "Time Line" Chart option in the Trend category that makes a nice chart like Google Finance. Customization is limited, but the Google Finance style charts are pretty cool.

6

This answer is now out of date; see accepted answer and others with more recent details.

Old Answer

This link: http://www.google.com/support/forum/p/Google+Docs/thread?tid=5cea8659f9b3bd29&hl=en

Indicates that there are work arounds allowing you to plot the X and the Y, using a 'hack' to plot dates for example.

0

go to Insert -> Chart -> Select Data Range (grid icon) -> Add Another Range

This allows you to enter 2 ranges - X and Y

-3

It is important to put your "Date" column as the FIRST range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.