This is the second part of looking at creating charts in Google Sheets. See part 1 here. Here, we’ll look at some of the areas in more detail. We’ll cover:
- Adding a new series to a chart
- Creating charts with two different scales
- Editing the title, subtitle, and axis titles
Adding a new series to a chart
In part 1 we only looked at 1 series of data, so let’s look at data sets with multiple series. Here, I’ve got some fictitious data on my book sales (blatant plug!). As is often the case, it’s difficult to see what’s happening with the data from just looking at a table, so let’s chart it.
I selected all the data and clicked the chart icon (as described in part 1). It’s automatically produced a meaningful chart showing the sales of both books. I can see that the sales of both books have been improving in the last few months, which is great news!
Now, I want to see how a third book I wrote is doing. I could just create a new chart including the new data, as before, but let’s add the third book’s data to the existing chart.
Click the 3-dot menu and click “Edit chart”.
This will open the chart editor. We can see the two existing series, Book1 and Book 2. Underneath we can add a new series by clicking on the grid next to “Add series”.
This will open a dialogue box prompting you for the data range.
You can either type it in (remembering to include the sheet name as well as the cell range) or go back to the sheet and select the data you want. Then click ok in the dialogue box.
As we can see it’s added the sales of the third book. Oh dear, things are not looking so rosy for that one!
Creating charts with two different scales
Here, I have the sales of one of the books above along with the revenue received (all wishful thinking on my part!). What happens if we chart this data?
Sheets has interpreted the data and decided that I want to correlate the sales and the revenue and shows the data in a correlation chart.
However, I want to see the two sets of data in a line graph and see their performance over time. So, I’ve changed the chart to a line graph, using the chart type option.
The problem with this is that both sets of data are using the same scale, and as the revenue one includes much higher values I can’t really see what’s happening with the sales. The answer is to create separate scales for the sales and revenue. Click on the 3-dot menu and click “Edit chart”, then click “Customise”.
Then open the Series menu. At the moment it will apply any customisation to all the series, but what we need to do is apply it just to one series.
Click “All series” and the individual series will appear. Then click “Revenue”.
Now, click on “Left axis”.
Let’s change it to “Right axis”.
Back on the chart, we can see that the left axis is for the number of sales and the right the revenue. This produces to line graphs which are much easier to read. As you can see in the chart, you may also need to edit the vertical axis title, so it matches with the data series.
If you create a second axis, you will that in the chart editor, you have the option to edit the vertical axis (left) and the right vertical axis.
Changing the line format
You can also edit look of the series in your chart. Under Series, you can change the colour, the line thickness, change the size and shape of points added to the line. The options available will depend on the chart type chosen.
As an example, let’s add some points to one of our lines. Click on “Point size”, then select the size you want from the list.
Then click “Point shape” to change the style of the point.
Choose one of the options from the list.
As we can see, it’s added the diamond points to the line.
Editing the title, subtitle and axis titles
Finally, we can also edit the titles in our chart. In the chart editor>Customise, click on “Chart & axis titles”, then “Chart title”. Choose the title you want to edit.
Here, I’ve edited the title, sub-title, and vertical axis.
The chart editor, is great improvement on the old version and is much easier to use. Have fun!
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” - iBooks store / Kindle store
- "Beginner's Guide to Google Sheets" - iBooks store / Kindle store
- "Beginner's Guide to Google Docs" - iBooks Store / Kindle store
- "Beginner's Guide to Google Slides" - iBooks Store / Kindle store
- "Google Sheet Functions - A step-by-step guide" - iBooks Store / Kindle Store