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+)


Google Sheets – Creating charts (part 1)

Continuing the theme from my last post on sparklines, a chart really is worth a thousand numbers. In this post I’ll show you how quick and easy it is to create full charts.

We’ll quickly look at:

  • Creating and inserting a chart
  • Moving a chart to its own sheet
  • Saving & copying a chart

Then we’ll look at the various options available within Quick Edit mode

  • Graph
  • Axis
  • Legend
  • Axis area
  • Chart area
  • Titles, etc

Creating and inserting simple chart

Here we have some sales figures.

1) To create a chart, first select the months and the figures.

Sheets16 - 1

2) On the toolbar, click the “Insert chart” icon

Sheets16 - 2

3) This opens the chart editor and shows you a preview of the chart it has created. For now, let’s just insert into our sheet by clicking on the blue button “Insert”.

Sheets16 - 3

This places a chart on your sheet, and quite often right over your data, so you’ll have to move it. Just click once on a white part to select it, then click and hold to drag it to a new position on the sheet.

Sheets16 - 4Sheets16 - 5

As you can see, it’s added the months on the x axis for you and it’s worked out quite a good y axis scale for you.


Moving a chart to its own sheet

Sometimes it’s fine having the chart on the same sheet as your data, but there are times when you want to just show the chart and also you may want the chart to be bigger and there isn’t space on the sheet, without affecting your data or without having to move around the sheet.

1) Click on the little triangle in the right-hand corner of the chart, to open the menu.

Sheets16 - 6

2) Click on “Move to own sheet”.

Sheets16 - 7

This will create a new tab and the chart will be displayed full size.

Sheets16 - 8


Copying a chart / Saving an image of a chart

The full size view is particularly useful if you want to copy the chart to somewhere else, where you are just displaying the chart in details, for in Google Slides.

Open the same menu as before, you click on “Copy chart”. This adds it to your clipboard and you can paste it to another Google App, for example, Google Docs or Slides.

Another way to get a copy of the chart is to save an image of it, which is useful for pasting into other documents or programs. From the same menu again, click on “Save image”. This will open a dialogue box asking what to call the image and where to save it on your local hard drive.


View / Quick edit mode

To the top right of the chart, there are two icons, the eye and the pencil.

Sheets16 - 10

Click on the eye icon to select view mode. This will prevent the chart being accidently edited and also it allows you to see the data values for each of the bars as you hover over them.

Sheets16 - 11

However, when you are first creating the chart you will want to be in quick edit mode, which can be selected by clicking on the pencil. When the chart is first created it is by default in this mode.


Quick Edit mode

There are a wide range of ways you can edit the graph whilst in quick edit mode, without entering the full chart editor.  Let’s edit this chart in a variety of ways to show you some of the ways you can edit your own charts.

Important: The first thing to be aware of is that most of these different editing options will depend on where you click on the chart.


Clicking on the graph

Click on one of the blue bars in the graph and this brings up the graph options:

Sheets16 - 12

Change colour

Sheets16 - 19  Sheets16 - 20

Change side of the y axis (left or right)

Sheets16 - 21

Add data values to the graph

Sheets16 - 22 Sheets16 - 23


Clicking on the y axis

Click on the y axis of the graph and this brings up the y axis options:

Sheets16 - 13

Min / Max

Type in the range you want and press “Enter”. You don’t have to enter both, for example, you can add just a maximum figure.

Sheets16 - 24  Sheets16 - 25

You can also change the font size, colour and add bold or italics.


Clicking on the x axis

Click on the x axis of the graph and this brings up, surprise, surprise, the x axis options:

Sheets16 - 16

As above, you can change the font size, colour and add bold or italics.

Sheets16 - 26

Plus, you can change the orientation of the words on the x axis.

Sheets16 - 27  Sheets16 - 28


Clicking on the legend

The font can be changed, plus, you can control where you put the legend.

Sheets16 - 15

To change the position, click on “Right”, then select the position you want.

Sheets16 - 29  Sheets16 - 30


Clicking on the white part of the graph (axis area)

This controls the way the axis area looks.

Sheets16 - 31

Moving along the top row of icons. The first changes the background colour.

Sheets16 - 32

The next one changes the opacity of the colour, which if you reduce it from 100%, will lighten the colour.

Sheets16 - 33

The next one controls the number of horizontal gridlines, which also automatically changes the y axis numbers.

Sheets16 - 34

Sheets16 - 35

The last one, changes the colour of the gridlines. Here I’ve changed them to black.

Sheets16 - 36 Sheets16 - 37

On the row below, there are two more options, “Move & Resize” and “Fit to area”. Click on “Move & Resize” to do just that, it allows you to resize the graph (vertically and horizontally) and move to where you want it within the chart area.

Here I’ve widened it and made it a bit taller. Note, the data labels have changed automatically.

Sheets16 - 38

To exit the editing, just click on the white outside of the axis area. To return it back to its original size and position, click on “Fit to area”.


Left-clicking on the chart area outside of the graph area

Click on the white area surrounding the graph (axis area) and the chart area menu will appear.

Sheets16 - 17

There are 3 options, the first controls the font of the axis and data labels.

Sheets16 - 39

The second changes the fill colour of the area surrounding the chart. Here I’ve gone for a tasteful shade of red, but for a reason. If you notice, this has also changed the axis area colour to a kind of orange. This is because this fill changes the colour of the entire chart box, and as I changed the opacity of the axis area to 40%, this means that some of the colour behind it is showing through. So, we end up with a mixture of yellow and red, which of course makes orange.

Sheets16 - 40

