“My name is city and welcome back to the channel. Where we make educational technology technology easy for you in today s video. I m going to show you 15 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. ” ..
Thank you for watching all the articles on the topic 15 Functions in Google Sheets You NEED to know! . All shares of thetruthaboutdow.org are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
“Google SHEETS is incredibly powerful. Are you using these functions to save time, get work done and be more productive? Here are 15 functions in Google Sheets you need to know!nnTimestamps:n0:00 Intron0:25 Why these functions?n0:41 SUM() n1:08 The info window of Functionsn1:28 SUMIF() Set conditions for your functionn2:35 Joining strings or text together using these three functions.nn2:55 CONCATENATE()n3:55 JOIN()n4:54 TEXTJOIN()nn5:30 COUNT() Count how many numeric values there are in the selected rangen6:14 COUNTA() Count the number of values (All values)n6:47 COUNTIF() Set conditions before counting the number of valuesn7:21 AVERAGE() Whats the average of the selected range?nn7:52 TODAY() Whats the date today? Interactive and updated automatically.n8:35 DATE() Use a date using a set format.n9:23 Find out the difference in days between today and a set date in the past or future.nn9:55 VLOOKUP() Use this to find specific information from a larger dataset.n11:20 SPARKLINE() The number one function needed for a quick visual chart within a cell.n11:58 GOOGLETRANSLATE() Translate any language into another, automatically!n12:37 GoogleTranslate to automatically create flashcards in Google Slides (Link: https://youtu.be/4J8WJDTjA9Y)nn14:04 CHAR() Use a Unicode Character incells. This is great when creating Bullet points and lists. List of all characters: http://www.unicode.org/charts/nn15:46 UNIQUE() Get an overview of all unique resultsn16:15 RANDBETWEEN() Get a random number to use in classnnLet me know in the comment section which of these is your favorite and when you use it the most? How are you implementing the use of Google Sheets in the classroom and what are tips and tricks when using sheets? Let me know!nn Don t forget to SUBSCRIBE today for more videos! nhttp://bit.ly/FlippedYTnnShare this video with a friend: https://youtu.be/mRHlvRRERgsnn AFFILIATE LINKS: n(These really help make our content better at No extra cost to you)nMy videos are made with Techsmith Camtasia.nGet this amazing software here: http://tools.eduflip.net/Techsmithnn CHECK out MY PRE-SELECTED list of my ALL-TIME Favourite EDTECH TOOLS:nhttps://www.amazon.com/shop/flippedclassroomtutorialsnnDisclaimer: Some of these links are affiliate links where I ll earn a small commission if you make a purchase at no additional cost to you. Some links will link out to one of my websites. These videos have been made for educational purposes and all views or opinions expressed are my own.nn Find me on SOCIAL MEDIA:nMy Official EduFlip Website: http://eduflip.netnFollow me on Twitter: https://twitter.com/sdcthailandnFind me on Facebook: http://bit.ly/FlippedFacebooknn get CLASSROOM RESOURCES today:nMy TPT Store: https://www.teacherspayteachers.com/Store/Eduflip”,
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 go…