hi everyone kevin here today i want to show you the top 20 formulas and functions in google sheets we’re going to start off pretty basic if you’ve never used google sheets before this will give you a good foundation to build upon as we go through formula by formula we’re going to get a little more complex by the end of this video if you run through all of these you’ll be pretty proficient in using formulas and functions in google sheets feel free to use the timestamps down below to jump around otherwise let’s jump on the pc and get started here i am in google sheets and formula number one that we’re looking at is addition i wasn’t kidding when i said that we would start out with the basics and then we’ll work our way up from there to enter a formula into google sheets we always start out by entering an equal sign i’m going to click in cell b5 b is the column 5 is the row and i’ll start by entering an equal sign this lets google sheets know that i want to enter a formula for addition it’s pretty simple you could simply enter the number hit the plus sign and then the other number that you want to add up here i see a tool tip telling me that it’s two also i could press the enter key and this will now sum up those values now of course the power from a spreadsheet comes from being able to refer to different cells within the spreadsheet versus using it like a calculator and entering in the numbers you want to add up for instance we sold 200 chocolate chip cookies and 150 snickerdoodle cookies what if i want to sum these up to know how many we sold in total well instead of typing in the individual values i could refer to the individual cells to do that once again let’s hit the equal sign and now instead of typing in the numbers i’m going to click on cell a3 i’ll enter the plus sign and then i’ll click on cell b3 so by doing that i’m now referring to these individual cells and in my formula you see that it says a3 plus b3 now just like before i’ll press the enter key and i could see that we sold 350 chocolate chip cookies and snickerdoodle cookies that’s a pretty good sales total next let’s take a look at number two subtraction how many more chocolate chip cookies did we sell than snickerdoodle cookies well this is a subtraction question and we want to take 200 and subtract 150 now just like before let’s enter the equal sign to let google sheets know that we’re entering a formula now once again instead of typing in the actual values i’m going to refer to the cell so i’ll click on a3 for the number of chocolate chip cookies sold and now enter the subtract symbol and i’ll click on the 150 snickerdoodle cookies so how many more chocolate chip cookies did we sell than snickerdoodles it turns out it was 50 more i guess chocolate chip cookies are more popular this brings us now to formula number three multiplication at the kevin cookie company we currently have a promotion going on where for every chocolate chip cookie we sell we donate two dollars to charity yes we’re doing some civic good at the kevin cookie company so i want to take our 200 cookies and multiply it by two dollars to see how much money we’re going to donate to charity that’s a multiplication question now once again let’s enter the equal sign and i’m going to refer up to the 200 chocolate chip cookies sold next i want to enter the multiplication symbol and it turns out that the multiplication symbol on a computer is an asterix so enter an asterix and we’re going to donate two dollars so i’m simply going to enter the number two now in this example we’re combining both references to cells and i’m simply entering a number it’s perfectly fine if you mix the two so let’s hit enter just like we did before and we see that we owe 400 to charity to follow through on our social good formula number four is division one thing we found at the kevin cookie company is that each employee is capable of making about a hundred cookies a day so how many employees do we need to make 200 cookies a day well that’s a division question once again just like we did before let’s enter the equal sign to let google sheets know that we’re entering a formula now we have 200 cookies so i’m going to click on cell a3 to refer to that cell next for division the division sign on a computer is a forward slash and i mentioned before that each employee can make about 100 cookies so we’re going to take our 200 cookies that sold divide by about 100 per employee and then hit enter so it turns out that we need two employees to be able to make these 200 cookies now that we’ve looked at addition subtraction multiplication and division let’s bring it all together i want to know how many employees we need to make all of these cookies first i’m going to add up the total number of cookies sold and then i want to divide it by 100 cookies per employee and that will tell us how many employees we need so once again let’s go down into the cell and let’s enter an equal sign to kick off our formula now first i want to add up all these values and i’m going to use parentheses so i could do all of the addition first i’m going to click through all the different cells and add up these values once i add up all the values i’m going to close the parentheses and next i want to divide by a hundred cookies per employee so i’m going to enter the forward slash for division and then enter in 100. i could see the answer up here or here i’ll click on enter and it tells me that we need about 6.25 employees to make all of these cookies that we’re selling now i probably won’t be able to hire .25 employees unless i get someone part-time but just to be safe and have some buffer i’ll probably hire seven employees to make all these cookies this brings us to our next two formulas min and max when you have a set of numbers you can very easily calculate what the min is and what the max is so which cookie sold the least well let’s use min once again let’s enter the equal sign and this time we’re going to type in min i’m going to open the parentheses and now i could select all of these values i could close the parentheses and when i hit enter google sheets will automatically identify which one sold the least here i could see the min was 50 and that’s our peanut butter cookie just like with min i can also enter max so now instead of entering min i’ll type in max open the parentheses highlight all of these values and close the parentheses and hit enter and here i could see that we sold the most of the chocolate chip cookie where we sold 200 cookies that’s a popular cookie and i could understand why they are delicious this brings us to formulas seven eight and nine these are different ways of calculating averages and just to refresh everyone we’re going to look at the mean the median and the mode to calculate the mean well this is truly the average where we take all of the different values i have seven values here we’re gonna add all of them up and then divide by seven and that’ll tell us the mean but luckily google has a formula that does this for us so within the cell let’s type in equals and then type out average i’m going to open the parentheses and highlight all seven of these values or this range of values and then i’m going to close the parentheses and hit enter so here i can see that on average for one of our cookie types we sell about 103.57 cookies next let’s look at the median and the median is the middle most number so here looking at all the numbers above my guess is it’s probably a hundred but let’s let google figure this out for us here let’s enter the equal sign and then type in median next let’s open the parentheses and just like we did with mean let’s highlight all of the values where we want to find the median and then close the parentheses when i hit enter here i see that the middle value is 100. lastly let’s look at how we could calculate the mode if you remember mode is the value that shows up most frequently or most commonly in your data set now just glancing at this you’ll see that most of these values are unique except we have 50 show up three times so that’s going to be our mode but let’s see how we could use google sheets to calculate this for us once again i enter the equal sign type in mode and here i’m going to highlight these seven values close the parentheses and hit enter and there we see the number that shows up the most often in this data set is 50. we’re now on formula number 10 and it’s getting a little bit more complex than where we started but don’t worry these are tools that you’ll be able to use to make your life so much easier now we sold all these different cookies across our new york city and our london locations i want to add up how many cookies we sold but i don’t want to click through and have to say well this cell plus this cell plus this cell because that’s going to take a while luckily we can use the sum formula function to calculate this for us first off just like we’ve been doing this whole time let’s enter the equal sign and now let’s type in sum let’s open the parentheses and i could simply highlight all of the values that i want to sum or all the values that i want to add up then i’m going to close the parentheses and hit enter and here i can see that across our new york city store and our london store we sold a total of 1528 cookies that’s a lot of cookies as another added bonus you can also highlight all of the values down below and in the bottom right hand corner you can see a quick sum down here along with some you can see the average the min the max the count and other helpful values if you don’t want to type in a formula so just an added bonus formula number 11. what if i just want to sum up specific values for instance what if i just want to know how many chocolate chip cookies did we sell now i could look at the list and say well here’s a chocolate chip cookie here’s a chocolate chip cookie and i could add them up it’s 4 15. but imagine you had a massive list and you can’t just eyeball it that’s where some if comes in basically what it’s doing is if it finds a chocolate chip cookie it’ll sum up or add up all of those values so how do we use it well once again let’s enter the equal sign and this time we’re going to type in some if then let’s open the parentheses here my range that i’m going to be evaluating against is the name of the cookie i want to look against this list and see where instances of chocolate chip show up so i’m going to select that as a range now i’m going to enter in a comma and for my criterion i want it to be chocolate chip i’m going to insert a quote and i’m going to type in chocolate chip once i type in chocolate chip i’m going to close the quote and now i’ll insert a comma again now this third value says well what is the sum range basically what that means is when it finds chocolate chip what value should it add up and i wanted to use this column with the number sold now i’m all done with my formula so i can close the parentheses and hit enter this tells me now that we sold 415 chocolate chip cookies that’s our most popular cookie so it’s understandable that it sold so much formula number 12 i want to count how many cookies had any sales at all now when i look at this table here i see that five different types of cookies had some sales two of the cookies had no sales i guess graham cracker cookies and whoopie pie cookies aren’t that popular at our nyc location i can use the count formula to help me with this just like we’ve been doing all along let’s insert the equal symbol and then let’s type in count next let’s open the parentheses and i’m going to highlight all of these values and then i’m going to close the parentheses now with the count function all it’s doing is it’s looking for cells that have some numeric value in it now you see that only five have a numeric value and two of them have a text value when i hit enter it’s only going to count the numeric values so it tells me that five cookies had some sales associated with them now let’s say i just want to know how many different types of cookies were for sale whether or not they had sales that’s where we could use the count a formula here when we look at this well we had seven different types of cookies for sale so let’s count this up so once again let’s enter the equal sign type in count a open the parentheses and we could simply highlight all of these cells once again it’s going to calculate any cell that has a value in it when i hit enter we were selling seven different cookies whether or not they had any sales lastly let’s use countif to count just the cookies that had at least 130 sales so basically what countif does is you could set some type of criteria in this case it had sales over 130. so here once again let’s insert the equal sign and now we’ll type in count if next let’s open the parentheses and here again i see a helpful hint telling me how i can use this formula now once again i’m going to insert my range and this is my range of cells next we’ll insert a comma and i need to type in my criterion and once again i want to know cookies that sold over 130 i’m going to insert quotes and i want to type in greater than 130 and then i’ll close the quotes and close the parentheses and hit enter so only two of our cookies sold over 130 and that’s the chocolate chip and the snickerdoodle our two most popular cookies formula number 15 and this is a long word concatenate so what does concatenate mean well you can take two values in different cells and then you can bring them together into one cell and you’re not just limited to two cells you could bring together any number of items for instance right now cookie is in cell a3 and monster is in cell b3 i’d like to bring them together so once again let’s enter the equal sign and then type in concatenate once we enter it in we can open the parentheses and i want to add cookie and then i’ll insert a comma and i want to combine monster now i could close the parentheses and hit enter and you see that it pulled together cookie monster unfortunately though there’s no space between cookie monster and i would like there to be a space well once again like i was saying concatenate can bring together any number of items so here i want to put a space between a3 and b3 so let’s simply enter quotes add a space close the quotes and then i’ll insert another comma the way to read this is we’re combining cell a3 then we’re combining a space and then we’re combining cell b3 now that i’m all done let’s hit enter and yes look at that cookie monster has a space between him formula number 16 is the if function and this allows us to run logic against our sheet now at the kevin cookie company we recently got our customer favorability ratings back and as you can see and as expected most customers love our cookies and they rate them pretty highly but it looks like we have a few trouble spots here in the us they don’t seem to like our fortune cookies and in china they really dislike our fortune cookies now i want to use an if function to evaluate if we need to investigate any of these cookies to see if there are any quality issues so once again like we’ve been doing throughout enter the equal sign and let’s type in if next let’s open our parentheses once again you see helpful information about how you can use the formula now it starts off by saying enter a logical expression this is basically the test that we want to run i want to see if let’s say 94 is greater than 50 if the favorability rating is under fifty percent we should probably investigate so i’ll select ninety four percent and then i’ll say is it greater than let’s say fifty percent fifty percent is point five next i’ll insert a comma and if it’s true if it is greater i’ll simply say all good because we don’t need to take any action next i’ll enter a comma and if it’s not true what do we want to do well we need to investigate so i’ll insert another quote and here i’ll say investigate and let’s throw an exclamation mark in there just to give it some urgency then i’ll close the quotes and close the parentheses and there’s my if formula when i hit enter now i see that our chocolate cookies in the united states are all good now if i want to pull this formula and apply it everywhere else i could simply click on the corner here and drag it all the way down this will apply the formula to each individual row here now i see that for the fortune cookies in the united states we need to investigate that that’s a very low rating and here in china maybe we’ll just discontinue the cookie formula number 17 is vlookup and that stands for vertical lookup i have a table here with countries and their associated sales revenue cost and profit and i want to list the store manager next to each one of these rows now each country has its own store manager down in this table below we see that in the united states heath chips is our store manager and in china we have oreo baker both fantastic managers now i want to insert the value in this table now i could go through and i could say well china that’s oreo i could copy this paste it up here but that’s a lot of work especially considering this table is fairly large this is where vlookup can help us like we’ve been doing this entire time let’s insert an equal sign and for the formula let’s type in vlookup next let’s open the parentheses and first it’s going to say what are you searching for well we’re searching for china because if it’s china well there’s a specific manager in china so this is our search key or the lookup value so i’m going to select that cell next let’s enter a comma next it’s going to ask me what is the range that i’m looking for this value in basically i’m looking for china where well i’m looking for china in this table so i’m going to highlight this table down below now right now this is a relative reference what does that mean well if i take this formula and i copy it down the table will also shift down i want to make this an absolute reference what that means is when i select this table as i copy the formula down it’ll continue to look just at this table and it won’t adjust the position of this table to make it an absolute range i’m going to click on the f4 key and you see that it inserts dollar signs in front of the letters and the numbers basically that’s fixing it to just look at this table next let’s enter another comma and here it says the index the index is what value i want to get back what that means is it’ll look for china in this table when it finds china which column does it send back does it send back the first column or the second column well i want to get the manager back and that’s the second column so the index is 2. i’ll now enter another comma and here the last piece of the formula is is sorted my list is not sorted because china shows up last and c shows up in the alphabet before you so it’s not a sorted list i’m going to enter false up here but if it was a sorted list i could type in true now i’m going to close the parentheses and hit enter and look oreo baker is our manager in china i’m going to drag this formula all the way down and it automatically fills in who the manager is based on this table down below and once again because i used an absolute reference up here it only looks at this table function number 18 is convert this is a handy one if you need to convert between different units of measurement for example we recommend baking all of our cookies at 375 degrees now for our european locations they’ve been asking me well what is that in celsius fahrenheit doesn’t make any sense to us well i can use the convert function to help with this i’m going to enter an equal sign and then type in convert now i can open parentheses and i want to convert 375 to celsius so for the value i’m going to select 375. next i’ll insert a comma and it wants to know what is the starting unit now over here on the right hand side it shows all the different units that you can convert between so you could convert in weight and distance in time in this specific example this is temperature and here i see what i can convert between so here i can convert between fahrenheit which is the f and celsius which is the c i’m going to insert a quote and enter f this is my starting unit next i’ll enter a comma and then for my end unit i’m going to insert a quote a c because i want to convert into celsius and then close the quotes and then close the parentheses and then hit enter turns out that the ovens in europe have to be running at 191 celsius and that’ll ensure that our cookies bake at the proper temperature number 19 we’re going to look at the today function if you’ve seen any of my youtube videos you realize that i always start off by saying today so what does today actually mean well i think google sheets might know let’s insert an equal sign type in today and then open and close a parenthesis and hit enter and this tells us what today is today is october 28th 2020. now as an added bonus what if you also want to know the time well once again let’s enter an equal sign type in now open and close your parentheses and hit enter and here we have not only the date of today but we also have the time number 20 and this is the last one of today at the kevin cookie company we’re looking at running a social media advertising campaign but we’re limited to 20 characters we came up with all of these different taglines but how do we know how many characters are in each tag line well we can use the len function to use this enter an equal sign and then type in len next let’s open the parentheses and click on this row next we’ll close the parentheses and here it tells me that it’s 28 characters long that’s too long for our campaign so unfortunately we won’t be able to use deliciousness in every bite i kind of like that one but i guess we’ll have to pass on it next i can copy the formula all the way down and here i see that we have three tag lines that fall under the 20 character limit my love my cookie order eat repeat and love it first bite i think i prefer love at first bite i think we’ll use that for our social media campaign all right congratulations you made it through the top 20 formulas and functions in google sheets by knowing those you are now pretty proficient with using google sheets and in fact these same formulas and functions work in most other spreadsheet applications like microsoft excel or wps office so you’re well on your way to mastering spreadsheets if you enjoyed this video please give it a thumbs up if you want to see more videos like this in the future hit that subscribe button that way you’ll get a notification anytime new content like this comes out and lastly if you want to see me cover any other topics in the future leave a note down below and i’ll add it to my list of videos to create alright well that’s all i had for you today i hope you enjoyed and i hope to see you next time bye you