The third option controls the opacity of the chart area background colour, so here I’ve dropped it to 20%, which is far more subtle. As you can see it also lightened the axis area colour.

Sheets16 - 41


Right-clicking on the chart area

All the above involve LEFT-clicking on various areas, the final set of options involves right-clicking anyway on the chart. This brings up the menu below.

Sheets16 - 18

The “Chart area” and “Legend” options open the same options as described above.

Series selects a specific range of data, but as we only have one here, let’s ignore that for now.


Title

Click on “Title” to add a chart title. You can change the font size, etc too.

Sheets16 - 42Sheets16 - 43


Axis

Click on “Axis” and this brings up various axis options. We’ve seen most of these already. The only ones we haven’t are the Horizontal & Left Vertical titles. These give you the option to add names to the respective axis.

Sheets16 - 47

To add a label to the Y axis, click on “Left vertical axis title”. Then type in the name you want and press Enter.

Sheets16 - 45    Sheets16 - 46


Change chart

Finally, you can of course change the type of chart. Click on “Change chart” and you have a selection of the most popular charts. Just click on the one you want.

Sheets16 - 48

Here I selected Line chart.

Sheets16 - 49


Summary of the quick edit options

  • Clicking on graph
    • Change colour
    • Change y axis
    • Add/remove data values
  • Y axis options
    • Min/Max
    • Font size, etc
  • X axis options
    • Font size, etc
    • Orientation of text
  • Legend
    • Font size, etc
    • Position
  • Axis area
    • Background colour & opacity
    • Number of gridlines
    • Gridline colour
    • Move & resize graph
  • Chart Area
    • Background colour
    • Font size & style
  • Right click
    • Chart area
    • Title
    • Legend
    • Axis
    • Series
    • Change chart

The best thing is to just play around with various options to get to know the controls, then you’ll be creating charts a little more tasteful than mine!


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Sparklines (mini-graphs)

We generally use data to give us information on something and to help us make more informed decisions. However, human beings aren’t very good at looking at a set of numbers and working out whether there is a trend or whether something looks unusual. This is where charts are invaluable. The old adage of a picture paints a thousand words, well it certainly paints a thousand numbers. One chart can instantaneously tell you what’s going on with the data.

There are two ways to create charts in Google Sheets, one is the more traditional approach, i.e. making a big chart from the data and either putting it on your sheet or as a separate sheet  (I will cover how to do this in a future post) or the other is inserting mini-graphs right next to your data to show what’s going on. These mini-graphs are called sparklines and that’s what I’m going to show you how to make in this post.

Here we have the monthly sales of 5 products (A to E). The sales manager wants to know how well each product is doing. Looking at just the data we may have some ideas but charts would make analyzing it so much easier.

Sheets15 - 1

Inserting basic sparklines (line graphs)

First, let’s add a sparkline in cell B16 which will show the trend of product A’s across the year.

Adding sparklines is the same as adding a function, type the following into cell B16:

=sparkline(B3:B14)

As you can see this produces a little line graph in the cell, which displays the Product A data.

Sheets15 - 2

Now we want to do the same for the other products but instead of typing in the sparkline function, we can just copy it across like any other function and formula.

Click on the cell and grab the little blue square in the bottom right-hand corner of the cell and drag it to cell G16.

Sheets15 - 3 (1)

Sheets15 - 4

Now we have trends for each of the products (and the total) and we can easily see which product’s sales are falling and the sales manager can take some action.

If we want to increase the x or y axis of the sparklines, maybe to see the chart in more detail, we just increase the row height or column width.

Sparklines are therefore excellent for giving you a quick insight into what’s happening with your data and are really easy to set up. Also, as they are small they are great for using with dashboards, where you may have lots of different data sets all on one sheet.


ADDING EXTRA OPTIONS TO YOUR SPARKLINES

Line graphs

We can control how the sparkline looks by adding some options to our function above.

The initial graphs we’ve made are fine for seeing the trends of the sales, but what happens if we want to compare the products with each other. There’s a problem, the maximum sales for each graph is different, so for example, product B’s maximum is 150 whereas, C’s is only 90, yet the graphs look like they have sold the same amount in December. This is because the y axis is different for each graph, so to be able to compare the sales of each product with each other, we need the same y axis for each of the graphs.

So let’s set the minimum and maximum value of the y axis, so all of them are the same. This is where we add set the options of the graphs within the function.

In B17 type the same function as before except without the last bracket:

