Month: September 2016

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. 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. In the brackets we refer to cell A2 (Fred), add a comma, then refer to cell B2 (Flintstone). As you can see it’s added the two names together, the only problem is, is that there’s no space between the two. To remedy this, we need to add a space in between the cells A2 and B2 in the formula, like this:...

Read More

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. 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”...

Read More

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. 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. Or you can have the opposite, users update the individual sheets and the information is sent to the master sheet. 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. 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...

Read More

Archives

eBooks available

Pin It on Pinterest