Google sheets – Creating charts (part 2)

This is the second part of looking at creating charts in Google Sheets. See part 1 here. The quick edit mode covers the majority of most people’s needs but Sheets offers you some more ways you can control your charts. Let’s look at some of those options.

I’m going to use the same data as before, but this time I’m going to include the column headers in row 1 (Month and Sales).

Select the data and headers and click on Insert chart from the toolbar.

As you can see it’s automatically labelled the x and y axises with the column headers. It’s also labelled the series “Sales” and you can see that in the legend. Plus it’s given a title based on those headers. So, to save time I would certainly recommend that you include headers when first selecting your data.

Sheets17 - 1

In the chart editor there are 3 tabs, “Recommendations”, “Chart types”, and “Customisation”. All three show a preview of your chart on the right, so you can see how your chart looks before inserting into your sheet.


Recommendations

There are two main areas here:

  1. Data area selected – This can be edited and added to.
  2. 6 possible charts you could use – Sheets has a guess at which ones you’ll probably want based on the data selected.

Sheets17 - 2

Sheets17 - 3


Chart types

In the second tab, “Chart Types”, at the top you have the data area selected, plus some options to determine how the data is used. For now and for only one set of data, the “Use row 1 as headers” is a useful one, as if you hadn’t previously selected the headers, you could add them here.

Sheets17 - 4

Underneath, is where you have a wide selection of chart types to choose from. Line, Area, Column, Bar, Pie, Scatter, Map (if your data consists of geo-tags), and Other, which consists of other types and also, graphics which aren’t charts at all, for example, an organisational chart.

Sheets17 - 6Sheets17 - 5Sheets17 - 7Sheets17 - 8

The ones greyed out, you can’t select, usually because your data doesn’t match the chart type.


Customisation

A lot of the controls in this section we covered in the quick edit mode in part 1. So, you can either edit your chart by clicking on the various areas of your chart, or you can enter here and edit all in one place.

Plus, there are extra options here to edit the way your chart looks. It’s important to note that some of these options change depending on the chart type selected. Let’s look at what else it offers for a column chart.

Features

Sheets17 - 10

  • Maximise – This fills maximises the space used by the chart.

Sheets17 - 9

Sheets17 - 11

  • Reverse – Changes the direction of the data

Sheets17 - 12

Error bars – These add either a constant error or error percentage to your chart.

Sheets17 - 13Sheets17 - 14


Let’s look at charting two data sets. Here I have sales of two of my books (blatant plug!). To create the chart, I do the same as before and select all the information, then select Insert Chart from the toolbar.

Sheets17 - 15

As you can see, it’s automatically picked up both sets of data. It’s also guessed that a line graph would be best in this case.

Sheets17 - 16

Clicking on “Customisation” and scrolling down, we get to the “Series” section. Here you have the choice of changing the way the lines look for all the lines or specific ones. If you want to choose a specific line (series), just click on “All Lines” and choose the one you want.

Sheets17 - 17

Let’s leave it on All Lines. Now let’s change the way the lines look. For a line graph, there are 3 options. Line thickness, Point size and Point shape. here I’ve changed all three so you can see what they look like. So now the lines are thicker, they have points in the shape of triangles.

Sheets17 - 18 Sheets17 - 19Sheets17 - 20


To make things easier and quicker, I would always try to put all the data together in a table, but sometimes you may have the data in separate columns which you can’t move. Here you will have to select the data using the Ctrl key (Cmd on Mac). In the example below, I’ve selected columns D and E, then holding down the Ctrl key, I’ve also selected column G.

Sheets17 - 21

When you click on Insert Chart, this time you don’t get the line graph being selected automatically, so you’ll have to change it manually.

Sheets17 - 22

Go to Chart types and select the Line graph. As you can see it adds the second series (Book 3), but the title doesn’t change nor does the y axis title. Whilst these can be changed, it just means extra work for you.

Sheets17 - 23Sheets17 - 24


Finally, what happens if you have two sets of data but they have two different scales, for example, the first set is number of books sold and the second one is the revenue from those books in pounds (£)?  Let’s see.

1) Select the data, then insert the chart, then choose line graph.

Sheets17 - 25

2) It’s showing both sets of data but the sales is using the revenue scale. So, we need to give it its own scale.

Sheets17 - 26

3) In “Customisation”, go to Series and select the “Sales” series.

Sheets17 - 27

4) Underneath, select “Left axis” and change it to “Right axis”.

Sheets17 - 28

This is much better. We now have two separate scales, the left one is for the revenue and the right one is for the sales.

Sheets17 - 29


To access the Chart editor once your chart is made, click on the little triangle in the top-right corner and select “Advanced Edit”.

Sheets17 - 30

Have fun!


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s