Hello, welcome to Chris' Quick Tips for Google Sheets.
In this Sheet I've got a record of my weight since 2015. The numbers are
interesting but it would obviously be much more useful to see the numbers in a chart.
In the Sheet I will show you how to create a normal line chart and then I will also
show you an extremely useful Timeline chart that Google Sheet keeps hidden away
at the bottom of the list of charts. With data like this I would normally
produce a line chart. Google Sheet makes this very easy with data in columns A and B with
headers in row 1 and no other data or text in these columns, it is possible to select
the entire column and then click Insert Chart. The default chart that Google sheet produces
is pretty good but I would change it so that the vertical axis does
not go down to zero. Click customize then Vertical Axis then type 60 in the minimum.
Google Sheet allows you to do a lot more customization. The majority of
the customizations are obvious so I will just run through a few of the less obvious settings.
Under Series it's possible to add dots to each point on the chart or you can then remove them
totally so that there are no dots on the chart. You can also add a Trendline which clearly shows
that since this data started in September 2015 my weight is steadily increasing!
If we now have a look at the Chart Style there are a couple of interesting options.
The Smooth option as its name suggests smooth the transitions between the points in
the chart. This is most noticeable as the line approaches a peak where it rounds off the peak
rather than just goes to a point at the peak. The maximize option is a different layout for
the chart that makes the chart as big as possible within the charts window. I think
this is a personal preference and you may have a different preference depending on
where and how you're going to use the chart. I've just created some null values in the
sheet by deleting the contents of some of the cells in the data table and the chart shows a
break to indicate that there are null values for these days. If you tick Plot Null Values the chart
will show a continuous line between the points. Let's have a quick look at data where there are
two time series to plot on the same time axis. I don't run but let's suppose I always go for a
run on the same day that I weigh myself and I've recorded the distance that I've run.
Create the chart the same way by selecting the three columns and clicking Insert Chart.
The distance run are much lower numbers than my weight so this chart would look
better with a second vertical axis with a different scale. Select the customize
tab and the Series section select the Run series and change the axis to the right axis.
You can change the setup of the right axis by clicking the new section that
has appeared called Right Vertical Axis. You can then change the maximum to 10 to
edit the left axis click on vertical axis. I think that setting the minimum to 60 gives
a nice separation to the two lines so that the chart is easily readable.
When you have got multiple lines on the chart you can enable Compare Mode which is in the
Chart Style section. When you are moving the mouse over the chart the pop-up shows you details about
both the points on both lines for that date even though the mouse is only over one of the lines.
Compare this when the Compare Mode is not enabled and you'll see that when you mouse over
the line you only get details about that line. It's important to note that in order to see this
pop up window the chart editor must be closed. If Chart Edit is open and you moved the mouse
over the chart it simply highlights the different elements of the chart. So if you are not seeing
the pop-up window close the Chart Editor pane. A quick way to customize a chart is to
double-click the element of the chart that you want to customize. You can do
this if the Chart Edit pane is open or not. For example, if you want to customize the
right vertical axis double-click and the Edit pane will open on the Customize tab
with the Right Vertical section expanded. This works for all elements of the chart.
Google chart includes a very useful Timeline chart that is hidden away
at the bottom of the list of chart types. Click on the icon of the timeline chart.
You can customize the chart as shown. The date range selector is at the bottom of the chart.
By simply dragging the start and end markers you can zoom in or out on specific date ranges.
If you zoom in on just a few months you will see that the date range selector also zooms
in to allow you to more precisely position the start and end markers.
To zoom out again drag the left and right date markers to the far left and far right. The chart
automatically abbreviates or expands the year and month names to ensure that the horizontal
axis labels are readable and not squashed. If you do not want the date range selector
in your chart untick the Show Date Range Selector box in the Chart Editor pane.
There are also zoom buttons at the top left of the chart with some standard time periods. The
markers in the date range selector show the start and end of the date period displayed on the graph.
Once the required zoom level has been selected you can then click and hold between the start
and end date markers and then drag the date range left and right on the timeline. The zoom button
can also be disabled in the chart editor. The timeline chart can also be annotated
with up to two columns of comments. The comments are taken from the two additional
columns and are placed to the right of the chart with the date for the comment.
The first columns comment which in this case is the minimum and maximum are in bold text. All
points on the chart with a comment in either column C or D are marked on the chart with
a letter so that you can easily see the relevant comment for the relevant date.
You can also click on the letter on the chart which will shade the
relevant comment or alternatively you can click on the comment. The
letter on the chart is changed to bold font when you click it.
Unfortunately this change is actually too subtle to really highlight the letter on the chart.
This Timeline chart is very useful and very flexible. It's a pity that it's
not a suggested chart and that you have to scroll right down to the bottom of the charts to find it.