Google Sheets Functions – CONCATENATE

Concatenation. What on earth does that mean? Put simply, this is putting different pieces together to create a whole. We can have values in different cells and use the CONCATENATE function to join them together to create one combined piece. Here I’ll go through some examples of how to use this function, building up from the very basics to more sophisticated ways to use it, combing it with other functions. So, let’s dive straight in.


Example 1 – Joining two names together

In the first example, we have some names which from our system have been stored in separate cells. One column for their first name and one for their surname. What we want to do is combine the two to create their full name and store it in column C.

functions3-33

In cell C2, we write a CONCATENATE function to join the contents of column A and B.

As we write the function, we can see that the function needs some strings (text, numbers, etc)  to put together.

functions3-1

In the brackets we refer to cell A2 (Fred), add a comma, then refer to cell B2 (Flintstone).

functions3-2

As you can see it’s added the two names together, the only problem is, is that there’s no space between the two.

functions3-3

To remedy this, we need to add a space in between the cells A2 and B2 in the formula, like this:

functions3-4

Now we have the words with a space in the middle.

functions3-5

Once we’ve written the formula for one cell, we can drag it down to the rows below to fill the table.

functions3-6

functions3-7


Example 2 – Making website URLS from information in different cells

We can use the same principle to create individual URLs. Here we have a table where we have the main website address and the various pages on it. On the right we’re going to join them together.

functions3-22

This time instead of add the individual cell references, we can write a range in between the brackets:

functions3-23

This create the specific page links which we can now share and use.

functions3-24


Example 3 – Combining a range of cell values to make a complete part number

We can continue the idea of using a range of values to create, for example, here a part number. The part number is made up of a location, parent type, subset and the part and are combined to create an individual part number.

functions3-19

If we just want to combine all the numbers together, we can just refer to the range where the values are:

functions3-20functions3-21

As the number is long and a little difficult to read, we may want to add some dashes to identify the individual parts of the number. Like the spaces we added above, we just add the dashes in between the cell references:

functions3-31functions3-32


 Example 4 – Concatenating formulas

Here we want to show on a website the quantity of each product we have, and also the total of all the things we have. In cell C12, we write the following formula:

functions3-12

First, we are summing the contents of column A (the quantities), we add a space, then add the word “Things”.

functions3-11


Example 5 – Making sentences and adding dates in the current format

Here we’d like to show today’s date as part of a complete sentence.

functions3-16   functions3-17

To do this, we first write the text we want (the fixed part of the sentence), in this example, “Today’s date is ” (notice the space at the end). Then we need to get the date which is in cell E7. If we just write E7, then the date won’t appear in a date format, rather it will appear as a number, like this:

functions3-34

So, we need to adjust the formula, so that it gets the text in E7 and puts it into a date format:

functions3-18

functions3-17


Example 6 – Creating addresses on different lines

Here we want to create some addresses but we want each cell to appear on its own line, ready to be printed. These are UK style addresses and we have the street number and name, the town, county and then the post code.

functions3-25

To add a line break after each line of the address, we need to use the character number 10-char (10). We add a cell reference, then add the char(10) after each one.

functions3-26

This puts the full addresses in column O, with each part of the address on a different line.

functions3-27


Further notes

CONCAT v CONCATENATE

If you’ve typed out a CONCATENATE function, you may have noticed that there’s another one called CONCAT. This works in a similar way…so what’s the difference? Basically, in Sheets CONCAT is only used to combine two values, whereas there is no limit with CONCATENATE (within reason). Note, in Excel 2016, CONCAT now replaces CONCATENATE but CONCATENATE is still backwards compatible with earlier versions of Excel.

functions3-30


AND

We can also use the AND operator instead of the CONCATENATE function. In the first example, we had the first name in column A and the last name in column B.

We write the cell reference, then the ampersand symbol(&), add a space (” “), then another ampersand, then the second cell reference:

functions3-28

Exactly as before it joins the two names together with a space in the middle.

