+ Reply to Thread
Results 1 to 4 of 4

Google Sheets, line graph, moving date line

  1. #1
    Registered User
    Join Date
    09-03-2015
    Location
    Yorkshire
    MS-Off Ver
    2016 32bit
    Posts
    43

    Google Sheets, line graph, moving date line

    Hi there,

    I'm trying to add a moving date line across my google sheet chart.
    Simply a vertical line the cuts across the chart showing a clear indication of todays date.

    I've done it in excel, by one of the two following methods:

    -By creating a new series, which is actually a line / scatter plot on the secondary axis with the values of 0,1 only.
    -By creating a new series, which is actually a single data point with a massive vertical error bar to give the appearance of a line.

    But I can't work out how to do it on a Google Sheets chart,
    Can anyone be of assistance?

    Best regards

    TinyDamooge

    Edit: uploaded a .xlsx of how I might achieve this in excel.
    Attached Files Attached Files
    Last edited by TinyDamooge; 04-07-2022 at 09:07 AM.

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,137

    Re: Google Sheets, line graph, moving date line

    Heres how to do it:

    Put the TODAY date at the top of each stack of dates, and leave blanks beside it in the alpha and beta columns.
    (note: i added 2 days to it so that it sits in the middle of the chart so you can see it works properly)

    Drop in a Line Chart or Smooth Line Chart

    Add in your Data ranges including the row that has the TODAY date, so that the first cell of the other columns includes a blank cell
    AND and in a range just for the TODAY date cell

    Your ranges will look something like this:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Combine the ranges Horizontally

    Add the full date range for the X-Axis

    in your examples it would be either:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Add in the Series for the column C and column D ranges, AND add in the single cell for the TODAY date

    Enable Use column B as labels

    Switch to Customize

    Go to the Series section

    Change Apply to all series to Line 3

    Change the line colour to red
    Ensure opacity is 100%
    Ensure point size is None
    Enable Error Bars
    Change percent to 100

    Go to the Vertical Axis section

    Set Max to 600
    Set Min to 0

    Screen Shot 2022-04-07 at 11.18.01 pm.png
    Last edited by janmorris; 04-09-2022 at 05:25 AM.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    09-03-2015
    Location
    Yorkshire
    MS-Off Ver
    2016 32bit
    Posts
    43

    Re: Google Sheets, line graph, moving date line

    Hi janmorris

    It took A LOT of playing around, (The actual data is much more complex that that I gave as an example)

    But I got it to work - thankyou! (I'll mark as solved)


    Note: I only managed to get my own sheet working after I transposed the large amounts of horizontal data into vertical data. Weird!

    Ta

    Luke

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,137

    Re: Google Sheets, line graph, moving date line

    im glad you got it to work out. thanks for the reps, much appreciated.

    from my experience, the trick seems to be to include the value you want (as the error bar) with the other data. it doesnt need the x and y values because as you add the series this single value will be turned into the error bar (vertical line) only.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Moving all data points closer to the trend line on a graph
    By curiousasd in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 09-25-2019, 09:18 AM
  2. Google Sheets - line up orders by turnover or quantity
    By Ymmies in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 01-18-2019, 08:28 AM
  3. google sheets copy and repeat line
    By Ditch1983 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 07-10-2017, 04:33 AM
  4. Updating existing 4 line graph is giving single line graph
    By carrach in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-15-2016, 07:53 AM
  5. Line Graph question - vetical line values at one fixed date
    By bohn in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-12-2015, 02:22 PM
  6. help adding a trend line to a not-so linear graph, and then moving it
    By jmarcian in forum Access Tables & Databases
    Replies: 4
    Last Post: 03-05-2009, 12:16 AM
  7. Adding a moving cursor or line on graph
    By Nalinster in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-10-2007, 02:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1