=sparkline(B3:B14

Now let’s add the options. First let’s add the maximum for the y axis, let’s make it 150:

=sparkline(B3:B14,{“ymax”,150})

Usually the options are in pairs, the name of the setting and then the value.

Note, the syntax. After the range, there’s a comma, then open curly brackets, then ymax in speech marks, comma, then 150, then closed curly brackets, and finally a normal closed bracket. There are no speech marks needed for 150 as it’s a number.

Now let’s add the minimum for the y axis, and let’s make it 30:

=sparkline(B3:B14,{“ymax”,150;”ymin”, 30})

Here we’ve added another pair of settings, ymin along with the value 30. Note, a semi-colon separates the ymax setting and the ymin setting.

On its own the new graph doesn’t tell us anything different. So, let’s copy and paste it as before along the row.

Sheets15 - 6

Now we can compare the graphs with each other. If we look at the second and third one, we can now see, for example, that product C’s sales are lower than product B’s.

Sheets15 - 7

We can also change the way the line looks in terms of line colour and line thickness.

In B18 type:

=sparkline(B3:B14,{“color”, “red”})

Now we have a red line.

Sheets15 - 8

There is another way to change the colour of the line. Just change the font colour of that cell.

 Sheets15 - 11   Sheets15 - 10

Here I’ve changed the font colour of the second graph to pink. Note, you can’t change a colour in this way, if you’ve already set it in the function.

In B19 type:

=sparkline(B3:B14,{“linewidth”, 3})

And this time we have a thicker line. The higher the number, the thicker the line.

Sheets15 - 9

So, as you can see we can quickly edit the way the graphs look, just by adding more options.


Changing sparkline chart type

By default, the Sparkline function will create a line graph, but there are times when other types of chart are more suitable. There are four types of chart to choose from:

  • Line
  • Column
  • Stacked Bar
  • Winloss

To change the chart type, we just add a pair of settings like we did above.

Let’s add a column chart. In cell B20 type:

=sparkline(B3:B14,{“charttype”,“column”})

This time we have the data represented in a column chart.

Sheets15 - 12

There are lots of other options you can use. Let’s add red to the lowest sales figure and green to the highest one. Type in the function below in cell B21.

=SPARKLINE(B3:B14,{“charttype”“column”;“lowcolor”,“red”;“highcolor”,“green”})

In this limited data, it’s pretty obvious which is the lowest and which is the highest, but you can see how it can be used.

Sheets15 - 13

The sales manager has decided to focus on product C and wants to see the month to month differences. He sets up the chart and adds a sparkline at the bottom. This time he’s colouring the times there was a negative movement in sales in red using this function:

=sparkline(K5:K15,{“charttype”, “column”;“negcolor”, “red”})

As you can see it’s very clear when there was negative movement in the sales.

Sheets15 - 14

Alternatively, if he just wanted to know in which months there were either negative or positive movements without worrying about the actual sales figures, he could have used a winloss graph:

=sparkline(K5:K15,{“charttype”, “winloss”;“negcolor”, “red”})

As you can see in the bottom graph, you can see when there was either a positive or negative movement in the sales.

Sheets15 - 15


The last chart type we have is a stacked bar graph. The complete bar is the total of all the data and then it’s divided into the parts.

This time the sales manager wants to compare the sales of Product B and C. Type in:

=sparkline(N4:O4,{“charttype”,“bar”})

Then copy the function down the cells. We can see that in January, the sales were the same, but then C’s increased while B’s decreased, then the trend reversed and B’s increased and C’s decreased. So, maybe there’s some kind a connection. It’s something the sales manager needs to investigate.

Sheets15 - 16

You can change the colours in the graph by using “color1” and “color2” for the first and second colours respectively.

You see this type of graph a lot in sports game, when they are talking about things like possession of the two teams.


Here’s the Google support page on sparklines, which gives you more options for each of the graphs.

As you type in the sparkline function (as with all functions) a box pops up giving you some useful information on how to complete the function. It gives type of information needed, an example, then what the function does.

Sheets15 - 17


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – What’s the difference between notes & comments?

There are two key ways you can add extra information to your sheet, without affecting the format of your sheet: Notes and Comments.

Sometimes people confuse these, as they both give extra information in the cells, but there is a clear difference between the two.

The key difference is:

Notes are simply little text boxes added to the cell which just add extra information. These are viewed by hovering over the cell.

Sheets14 - 1

Comments are also little boxes that are added to the cell with extra information, but these are usually used to make a comment to someone requesting some kind of response. They also allow you to see who added the comment.

Sheets14 - 2


Adding notes

I use these to remind me of extra information related to that cell. Let’s look at an example.

Here I have a teacher’s timetable and I want to remind myself of some information about her classes, for example, when they will finish.

Sheets14 - 13

1) Right-click on the cell you want to add the note in, and select “Insert note”.

Sheets14 - 3

2) A text box will appear. Type in the note you want, then click away from the box to add it.

Sheets14 - 4  Sheets14 - 5

3) As you can see a little black triangle in the top right-hand corner is now visible. This shows you that there is a note in that cell.

Sheets14 - 6

To view the note, just hover over the cell and the text box will appear.

Sheets14 - 1


Editing a note

To edit the note, just click inside the text box and type.


Deleting a note

To delete the note, right-click on the cell and select “Clear notes”.

Sheets14 - 14


Adding comments

1) Right-click on the cell you want to add the comment in, and select “Insert comment”.

Sheets14 - 7

2) A text box will appear with your name. Type in the comment you want and click on “Comment”.

Sheets14 - 8

Sheets14 - 9

3) As you can see a little yellow triangle in the top right-hand corner is now visible. This shows you that there is a comment in that cell.

Sheets14 - 11

To view the comment, just hover over the cell and the text box will appear. As you can see it also adds the time and date the comment was left.

Sheets14 - 10

As these are generally used to request a response. Other users of the sheet, will be able to read the comment and do one of two things, reply or mark the comment as resolved.


Replying to a comment

To reply to the comment, hover over the cell and click on the text box. A reply box will appear below, which you can type in.

Sheets14 - 12

Type in your reply and click on “reply”.

Sheets14 - 15  Sheets14 - 16


Resolving a comment

If you consider the comment is now resolved, then hover over the cell and click the “Resolve” button. This will remove the comment.


Editing a comment

To edit the note, bring up the note as described above, then click on the 3 dots. Then click on “Edit”.

Sheets14 - 17


Deleting a comment

To delete the note, bring up the note, click on the 3 dots, and click on “Delete”.


Comments – Tab info

When inserting a comment, apart from adding the small yellow triangle in the cell, it also adds a number in the sheet tab, which tells you the number of comments on that sheet, in this case, 1.