functions3-29

The limitation is that you can’t use ranges, like in examples 2 & 3 above, with the ampersand (&). Plus in longer formulas, you have to continually add the ampersand, whereas with the CONCATENATE function you only write it once. Finally, the ampersand is used in other ways and so using it, sometimes can make longer formulas more difficult to read and understand.


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

Baz Roberts (Flipboard / Twitter / Google+)


 

Google Sheets Functions – IF

Sometimes you’re not sure what’s going to be on your sheet and in your cells, and you want to show a value or piece of text, depending on what’s in those cells. This is where the IF function comes in. It’s like what we use in English, IF this happens, I’ll do this, IF NOT I’ll do something different. It’s one of the functions I use all the time, and I’m sure you will!


Example 1 – Has the student passed or failed?

Here I have 4 students who have done an exam and I want to show who has passed or failed. The pass mark is 60% and clearly with just 4 students this is easy to see, but imagine a sheet with for example, 100 students.

functions2-3

In cell C3, start off with by typing =IF(

Here the help box will appear. In the first line it’s showing you what it needs you to fill out. There are 3 parts:

“logical expression” – This just means what are you trying to test for. It needs to be a true or false question. For example in this case, Is the exam mark more than or equal to 60%?

“value_if_true” – This is what you want to put in the cell, if the answer to your question is TRUE, i.e. the exam mark IS more than or equal to 60%.

“value_if_false” – This is what you want to put in the cell, if the answer to your question is FALSE, i.e. the exam mark IS NOT more than or equal to 60% (in other words, less than).

functions2-2

So, to continue with our function, next we write the question we want answering, the logical expression. So, we want to know if the exam mark in cell B3 is more than 60%. First, we add the cell reference B3.

functions2-4

Then we add the more than symbol and the equals sign, which together mean ‘more than or equal to’, then we add the number, in this case 0.6 (note, this is not 60, as 60% = 0.6).

functions2-5

Then we need to tell it, what to do if the above question is TRUE or FALSE. I want it to add the word “PASS” if it’s true and “FAIL” if it’s not. Add a comma, then in double quotes write PASS and FAIL as below. Then close the bracket. So your function should now look like this.

functions2-6

Press Enter and as you can see in cell C3, the word PASS has been displayed, as John has indeed got more than 60% in his exam.

functions2-7

Click on cell C3 and drag the little blue square down to copy the function down to the other cells. Straight away we can see George and Paul haven’t passed the exam.

functions2-8


Example 2 – Referring to a cell

What happens if the teacher believes the exam was too hard and the pass mark should now be 50%? In the example above, we would have to go back into the function and change the 0.6 to 0.5. Possible but more work for us. We can avoid that by referring to a cell where the pass mark has been written in.

Here the pass mark is in cell B15.

functions2-9

Here’s the original formula:

functions2-11

Let’s change the 0.6 to the cell reference, B15. Note I’ve had to put the dollar signs in, as this makes it an absolute reference, i.e. it will always refer to that cell, even when I copy the formula down the rows to the other students. See my post on relative and absolute references.

functions2-12

I copy it down the rows as before and it changes George’s result to a PASS.

functions2-13

Now, by changing the pass mark in cell B15, this will automatically update the Pass/Fail results in the table.

functions2-14

So, certainly use references if you may change the formula values in the future.


Example 3 – Dealing with blank spaces and making your data look prettier

With the examples so far, before adding the exam marks, the table would look like the one below, i.e. in the pass/fail column it’s stating that they have failed because the Exam mark cells are blank.

functions2-15

To get round this, there is a really good use of the IF function, which only shows the content in the cells, if another cell has something in it.

We need to add a check at the start of our formula, so see if the cell has an exam mark in it. If it’s blank then we want it to make the pass/fail column blank.

The start of the formula is as below. It looks in cell B19 and if it IS blank (two double quotes with nothing in between means nothing), then we add nothing (again two double quotes).

functions2-17

So this is if the check is TRUE, now we end with the original formula, as this will run if the cell ISN’T blank (i.e. the false result). Then we end it all with a second closed bracket, as follows:

functions2-18

So, just to summarise that, the first part checks to see if B19 is empty, if it is it makes C19 empty. If it isn’t it checks to see what has been added in B19 is more than 60%, if it is it writes PASS, if not it writes FAIL in C19.

Here’s what the table looks like now. Nice and tidy waiting for results to be added.

functions2-19

As soon as we do, the function starts working out if the exam mark is pass or fail.

functions2-20


Example 4 – Nested IF functions and multiple options

In the example above, we used two IF functions together. This is called nesting and this can be extremely powerful and allow you to check the multiple situations, not just the basic is one thing true or false. The above example provided us with 3 possible outcomes:

1) Cell B19 was blank -> Make C19 blank

