Google Sheets Functions – HYPERLINK

Here let’s take a quick look at the HYPERLINK function, which allows you to add hyperlinks with ease and to also rename them.

Here I’ve just copied and pasted my blog address into the cell.

functions18-1

This is fine, but sometimes we don’t want an ugly URL in our sheet. For example, I can change what shows by using the HYPERLINK function.

functions18-3

Here I add the URL between speech marks, then after the comma add the text I want visible on the sheet, again in speech marks.

Now in cell A3, I have the text I want.

functions18-2

When you hover over the text, you can see that it’s linked to a URL and clicking on the blue link, will take you to the webpage.

functions18-4

Extra tip: Links to documents on your Drive could also be done in this way. Just make sure the person using your sheet, has access to the document.


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – ROUND, ROUNDUP, ROUNDDOWN

In this post we’re going to have a quick look at rounding numbers up and down, by using the ROUND, ROUNDUP, and ROUNDDOWN functions.

The syntax is very easy, we tell the function the number we want to round and then to how many decimal places.

In cell A1 we have a number and in B2 we want to reduce it to just 2 decimal places. In cell B2 we write =ROUND(A1,2), this gets the number in cell A1 and then converts it to 2 decimal places.

functions16-1a

In the examples below we can see how the same formula treats numbers that are 0.5 or higher or less than 0.5. If the number is half or more it will round the number up, so 3.56 becomes 3.6, if we’re working to 1 decimal place. If the number is less than half, it will round it down, so 3.14 becomes 3.1.

functions16-1b

We can control whether we want it to round up or down. To always round it up, we use the ROUNDUP function in the same way as before. As you can see, it’s rounded both figures up this time.

functions16-2

To always round down, we use the ROUNDDOWN function in the same way:

functions16-3

If you want to round whole numbers up, for example, to the nearest thousand, use a negative number. Here I’m rounding to the nearest thousand, by adding a -3.

functions16-4


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – IMAGE

In this post let’s look how we can insert images into our sheets. There are two main ways, either inserting the image via the Insert menu or by using the IMAGE function.


Example 1 – Inserting an image from Drive

Here let’s add an image from my Drive. Open the “Insert” menu then click “Image”.

functions17-10

Choose where your image is, in this case, let’s choose “Google Drive”.

functions17-13

Search for your image, and click on the one you want, then click Select.

functions17-12

This will place the image on top of your sheet and won’t affect the cells in any way. You can change the size of it by moving the blue squares on the border of your image.

functions17-11


Example 2 – Inserting an image within a cell using the IMAGE function

An alternative way to inserting images, is to use the IMAGE function, which will insert the image within the cell where that function is. To do this we need the URL of the image.

In the cell I type =IMAGE() then in between the brackets I add speech marks and the URL inside them, i.e. =IMAGE(“www.google.com”)

Here I’ll add an image from my blog:

functions-17-1

This adds the image within the cell. A bit small isn’t it?

functions-17-2

This is because it has adjusted the size of the image to the size of cell. To make it bigger we just make the row and/or column sizes bigger.

functions-17-3

By default, the image is inserted in “sizing mode 1”. So, what does that mean? Well, there are 4 modes and they treat the images in different ways.

Mode 1 – Resizes the image to fit inside the cell, maintaining aspect ratio.

Mode 2 – Stretches or compresses the image to fit inside the cell, ignoring aspect ratio.

Mode 3 – Leaves the image at original size, which may cause cropping.

Mode 4 – Allows the specification of a custom size.

So, let’s look at the modes 2, 3, and 4 in turn.

Mode 2

Taking the same image and formula, but this time just adding a comma and 2 at the end, will squash the image into the cell, and ignore the original aspect ratio, so it now looks too wide.

functions-17-4

functions-17-5

Mode 3

This time replacing the 2 with a 3, will insert the image as its original size, but if it is bigger than the cell, it will be cropped, as we can see below.

functions-17-6

functions-17-7

Mode 4

Finally, we can control the height and width we want, but to do this we must use mode 4.

As you can see in the formula, after the 4, we add the height (150) and then the width (120).

functions-17-8

functions-17-9

Which mode you use is of course entirely dependent on what you want to achieve.

It’s important to note that as these images are within the cells, they are affected by any cell changes, rows or columns added, etc.


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

Baz Roberts (Flipboard / Twitter / Google+)