Sheets14 - 18

Clicking on the number in the tab, opens all the current comments on that sheet, which can be very useful if you have lots of comments on the sheet.

Sheets14 - 19


So, in summary, generally I use notes for just adding extra information to a cell and I use comments when I expect a response from someone.


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Merging cells

Cells on the spreadsheet can be combined together by what’s called “merging”. This helps your information be understood more easily.

Here I have some products and I wanted to work out the profitability of them. The cells in yellow are related to income and the ones in blue are costs. As you can see, the income and costs titles don’t look very good the way they are at the moment, and I want to use them as headers for the information below, to show that they are connected.

Sheets13 - 1

Merge all

1) Let’s start with the income. Select the cells you want to merge.

Sheets13 - 2

2) From the toolbar, click on the merge icon (2 arrows facing each other).

Sheets13 - 4

3) This combines the 3 cells together making it one cell.

Sheets13 - 5

4) Usually I add a bit more formatting, for example, add a border and centre the word. Use the shortcuts from the toolbar.

Sheets13 - 7  Sheets13 - 6

As you can see, I now have a header for the three areas of income.

Sheets13 - 8

Then I follow the same process for the costs. I’ve also filled in the income and costs cells in the same colour as the cells below them. Now the table looks much better.

Sheets13 - 9


Merge vertically and horizontally

Now I want to add a bit more information to my table, to help explain it a little better. Under sales and price, I want to add some text, to add extra information.

1) Select the an area below the 2 columns, here I’ve selected 4 rows and 2 columns, as I think that will give me enough space.

Sheets13 - 10

2) Go to the merge icon and click on the little triangle to the side of it to bring up the merge menu. Here I want to choose “Merge vertically”.

Sheets13 - 11

As you can see it’s merged the 4 rows in the first column and the 4 rows in the second column.

Sheets13 - 12

Now I add the information I wanted. In the first column under sales, I want to add the countries the sales were in. I typed these in by, typing “UK” then Ctrl+Enter, to move down a line, without moving down a cell, otherwise, your text will be written horizontally.

Sheets13 - 13  not this: Sheets13 - 18

I then add a comment in the cell under price. Note, I’ve written a sentence and it’s too big for the box. So, I need to change the cell wrapping to “Wrap” by using the wrap icon on the toolbar.

Sheets13 - 14  Sheets13 - 15

Now I have my additional information in the cells.

Sheets13 - 16

One final thing, I want to align the text to the top of the cell not at the bottom as it currently is. Click on the align icon in the toolbar (downward facing arrow) and select the upward facing arrow.

Sheets13 - 17

Now we have the table looking the way I want it.

Sheets13 - 19


“Merge horizontally” works in exactly the same way.

If you want to undo a merge and redo it. Select “Unmerge” from the merge toolbar then if you want you can merge a different range.

Note, the cell reference of merged cells is the upper left cell. So if you merge cells A1 to C3, the reference will be A1.


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Sorted!

Here we’re going to look at how to sort your data in numerical order and alphabetical order.

Here we have some performance figures and I want to sort them by performance, starting with the highest and ending with the lowest ones.

1) Select the data. In this first example, let’s ignore the headers in row 1.

Sheets12 - 1

2) Go to the Data menu and click “Sort range…”.

Sheets12 - 2

3) I want to sort by the figures in column D, so I click on “Column A” to change it.

Sheets12 - 3

4) Then I select “Column D”.

Sheets12 - 4

5) I also want to sort from highest to the lowest, so I need to select “Z->A”.

Sheets12 - 5

6) Click the blue “Sort” button.

As you can see, the data is now sorted by performance.

Sheets12 - 6

Note, the “Sort range” menu can also be accessed by selecting the range then right clicking.

Sheets12 - 17


What happens if I want to sort it by class and within each class, I want the students’ performance to be sorted from highest to lowest? That’s easy, we just need to create 2 sort rules.

Follow steps 1-2 above, so we have the first sort criteria (column A, sorted AtoZ).

1) Then click “+ Add another sort column”.

Sheets12 - 7

2) Click on “Column B” to change it to column D.

Sheets12 - 8 Sheets12 - 9

3) Click “Z->A” to change it to highest to lowest. Then click “Sort”.

Sheets12 - 10

As you can see the data is now sorted by class (A to D) and within each class by performance (highest to lowest).

Sheets12 - 11


The above didn’t include the column headers, but this time let’s include them, to see how that affects the sort list.

1) Select all the data including the column headers.

Sheets12 - 12

2) Go to the Data menu and Sort Range as above. Then click “Data has header row”. You will see the sort by drop-down menu has changed from “Column A” to “Class”.

Sheets12 - 13

3) Click on “Class” and you will see the column headers, you can sort by. So if we wanted to sort by performance as before, we would click on “Performance”.

Sheets12 - 14


Finally, you may have noticed other sort options in the Data menu.

Sheets12 - 2

The top two “Sort sheet by column A…” will select the entire sheet and then sort whatever is in column A. This is useful if you have a sheet full of data, and saves you having to select it beforehand.

The next two “Sort range by column A…” will sort just the range you have selected and will sort column A. This is useful if the column you want to sort is the one in which your cursor is.

For example, below the same range has been selected, but the one of the first one was selected from left to right and so the cursor remains in cell A2, and so the sort range would be in column A. The second one was selected from right to left and so the cursor remains in cell D15, and so the sort range would be in column D.

Sheets12 - 15  Sheets12 - 16


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Conditional Formatting & custom formulas