2) Cell B19 wasn’t blank -> Was C19 more than or equal to 60%? -> Yes, so write “PASS”

3) Cell B19 wasn’t blank -> Was C19 more than or equal to 60%? -> No, so write “FAIL”

In this example, let’s look at a company where they offer different discount rates to their customers, depending on the quantity they buy. Here’s the table summarising the discounts:

functions2-22

So, up to 99 products you get a 2% discount, up to 499 you get a 5% discount, and so on.

So, we want to create a formula which will check if they have bought 1000 or more, if so, apply the 10% discount, if not check if they have bought 500 or more, if so, apply the 7 % discount, etc.

First, we check for the largest quantity: Is the quantity in cell F11 more than or equal to 1000, if it is, then put the 10% discount from cell F6.

functions2-21

Second, we check for next discount. If the quantity in F11 is more than or equal to 500, if so, put the 7% discount from F5.

functions2-23

functions2-24

Note, that in the first test we checked if it was 1000 or more, then IF NOT, then check to see if is 500 or more.

We continue until we get to the last possibility, which is, is it 1 or more. If it isn’t, then it must be 0 (assuming no mistakes or typos), so we end the formula with a 0, to show we would offer a discount if they didn’t buy anything!

functions2-25

Even though they can look complicated and sometimes be quite long, a nested IF statement always follows the same pattern:

IF(check a condition, action if TRUE, if FALSE check a condition, action if TRUEif FALSE check a condition….until the last one…action if TRUE, action if everything is FALSE)

So, in our example, we have a customer, the quantity they bought, the price of the product. Then the discount which will be applied based on the quantity they bought, using the formula above, and finally the cost with the discount (quantity x price-discount).

Here Widgets Ltd, have a 7% discount as they bought more than 500 but less than 1000.

functions2-27

As before, we copy the formula down the rows and we can see the different discounts that have been applied immediately.

functions2-28


Example 5 – Making decisions based on words not just numbers

So far, we’ve looked at whether a figure is larger than another, but we can also set an IF function to do something if it matches a piece of text.

Here we have the same customers, but this time the company’s decided to offer certain customers an extra discount. In column J, they’ve written YES or NO to determine who gets the extra discount.

functions2-29

Here I want an IF statement that will add the extra discount to the total if it states “YES” in column F or to leave the total the same as it is in column I.

So, for the first customer, I write the following formula:

functions2-30

This checks to see if J19 is YES, and if it does, it gets the total and multiplies it by 0.95 (to work out a 5% discount), and if it doesn’t, then it just gets the original total from I19.

Below we can see in the green shaded  parts, that the extra discounts have been applied.

functions2-31

The IF function is very powerful and can automate so many things, just as long as you create a condition (or question) that can be answered TRUE or FALSE. It will then use just pure logic to give you the answer.

When checking for various values such as in example 4, the IF formulas can get quite long, in these cases it would be better to use something like VLOOKUP, which looks up the value in the table, but that’s in a future post. For now, enjoy trying out the IF function.


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Sheets Functions – IMPORTRANGE

