15 Functions in Google Sheets You NEED to know!

hi my name is city and welcome back to the channel where we make educational technology easy for you in today's video I'm going to show you 15 Google sheets functions everyone should know about so let's dive into it with another flipped classroom tutorial I'm going to leave a list of all 15 functions in that description below with time stamps so by all means use that to go back over some of the functions discussed now this is my personal top 15 and I do scroll down into the comment section and let me know what your favorite functions in Google sheets are have I missed any that you are using on a daily basis and if so which ones please let us know and I will be making a follow-up video now let's go to the first one and that's some now we all like to add different values together and some is going to allow us to do that now in order for us to use some it's very simple we're going to first press the equal symbol and that's what we're going through with every single function in Google sheets and this will bring up an autocomplete window now what we're looking for is s um some and then you will select the first sum now what's great about Google sheets is that it opens up a little dialog box that gives you more information about which functions and how to use these functions now some is one of the easiest ones to use you simply highlight all the values you'd like to see added together you close your brackets and then you simply tap enter this is going to add all your values together which brings us neatly to the number two function and that is sum if this is going to do the same thing as sum but it's going to have a criteria so it's going to check for a criteria and only add numbers together that fit that criteria so let's go ahead and demonstrate this now we're going to press equals select sum if and if you can't find it simply start typing it and it'll pop up so the first thing we're going to do is we're going to select our range so these are the values we would like to see added together and then we're going to add a comma now this comma allows us to go into the second part and this is the criterion this is what is going to be used to check if we're going to add it together or not and what we can do now is we can add a range of different things what I'm going to select is the greater than so let's go ahead and open up our quotation marks we're going to add the great sir than ten and then close that we can close the brackets and enter now only the values greater than ten are going to be summed or added together so this is some if this is great for when you've got results or test scores or you'd like to see some different groups added together now the third function in my top 15 list is a three-part function because there are three different functions and they all do a similar thing now what it does is it takes strings or text information and it's going to merge it together or bring it all together so for example if we have a name and a sentence we can then put that name into the sentence now you can either use concatenates join or text join now let's start we concatenate now we're going to press equals and then concatenate and what we're doing now is selecting these strings we'd like to see join so go ahead and select the cell that has the first string and then press coma now you're going to select a second string but do be aware that if you are selecting another cell and there are no spaces in that cell these two strings will be merged together into a single word with no space in between if you do want to have a space you'll actually have to add in a blank space now the way you can do that is by simply opening up quotation marks and then pressing space and closing those quotation marks you know comma again and this is where you select your second string as you can see in our function we now have three strings we have our first cell a blank space and then a third which is our second cell these are going to be merged together and let's just have a look at the results we're going to close our brackets and then press Enter as you can see concatenate takes these strings and they will be merged together now the same thing can be done with join so let's go ahead and open up a joint function we're going to press equals join now the difference in this function is that the first thing you have to type is the actual character that you'd like to use in between words so we're going to use a space so go ahead and open up your quotation marks space close quotation marks now you go into comma and now we're going to select the range of strings that we'd like to see merged now it is going to use the character mentioned at the beginning of our and is going to put that in between every single other cell so let's have a look at the results we're going to close our brackets and press Enter all my cells have been merged into a single sentence and in between the words we've used that character so let's just quickly change the character and see what happens we're going to add a dash in between the words and by simply changing our function you can now see that my sentence looks very different this is how join works and then the text join again starts off with that character you'd like to use in between the words but there is a second statement that you need to do and this is a boolean statement it's a true or false and the true or false is asking you what do you want to do with empty cells do you want to skip them or not so we're going to go with true we're going to skip empty cells and then the next one after our coma is going to be our range so let's go ahead and select a range close the brackets and let's see what happens now again we have the same results with that difference that empty cells will be skipped and this was my number 3 and that brings us to the fourth function and that's counts now count is going to count up how many numeric values can be found in a range so let's say that you have a number of scores you can use the count to find out exactly how many scores have been entered this is great when you're checking to make sure that everything is in order that everyone has replied for a quick way of quickly counting how many values have been submitted some account works by simply typing equals counts open up your brackets and then select the range we're going to close the brackets and press Enter as you can see in this column I have a number of values however in the other column I have no values there are no numeric values only texts and strings so it returns a zero which brings us to the next one and that's count a has in count all so let's see what it does when I use a count a the count a takes in all values not just numeric values it is actually going to check the entire range and if there is a text in your cell that's going to count as one value if there's a number that's another value and it adds it up that so counts for numbers count a for all values which brings us to the next count and this is by number six and number six is count if now count if just like some if it's going to count up the values but only if they meet a criteria so we're first going to add our range comma and then we add the criteria so let's go ahead with the greater than ten and then we're going to close our brackets and enter so here we have the total count for a certain criteria this is great to evaluate how well students have understood a certain concept or when you're using tests and you're grading scores this allows you to see who has achieved a certain minimum on these tests and talking about scores this brings us neatly to the seventh function and this is average now average is going to give us the average score when a range of numeric values has been selected so let's say that you have a number of test results and you'd like to find out what the class averages simply type equals average open up your brackets select your range by clicking dragging and dropping and then we're going to close the brackets and this gives us an average score now working with dates and time is not always as easy as you might expect it to be in Google sheets and this brings us to the next function working with dates and especially the 2-day function so let's just go ahead and type in equals today close our brackets and enter what this does is it automatically gives you an updated 2-day dates that you can then use in your spreadsheets this is great when you're trying to find out how long ago something has taken place because then you can use this today function to subtract it from a previous date this way you can find out the difference between today and let's say a test that took place a while ago now what is important to note is when you are working with dates please do use the functions so after today's function we're going to move on to the date function so let's say that you're talking about the first of January 2019 well let's use that function in order for us to put that date in there going to press equals then date open up our bracket the first thing it's asking is year comma month comma day now why do we have to use a date function well this is because this way Google sheets knows 100% of the time that you are talking about a date it also knows what the month is and you can never get the format wrong it also allows you to now have interactions between these two cells so we have a two day and a date we can now subtract these from each other and find out how many days difference between these two dates let's have a look at that right now we're going to press equals and I'm going to type date 2019 first the first that I'm going to subtract us from today and close that now because it is set as a function the later in time we open up the spreadsheet the higher the number or the difference in days will be which brings us to the tenth function and this is the V lookup or vertical lookup now the way vertical lookup works is you simply start by typing equals V lookup and then open up the brackets the first part of your function is going to be once you're searching for so in my example I'm going to search for the name Mike I'm going to put this in between I'm going to put this in between speech marks because it is a string then I'm going to press comma next you're going to select the range so where would you like to look now remember vlookup looks in the vertical first column so if we're going to find Mike we need to make sure that Mike is in there and we're going to select our range next it is asking for an index now this index is going to tell us which value do you want me to return and this is going to be the following columns so you need to look at it as the first column being the index one and then as we count we have index two three four I would like to see the results of the second column returned to me so I'm going to press number two then I'm going to close the brackets and simply enter as you can see the current value is returned to me and I can instantly look up a specific value great for when you're dealing with prizes again results from tests or summative assessments this is very very helpful especially when it comes to interpreting all that data that you've collected on your students which brings us to the eleventh function and this is a function that is often mentioned during boot camps and when you're preparing to take the educator examine is this sparkline many of us have never heard of it before until we've actually studied for the educator exam and the sparkline is going to give you a mini chart inside a cell so let's go ahead and type in equals sparkline and a simply select the range of values we'd like to see reflected in this sparkline I'm going to close our brackets and now again we get a mini chart linked to our cell and we can easily move it around number 12 my all-time favorite and I should have really put this on number one but google translate yes you can use google translate in your cells let's have a look at how this works we have a column here in one language and let's say that this is in English and I want to translate a cell automatically into Spanish no problem we can use google translate now the way Google Translate works is it's going to take a string from one cell translate it into a second language and then return that string to a cell now you can use this for as many languages as you like and you can even have it work automatically on multiple cells at the same time that I will leave a link in the cards at the top and this is where I'm actually using this and then linking it with autocrats to automatically generate flashcards that you can then use in multiple languages in your classrooms but let's look at the basic functionality of Google Translate we're going to press equals google translate the first parameter is going to be our original text so let's go ahead and select this cell with the original text so now that we have our text we're going to press comma and then the second parameter is going to be the source language and yes you've guessed it right we can use the language codes so let's go ahead and put en for English once you've done that another comma and this brings us to the target language now the target language is again using these language codes and for Spanish we're going to use es as in espanol our function is complete we can now close the brackets and see the magic happen Google Translate is working in the background and it automatically translates this word now again as I mentioned you can have this work automatically for rows and columns by simply dragging this little corner bit down or sideways and is then going to adjust the formula now before moving on to the 13th do scroll down and go to that comment section let me know which one has been your favorite so far which functions are you hoping to see next and have I missed anything let me know and then scroll back up to watch the rest of this video because this brings us to number 13 and this is the character function now using the character function we can pull in bullet points and these all come from Unicode so I'll leave a link in that description to the Unicode overview but for now let's just stick to the bullet points and this is the 8th two to six so we're going to put in character open up your brackets 8 2 to 6 and close them you'll see as soon as I press Enter what do we get we get a bullet point now if we want to have text behind that no problem we simply add to our function we're going to add a ampersand or an and symbol we can now open up our speech marks let's start off with a space and any word we would like to see bullet point its now we can close our speech marks let's go ahead and enter again and you will now see that your word is behind that bullet point in a single cell do note that you cannot interpret this as a string anymore because now it is part of a function in order for you to work with these words elsewhere in your documents I would recommend having them in one column and then having a bullet pointed list in another column and simply hiding the first column the way you would do that is by using character 8 2 2 6 close it ampersand open up speech marks space closed speech marks and % again and then selecting the cell that can Taine's the word once you press ENTER you will now get that word from that other column you're going to pull it in you're going to add a bullet point in front of it very useful in some cases not necessarily in the daily classroom use of Google sheets however what is very useful in the classroom is our fourteenth function everyone should know about and that is unique and what's unique does is it looks at your range and it's going to return all the unique values discarding any duplicates so let's go ahead and test it out we're going to press equals unique open up our brackets and select our range now as you can see I have a number of doubles here going to close the range enter and only the unique values are returned to me very useful indeed and that brings us to the final 115 functions you should know in Google sheets and this is number 15 random now randomizing is something that we love to do in classrooms we either pick around the name or we get a random number now the one I like to use is the random between by the way this works is the same as with all other functions start off with equals and then we're going to type in our a and D between within our brackets we give it two values the low and high value and then what does it return a random number between these two so let's say between 10 and 20 so the low number will be 10 comma 20 and we're going to press enter once we've done that we get a random value between 10 and 20 these numbers can be anything you'd like it to be and that is the end of this video I hope you found it helpful do scroll down to that comment section and let me know which function do you use most often and why also which functions have I missed out and are there any functions that you like to use in the classroom that I've completely missed I'd love to hear your feedback in that comment section don't forget to share this out and in the meantime we have other videos on the channel as well thank you for watching and I will see you in the next one

Channel: Flipped Classroom Tutorials

Views: 289,316

Rating: 4.9248981 out of 5

Keywords: top functions in google sheets, how to use functions in google sheets, google sheets tutorial, google sheets tutorial for beginners, how to use vlookup in google sheets, google sheets functions, google sheets functions sum, functions in google sheets for the classroom, using google sheets for budgeting, using google sheets in the elementary classroom, using google sheets in the classroom, 15 Functions in Google Sheets You NEED to know, google sheets, google sheets tips and tricks

Id: mRHlvRRERgs

Length: 17min 30sec (1050 seconds)

Published: Sun Jun 09 2019