Sometimes we spend time colouring in our sheets so that the data is more visible, in a way that it can be read and understood really quickly and that the most important points can be ascertained with ease. Wouldn’t it be great if Sheets could colour your data automatically, depending on a set of conditions? Well, of course, it can. One key way is ‘Conditional Formatting’.

Let’s go through an example, to show how easy it is to do and yet how effective it can be.

Here I have some exam results and I want to highlight those who have passed.

1) Select the range of data.

Sheets11 - 1

2) Right click and choose “Conditional formatting”.

Sheets11 - 2

3) This opens up the Conditional format rules dialogue box. Under “Apply to range” it shows us the range we just highlighted and the one which we’re going to add the formatting to. Underneath in the “Format cells if…” part, click on “Cell is not empty”.

Sheets11 - 3

4) This opens the various conditions. Let’s start with a common one. Click “greater than” as we want to highlight those with an exam mark better than a minimum, i.e. they have passed.

Sheets11 - 4

5) A box will appear below to type in the value. Let’s type in 0.6, as I want to know who has more than 60%. (Remember it’s 0.6 not 60 we need to write here as it’s a percentage).

Sheets11 - 5

Below this, you have a choice of formats. You can add Bold, Italics, Strikethrough, or you can change the font or cell colour. Probably, the last two are the most common. I’m going to fill the cells with green for those who have passed, so I click on the fill box and the colour palette opens. Just select the colour you want. Then click “Done” at the bottom of the dialogue box.

Sheets11 - 6

As you can see, this colours all those above 60% in green.

Sheets11 - 7


You can set more than one rule at a time. So, let’s colour those who have failed in red. The process is very similar to before.

1) Select the range and right-click, then select “Conditional formatting”.

Sheets11 - 8

2) In the rules, you’ll see the one you created before, with a summary of what it does. Below it click “Add new rule”.

Sheets11 - 9

3) From now on it’s the same process as before, except we’re going to select a different condition. Click “Cell is not empty”, then “Less than or equal to”.

Sheets11 - 10  Sheets11 - 11

4) Type in “0.6” in the value box that appears, and this time let’s colour it red, by clicking on the fill colour and selecting a shade of red.

Sheets11 - 12

  Sheets11 - 13Sheets11 - 14

Now we have those who failed the exam highlighted in red.

Sheets11 - 15

Watch the video explanation on YouTube:

Conditional Formatting – Part 1


Conditions

We can set up all sorts of conditions using the list below and as you saw from above, these can be combined in countless ways.

Let’s look at a few of the options Sheets gives us. There are 5 groups:

  1. Is the cell empty or not?
  2. Text – What does it contain, start with, end with, or does it match an exact word or phrase?
  3. Date – Does the date match another? Or is it one before or after the specified date?
  4. Numerical – Is the number the greater, less, equal or between some number(s)?
  5. Does the cell match a specified formula you’ve set?

 Sheets11 - 16

Watch my video explanation of these on YouTube:

Conditional Formatting – Part 2

Personally, I think the first 4 groups are easy to set up and are similar to the way I described above. The last one however, custom formulas, is very different and can be a little complicated sometimes, so, as this is all about the basics, I’m just going to show one useful example of a formula you can use.


Custom formula

Here we have some performance results of some students. I want to highlight those who didn’t achieve the minimum 50%. AND this time I don’t want to just colour in the performance figure, I want to colour in the whole row. This is where a custom formula helps.

1) Select all your data except the Headers.

Sheets11 - 22

2) As before, right click and click Conditional Formatting. Click on “Cell is not empty”.

Sheets11 - 18

3) At the bottom of the list of conditions, click on “Custom formula is”.

Sheets11 - 19

4) Type in the following formula and click “Done”.

=$d2<0.5

Sheets11 - 20

You will see that all the rows that contain a result of under 50% are now highlighted. Magic!

Sheets11 - 21

So, how did that work? Let’s look at the formula and understand what’s happening.

=$D2<0.5

The equals sign, tells Sheets it’s a formula. Then I tell it that if D2 is less than 0.5 (50%), then fill it green. The dollar sign (see my post on absolute references) makes sure that the column D never changes as you move columns, but as the row number doesn’t have a dollar sign it can change as you move rows.

Sheets11 - 23

So, let’s look at cell by cell. In cell A2, the formula is still referring to D2, as the dollar sign made sure the column didn’t change, even though we’re in column A, and we are still in row 2. The same goes for cells B3 and C3. So, all of them look at D2 to see if the value is less than 0.5 (50%), which in this case it wasn’t, so it hasn’t changed the colour of any cells.

In A3 we notice that the formula has changed to D3. This is because the dollar sign has prevented the column changing, but this time we are on the row below, and as there is no dollar sign next to the row number, it changes by 1, to row 3. This time the cells in that row are looking at D3 to see if it is less than 50%, which again it isn’t. And so on. D5 was under 50%, so it coloured the row green.

This is more difficult to describe than it really is! Custom formulas give you greater scope to control how you format your data, so you are not just limited to the preset list you are given. Play around with them, they are a wonderful tool!

For an extra use of conditional formatting and custom formulas, see my post on using the functions ISEMAIL, ISNUMBER, ISURL and NOT, to check that emails addresses, etc are ok.


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Filtering your data

Let’s look at how you can filter your data to show a particular aspect of it. There are two ways to do this:

  • Filter (by value & by condition)
  • Filter View

Filter – By value

If we have a set of data, we can add some filters to enable us find the information we want.

In this example, we have some course books which the teachers are using, and I want to be able to see what books a particular teacher is using.