Google Sheets has a wonderfully useful function called IMPORTRANGE. So what does it do? It allows you to connect different spreadsheets and import data from one to another. The most basic example would be to connect one sheet with another sheet and import some data from the first sheet to the second.

functions1-40

What’s important is that if you change the data in the first sheet, it’s automatically updates in the second sheet.

Other uses

Not only can you connect 2 spreadsheets, but you can connect multiple spreadsheets with a master sheet. This can be useful if, for example, you only want someone to see part of the data you have on the master sheet, so you share the individual sheet or range with them.

Either the master is updated and the information is sent out to the individual sheets.

functions1-42

Or you can have the opposite, users update the individual sheets and the information is sent to the master sheet.

functions1-41


How to use it

Let’s start with a simple example showing how you can connect different spreadsheets.

I have a sheet, linked to a questionnaire form, where the responses from our student questionnaires are stored.

functions1-43

The students are asked questions about the teacher and also about the administration of their course and the service we provided during sign up, etc. I want to share the information about the admin process with reception but I don’t want to show them the personal information about the teacher. So, what I can do is, create and share a sheet with reception and import the relevant information for them.

First I create the sheet and share it with reception.

Then in the shared file, I type the IMPORTRANGE function. As with all functions, it starts with the equals sign, then the function name. Then open the brackets.

At this point you will see the useful help box appear, which gives the content of the brackets it’s expecting, an example of the function filled out, a summary of what the function does, plus some extra information, for example, in this case, what is the “spreadsheet key”.

Note, if you click on “Learn more about IMPORTRANGE” this takes you to the Google help page for that function, which will give you more information on how the function is used. In here it states that you should use the full URL and not the spreadsheet key. However, in my experience both work.

functions1-1

As the help box states, you need to add the ‘spreadsheet key’. This is found in the URL of the file. It’s the mixture of numbers of letters after the /d/  and before /edit. This is telling the Google Sheet which file it is, as every file has a unique key.

functions1-3

Copy this and then back on the shared file, add speech marks then paste the key in and add speech marks again. It won’t work without the speech marks.

functions1-2

Now we need to tell it where the data we want is in that file. So in our function, we first add the sheet name then the range. The sheet in this case is called “Questionnaire”.

functions1-8

So, we start with a comma after the key we’ve just added, then speech marks again, then the name of the sheet followed by an exclamation mark, which tells Sheets this is a reference to a sheet.

functions1-5

Looking back at the original file, we can see we want the first 3 columns A to C and down to row 8. Columns D and E are about the course and the teacher.

functions1-4

Finally, we add the range we want, A1:C8 and end with speech marks. then press Enter.

functions1-5

The first time you try to link the sheet, the #REF! error will appear. Don’t worry, it’s just reminding you to do something. Hover your mouse over the cell, and you’ll see the box below appear asking you to “allow access”. This is a security measure and is always necessary the first time you share the file. Just click the blue button to link the files.

functions1-9

The contents of the range above will appear in the shared file. Note, that this only imports the content, i.e. the values of the cells, and not the formatting.

functions1-6

So, sometimes you’ll have to adjust the cells accordingly.

functions1-7

And that’s it! The two sheets are linked and if I make a change on the original sheet, it’ll update the shared one automatically.

Depending on the amount of information being imported, sometimes there is a slight delay in the second sheet updating. That’s normal.

I of course do the same for the teachers and share just the ranges that apply to them.


Open-ended ranges

In the example above, we are only sharing a fixed range (A1:G8), but what happens if we receive some more questionnaire feedback and have data that is in rows 9 and above? The range we used above won’t show us that data, so here we will need to use an open-ended range.

Let’s slightly change our function at the end. Change the range from A1:C8 to A:C. As we don’t state the row numbers, the range is in effect going from row 1 to infinity (well until the maximum number of rows).

functions1-9-1

Here we’ve received two more pieces of feedback in row 9 and 10 in the original file.

functions1-12