Sheets10 - 1

Select the headers of the columns. Generally, I select all of them just in case I want to filter any of the columns.

Sheets10 - 2

Go to the Data menu, and click on “Filter”.

Sheets10 - 3

or use the toolbar shortcut: Sheets10 - 10

Small little boxes with triangles will be added in the header cells.

Sheets10 - 5

To access the filter menu, click on one of the squares. For example, I want to filter the list by one of the teachers.

At the bottom of the dialogue box, under “Filter by values”, you will see the list of teachers with ticks next to them. This means that they are selected and that they will be visible in the list.

Sheets10 - 6

Here I want to see just the books that Bob has. So, I untick the other teachers and leave Bob ticked. An alternative way, if you have a long list, is to click “Clear” and then tick the one you want. Then click “OK”.

Sheets10 - 7

As you can see this hides the other teachers from the list and just leaves Bob.

Sheets10 - 8

To reset the list, click back on the same filter square, and click on “Select all”, then click “OK”.

Sheets10 - 9

The full list will return.

Sheets10 - 4


Filter – By condition

The above filters by specific values, which you can hide or unhide. But how do you filter a range of values based on some kind of stated condition? That’s where “Filter by condition” comes in.

Here we have some exam results and I want to filter the list to show those who haven’t achieved the pass mark of 50%.

Sheets10 - 11

Click on the filter in the Exam Results cell. Then click on “Filter by condition”.

Sheets10 - 12

A box will appear below “None”. Click on that and you will have various conditions to choose from. As I want to see who had less than 50%, I select “Less than”.

Sheets10 - 13Sheets10 - 14

Another box will appear below, where you fill in a value, or sometimes a formula. In this I’ll type in 0.5. Note, I need to type 0.5 and not 50, as I’m working with percentages here. Then click “OK”.

Sheets10 - 15Sheets10 - 16

As you can see, the list now shows the 3 students that didn’t pass.

Sheets10 - 17

You can reset the list by clicking on the same filter again, then click on the condition, in this case, “Less than”.

Sheets10 - 19

Then click “None” for no condition.

Sheets10 - 18


Filter View

 The above filter is great for most cases but it does have a couple of disadvantages.

  1. When you filter something anyone else also viewing that sheet will also see the filtered view, and sometimes you don’t want to affect how someone else sees the sheet.
  2. Sometimes you want to use the same filters over and over again, and having to set them up each time would be a little bit annoying.

So, you’ve guessed it, there’s an alternative way to filter which addresses these two issues.

Here I have some performance figures of some students in four different classes (A, B, C, and D). I want to create a filter for class A.

Sheets10 - 20

1) Select the header row and all the columns you want to include in your filter.

Sheets10 - 21

2) Go to the Data menu, click “Filter Views” then click “Create new filter view”.

Sheets10 - 22

This brings up the filter view. This is where you tell it what to filter by actually filtering the results.

Sheets10 - 23

Click on the Class filter.

Sheets10 - 24

Then untick classes B, C, and D, leaving A ticked. Click “OK”.

Sheets10 - 25

This will filter the list, showing only Class A. Others viewing the sheet with their accounts, won’t see this view, they will see the sheet as normal.

Sheets10 - 26

Now, it’s a good idea to rename the filter, so you know which one is which. Next to “Name:” click on “Filter 1”, then type in the new name, in this case “Class A” and press Enter.

Sheets10 - 27  Sheets10 - 28

To close the filter, click on the cross on the right hand side of the page.

Sheets10 - 29

To filter by that filter again, go to the Data menu, click “Filter views”, then click “Class A”.

Sheets10 - 30

It will filter exactly what you set up the first time.

Sheets10 - 31

You can also set up filters using the “filter by condition” option.


Duplicating a filter

Sometimes you want to create filters that are similar to each other, and the best way is to duplicate one. For example, with the list above I want to create a filter for each class. So let’s create one from the Class A filter already made.

1) Click on the cog on the right-hand side of the screen, and click “Duplicate”. This makes makes a copy of the existing filter.

Sheets10 - 32

2) Change the filter name, e.g. Class B.

Sheets10 - 33  Sheets10 - 34

3) Click on the filter you want, e.g. the class one.

Sheets10 - 35

4) Click on the values you want, e.g. B.

Sheets10 - 36

4) Click the cross to close the filter.

Sheets10 - 37

Clicking on Data > Filter views, you will see the new filter added to the list.

Sheets10 - 38


If you want to delete a filter, open the filter via the Filter views menu, and click on the cog. Then click on “Delete”.

Sheets10 - 39


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Sharing, printing, downloading

Ok, so you’ve entered your data, you’ve made it look pretty, and you’ve used some awesome formulas. Now what? You usually want to share it with someone, somehow. There are a few ways you can do this:

  • Print it
  • Download it in different formats
  • Email it
  • Share it

Printing sheet(s)

Even in this paperless age there are still times where having a copy on paper is useful.

Click on the printer icon in the toolbar to access the print settings menu.

Sheets9 - 1

Here you will have various settings to change the way your printed sheet(s) look like.

Sheets9 - 3

Let’s look at them one-by-one. Under Options:

Current sheet – Print just the one sheet you currently have open.

All sheets – Print all the sheets in the file.

Selection – Print a specific area you have selected on a sheet.

Sheets9 - 4

Unfortunately, at the moment you don’t have the control like you do in Excel, where if you have a large amount of data on a sheet and it goes across more than one page, you can control what is on each page. One way round this, is to use the Selection option, to select what you want on the first page, then print, then select what you want on the second, etc.


Then you have some formatting options:

Repeat row heads on each page – If you have a long list of data with the headers in row 1, you can choose to repeat that row on each page. This is useful as without it, on the second page onwards, you wouldn’t know what was in each column of data.

No gridlines – This removes all the gridlines where you haven’t added borders, i.e. the grey gridlines on the sheet. I almost always select this one.

Include document title – On each sheet you can add the filename at the top of the page.

Include sheet names – On each sheet you can add the sheet names at the top of the page, which is particularly useful if you have lots of sheets with different information.

Include page numbers – On each sheet you can add the page numbers at the bottom of the page.

Sheets9 - 5


Under Layout you can control the size and orientation of the sheet(s):

Fit to width – This fits it to the width of the paper. If it’s a large sheet, it will automatically move across pages.

Actual size – Maybe you don’t want the sheet to fit the width, so here you can leave it at its actual size.

Portrait / Landscape – Control the orientation of the sheet on the paper.

Sheets9 - 6


Once you have chosen the settings, click Print to open the Print menu.

Here it will tell you how many sheets of paper will be printed, which is a good double-check in case your settings are wrong or need changing.

Destination – Which printer you’re going to print on.

Pages – All or specific ones. Use a comma or hyphen to add more than one page. E.g. 1-5: prints pages 1 to 5;  1, 3, 4: prints pages 1, 3, and 4.

Copies – How many copies of each page you’re going to print.

Paper size – Usually ok left on A4, but sometimes you may need to change it, for example, to A3.

Options: Fit to page moves the contents slightly so there is space around the edge of the page.

Sheets9 - 7


Downloading a spreadsheet in different formats

Go to File > Download as

Sheets9 - 13

Here you will find various formats you can download your sheet as. Probably the most commonly used ones are .xlsx and .pdf. Just click the ones you want and it will download it to your computer.


Emailing a spreadsheet

There are two ways to email the spreadsheet.

1) Email collaborators – Sends a link to the spreadsheet to those it’s already shared with

2) Email as attachment – Sends a ‘physical’ attachment in an email (in .xlsx or .pdf format)

Both can be found in the File menu:

Sheets9 - 8

Email collaborators

Clicking on this, will open the Send message box. Within it, you’ll be able to send an email to those who the sheet is shared with. On the left is the email and on the right are the possible recipients. By default, everyone is ticked and will receive it, just untick those you don’t want to send it to. If you have a long list but only want to send it to a few people, then it’s quicker to click “none”, then tick those you want to send it to. Then press Send.

Sheets9 - 9

Email as attachment

If you want to send them a ‘physical’ copy of the sheet, maybe because they don’t have access to the Google Sheet or you want to send them a snapshot of what’s on there and not have live access to it, then you can send it as an Excel-friendly file (.xlsx) or as a PDF.

Sheets9 - 10

Fill in the email. Note that in the “To:” box, as you type the names of the recipients, if they are already in your contacts, they will appear below so you can click on them without having to type them out completely.

 Sheets9 - 12

Then select which format you want. Note, if you have a spreadsheet with lots of formatting, links and formulas, sometimes there are problems when the file is converted to the Excel format. Also, if you have a big spreadsheet, the PDF format is sometimes very small to read. If in doubt, check before sending them, by downloading the file (see above).

Sheets9 - 11


Sharing a spreadsheet

To share your spreadsheet from within the file, click on the blue “Share” button in the top right-hand corner of the screen.

Sheets9 - 14

This will open the Share with others dialogue box. Here you can add people you want to be able to edit or view your sheet. This is the same as doing it in Google Drive, see my post on sharing files in Google Drive.

Sheets9 - 15

Note, this shares the file with them and it will appear in their Shared with me filter. By default, they will receive an email saying that you have shared it with them, with a link to the file.


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets – Moving beyond one sheet

One way to think about Google Sheets is that it is like a page from a book with a grid pattern on it, but as the name Sheets suggests, you can have more than one page in the book. In one file you can have numerous sheets, with either separate things on them or interconnect the things between the sheets.

So, in this post let’s look at:

  • Renaming sheets
  • Adding colours to the sheet tabs
  • Duplicating sheets
  • Adding sheets
  • Deleting sheets
  • Hiding sheets
  • Moving sheets
  • Moving the tabs to find a particular sheet
  • Sheet tab menu
  • Adding references to other sheets

Most of what we’re going to look at, happens at the bottom of your screen around the tab area. When you create a new spreadsheet, by default, you’ll start with just one sheet called “Sheet1”, and the sheet has a tab with the name on it “Sheet1”.

Sheets8 - 1


Renaming sheets

We can change the name of sheet, by double-clicking on the tab and typing in the name we want. This is more important once you have more than one sheet, but isn’t essential, although I usually give them names so it reminds me what’s on each sheet.

Sheets8 - 2 Sheets8 - 3

You could also click on the triangle on the tab, to bring up the tab menu. Then select “Rename”, but this isn’t as quick as just double-clicking on the tab.

Sheets8 - 4


Adding colours to the sheet tabs

Apart from being pretty, colouring the tabs can help you find your data quickly. I’ve also used it to show the status of the data. Green being ready or completed and red meaning some work is needed on the sheet.

Click on the triangle on the tab to bring up the tab menu. Select “Change colour”. Then select one of the colours from the palette.

Sheets8 - 5    Sheets8 - 6


Duplicating sheets

There are a couple of ways to add more sheets. The first is to duplicate the current one. This has the advantage of copying the current data and formatting, which could save you time later on.