And automatically it appears on the shared admin file, without having to update the range. Note, I added the green colour just to highlight the new data added.

functions1-13


Multiple IMPORTRANGES on the same sheet

So far, we’ve seen one range from one spreadsheet being imported into another spreadsheet. We’re not limited to just one, if we wanted we could import hundreds on the same sheet.

Let’s look at another example where I have some details about two teachers on two different spreadsheets. They have access to their specific file and I want to combine this information in a master sheet, so the information for both teachers appears on one page.

First, we have the information in one file, on a page called Teacher 1.

functions1-15functions1-16

Second, we have the information about the other teacher in another file, on a page called Teacher 2.

functions1-17functions1-18

Each teacher only has access to their particular file to ensure privacy.

Now, I want to add them to a master sheet. In cell A1 I type in the first IMPORTRANGE function as below. So, exactly the same as above, I get the spreadsheet key of the first spreadsheet, then I refer to the ‘Teacher 1’ sheet and the range ‘A1:B5’.

functions1-19

As always I may need to tidy up the formatting, for example, here the column width and the alignment.

functions1-20

Now in cell C1, I add the second IMPORTRANGE function. I include the key from the second spreadsheet, refer to the page Teacher 2 and this time, I only need the column B.

functions1-21

I could continue adding teachers like this by adding another IMPORTRANGE function to D1, E1, etc.

functions1-22

If I’m expecting further information to be added, for example, qualifications to be updated, and therefore, extra rows added, I could of course use an open-ended range for each of the functions. For example, in the case of the first one the range would be A:B not A1:B5.

One important thing to note, is that you cannot write anything within the imported range. If you do, the data disappears and you get the #REF! error as you can see below. Here I typed the word “text” in cell C5 and that has caused the second teacher’s details to disappear. so, you cannot edit the imported information, only the original data can be edited.

functions1-23


Pre-formatting an imported range

In the above examples, we just had text and the imported range didn’t look that good, and we had to format the cells afterwards. Let’s look at how we can maintain the format of the original data in our new imported range.

Here I have the attendance for two classes (A & B) in two separate files and I want to add them into one file but with a tab for each class. This time the data is formatted in various ways, e.g. background colours, font size, bolding, conditional formatting, percentages, alignment. To reformat this for each imported class, would be a real pain. So, it’s far better to set up the destination file with the same formatting, so when the data arrives you don’t need to do anything.

functions1-29

functions1-36

Here we’re going to do two key things:

  1. Use “Copy to…” to copy one of the original sheets to the destination one.
  2. Use “duplicate” to create a copy of a blank formatted sheet.

First, from the Class A file, click on the sheet tab arrow and select “Copy to…”.

functions1-25

You then need to find and select the file you want to copy to. I often use click on “Recent” as usually the file I want has been worked on recently, so will appear near the top of the list.

functions1-26

Click on the file and click “Select”.

functions1-27

You’ll get a confirmation message and if you want to go to the destination file, click on “Open target workbook”.

functions1-28

Here it’s added a new tab called “Copy of ClassASheet”.

functions1-37

As you can see, it’s identical to the original sheet.

functions1-29

Now select all the cells and press delete.

functions1-30

This will leave you with a pre-formatted blank.

functions1-31

Now as we have another class we want to import and I want to put it on a different sheet, click the sheet tab arrow and this time select “Duplicate”. This will make a copy of the blank formatted sheet.

functions1-32

Now all we need to do is add our IMPORTRANGE functions. I add class A to the first sheet typing the function in cell A1 (although it could in fact be in any cell).

functions1-33

As you can see it formats it perfectly automatically.

functions1-34

Then I add class B.

functions1-35

functions1-36

So, now I have both class A and B in the same file, which saves me opening different files to see the information. It also allows me to analyse the different sets of data within the same file, for example, maybe I want to produce a report for the attendance of all students, this can easily be done within the same file.


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

Baz Roberts (Flipboard / Twitter / Google+)