Click on the triangle on the tab to bring up the tab menu. Select “Duplicate”.

It will create a new sheet to the right of the current one, with the name “Copy of + name of the duplicated sheet”, e.g. “Copy of Sheet 1”. If you duplicated that one, you’ll get one with the name “Copy of Copy of Sheet 1”. Not the prettiest of names, so you’ll want to rename them. Note, duplicating also copies the colour of the tab.

Sheets8 - 7   Sheets8 - 8


Adding sheets

You can add a blank sheet by clicking on the plus button to the left of the tabs. Here, the newly created sheet will be called “Sheet2”. If you already had, let’s say 5 sheets, the new one would be called “Sheet6”.

Sheets8 - 12  Sheets8 - 13

Sheets8 - 9     Sheets8 - 10


Deleting sheets

To get rid of a sheet, click on the triangle on the tab to open the tab menu, then select “Delete”, then a warning will pop up, to check you want to do it, just click OK. If you only have 1 sheet, this option is disabled, as you can’t have a spreadsheet with no sheets.

Sheets8 - 10a  Sheets8 - 11


Moving sheets

To change the position of a sheet, you just need to change the position of the tab. Click and hold on the tab and drag it it to the position you want, then let go.

Moving tabs


Moving the tabs to find a particular sheet

If you have a lot of sheets, some of the tabs won’t be visible on the screen. The wider your screen, the more you will be able to see. To find the sheet you want, click on the arrows to the right of the tabs. These move along a page full of tabs at a time. For example, if I can only see 9 tabs at the bottom of the screen, when I click on the move arrow, it will move along 9 tabs to the next set of 9, so the last tab on the right moves to the first one on the left, assuming I’ve got that many sheets.

Sheets8 - 14  Sheets8 - 15

Sheets8 - 16

Moving tabs along

Tip: I usually try to use short names for my tabs, so I can see as many on the screen as possible.


Hiding sheets

Sometimes you may want to hide sheets. Typical reasons are that your end-users only need to see certain sheets; you have a sheet with all the calculations on it which you don’t want someone to accidentally mess up; you have archive data which you want to keep but it’s not used now, etc.

To hide the sheet, click on the triangle on the tab to open the tab menu, then select “Hide sheet”.

Sheets8 - 17


Sheet tab menu

Clicking on the ‘hamburger’ menu to the left of the tabs, opens up the sheet tab menu. This is useful for a couple of reasons. Firstly, if you have hidden a sheet, as explained above, this is where you can unhide it. To do so, click on the menu, then find the sheet you want (you may need to scroll down). Hidden sheets are greyed out. Click on the sheet name and it will appear in the tabs, in the same position it was before you hid it. Sheets also, automatically opens that sheet. Here the sheet “currency” is hidden. Clicking on it unhides it.

Sheets8 - 18 (1)

Sheets8 - 20  Sheets8 - 19

It’s also useful if you have a lot of sheets, then you can navigate quickly to the one you want.


So far we’ve looked at how we control the sheets, now let’s look at how you can connect data from one sheet to another.

Adding references to other sheets

Using an example to show how it works, I’ve got the exam results of two classes (A & B) and I want to compile both sets of data on a separate sheet. On sheet “ClassA” I fill in their exam results in the table, I then do the same on sheet “ClassB”.

Sheets8 - 21  Sheets8 - 22

On sheet “Year10” I want both classes together, as maybe this is what I need to present to the head of that year, so obviously he/she will want to see all the data together as a summary. So, I set up a blank table ready to collect the results from the other sheets.

Sheets8 - 23

In cell B3 on the Year10 sheet, I type the following formula:

=ClassA!A3

This tells Sheets to look at the page called ClassA and then the cell A3, and put the contents in the cell I’m in. The exclamation mark after the sheet name is what tells Sheets that this is the sheet name.

Sheets8 - 24  Sheets8 - 25

As you can see it’s put the name Fred which is in cell A3 on the ClassA sheet. I could type in formulas for all of them, but that would be a slow way to do it. So, as I know that there are 3 other students below Fred, I just click on the cell B3, and copy-drag (clicking and dragging the little blue square) down to cell B6. This then automatically updates the formulas so that it picks up the correct reference on sheet ClassA.

Sheets8 - 32    Sheets8 - 26    Sheets8 - 37

Now, I just need the exam results. I know they are in the cell to the right of the names, so I select all the names and copy-drag them to the cells to the right. Now all the results are there.

Sheets8 - 27

I repeat the same for the second class. This time in cell B7 I type:

=ClassB!A3

Sheets8 - 28 Sheets8 - 29

Then I repeat the same process as above to copy this formula, so that I get the other names and exam results.

Sheets8 - 33  Sheets8 - 30   Sheets8 - 31

Here are the formulas that the above produced in each cell.

Sheets8 - 34


Of course, I could just copy the results from each class and paste it in the new sheet, but I want is to create a connection between the sheets, so that if I change anything on the first two sheets, it automatically updates the third sheet. This allows me to use the sheet for future uses, without having to input all the data in twice.

So, as an example, let’s change the results of class A. On sheet Class A, I enter the new results and when I go to sheet Year10, I can see they have automatically updated.

  Sheets8 - 35  Sheets8 - 36

This is obviously a simple example, but the main way I use multiple sheets and references, is to organise my data better. Often, I have a sheet for each set of data and then a separate sheet, which pulls in certain bits to create some kind of summary on one page. It allows me to show a certain group of people some information (e.g. class A) without showing them the other (class B).


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

Baz Roberts (Flipboard / Twitter / Google+)