How to use Formulas and Functions in Microsoft Excel

hi there jamie keith here today at teachers tech hope you're having a great day today today i want to show you how to build formulas and functions in microsoft excel so maybe you haven't used microsoft excel in a while or maybe you're brand new this video can get you up and running in no time in understanding functions and formulas so let's get started today on teachers deck i'm going to have nine different topics in this video tutorial here today and if you take a look at the description down below you can see everything is time stamped and you can click on the number and it will take you to that part of the video and also if you're looking to learn more about excel take a look at the playlist the link i'll put in the description that has lots of different excel tutorials and also in the card up above but let's get started with some basic formulas here today in this tutorial so i'm just going to

start with basic math formulas and then i'll look into the functions and go over a few more things but starting with entering a formula in this excel if you wanted to use it just as a basic calculator if you go to a cell click in the cell and when you're starting a formula you need to put the equal sign first so i would in first and then i can do a formula so if i was going to go five plus two hit enter i get seven uh if i click back on the cell again you can see the operation in the formula bar if i go ahead and double click on the cell it shows me so if i wanted to adapt it i could now i could use all the different operations uh this way to it but i would suggest that you you'd use more of a cell reference which i'm going to show you here because if you have a big spreadsheet and you have all these different formulas in you can't see what the constants are i don't know that this is five plus three without clicking on it but if i'm using cell references i can see them here it'll just make a lot easier and i can adapt them and change them a little faster too so what i mean is by doing this so if i go to if i want my answer here i still start with the equal sign like so now a cell reference i'm referencing that cell so it's going to be c8 and i want this addition and then c8 plus this one c9 and i'm going to hit return i get my answer as 7. now i can see that the constants are these numbers and i can quickly update so if also in this turn to 3 it's going to update it over here so i could go through all these different ones by putting the equal sign first click on the cell reference use your whatever operation you want click on the other cell and you're going to go through so these are the different operations that you can use from multiply to division to even the power one so if again if i hit equals select my cell use the power above the six and then click my other one so it'd be five to the power of three you can see it's 125 but then can be quickly changed at any time by entering in a different digit here so that's just your basic math formulas in excel how to make sure that you're writing them like i said use the cell reference that's what i would recommend but you can enter into any cell and just do the operations that way too

all right so let's move over into functions now and if you've never used functions before you can't remember they can do a number of different things inside microsoft excel they could be doing mathematical operations they can be looking up values they could be calculating date and time and i'm going to be showing you uh some examples in these but so for this first one right here i'm just going to look at a very simple function and that's the sum function and i'm going to put my answer right here and it's just going to simply add up uh the information that i have here uh i could go back if i was doing it to the previous method that i showed you i could just hit my equal sign and i could click on and just hit each cell adding up like so until i got to the last one and then i could hit return and go through but you can see that could take a while if the list was really large and functions can really speed this up too so i'm going to go back and just hit delete here and i'm going to show you the function sum so with a function we still need to put our equal sign so if i start with our equal sign i'm going to start typing sum and you can see if i knew what the formula was i could write it and fill it in but it will show me when this drop down comes up here i'm going to double click on it and i'm going to pick sum and it's going to say number one number two but what i like to do i'm just gonna highlight uh the range and i'm gonna hit enter after that and so you can see it's 170 a little bit quicker than hitting each one like i showed you before by cell reference and if i click on this one you can see what it did in the formula here so i could write this formula just like this and would give me the same information but you can do it like that to kind of just speed things up now if i move over here i'm going to go and do it a little bit different make sure you're under your formula tab up top here and go over to autosum i can just click on autosum like this and it's quickly going to grab that whole area kind of knows what it's looking for the numbers so it just knows that that's probably the information what i want and i can hit return and it goes very quickly here's a shortcut for you if i click in here and if i hit alt and then i hit the equal sign together it does the same thing so the shortcuts can really uh speed up a lot of time

using excel once you get to know them too there's other ones too so if we want to be looking at other formulas here's a simple one like count if i uh go and hit the equal sign and then i start typing count you can see it comes up i'm going to double click on this highlight my uh my range that i want and i'm just going to hit enter and it does a count of five so i just went through and counted now it doesn't seem like much here because i only had five entries but if you had a large amount of entries and you wanted to get a quick count of how many there would be i could use the count function now let me show you a few different things that you can do with this i'm just going to scroll down here a little bit i just want to point

out the status bar to you at the bottom in excel i'm just going to go ahead and highlight a bunch of cells here so i'll highlight these right here this range take a look follow my mouse down i'll get myself out of the way and you can see if you want a quick idea what's happening in the amount and the place that you selected look we have average count and sum so you can quickly even before you do a function get an idea what's happening okay so now what i wanted to show you was another thing that you can do with formulas such as sum if i was going to go and type my sum in here i'm just going to double click on this i'm going to sum up this amount and i could hit enter it would sum it up but what i want to do is i'm going to hit comma on it and i'm going to add another amount to it so i'm going to go and add 100 to it and you can see this time it added this up and added a hundred to it and if you take a look at the formula up here you can see that there was a comma 100. now we still wouldn't suggest to do it that way what i would do and i'll go to a new spot to do this if i put my equals sum and i'll just go back to this sum over here and i'll highlight i'll just highlight this section right through here and this time i'm going to put comma and click on the cell and it gets the same answer but remember if this cell changes to a different amount this adjusts only the only way i can see this one is so i click into it or double click into it and take a look at the bar here so i still would suggest using that cell reference in order to make things easier so it's easier visually to work with all right let's look at a couple other

functions popular functions and we'll go with average here so i want to average these numbers this range of numbers here and like the other ways you also have to start with equal so i put my equal sign in here and then i'm going to start typing average so you can see i could double click on the average uh and fill it in the way i showed you the sum or i could just keep adding i could add my bracket what's the number that i want to start with uh would be or the cell i could go ahead and type in d3 like so i could put in a colon like this and then i can go d6 and then finish it up and it will give you my average but like i said what i like to do is if i hit my equals start typing it in i could use my average here or i could just uh the other thing so i could use my average here real quickly highlight like this or i could use up in the autosum right here so if i click here type click uh drop down hit average and then it quickly does it that way too so just looking at different ways you can do it quickly to get things like that and we have a function like formula where if i could go in here i'll put my equal sign i'll start typing median and then i'll double click on this and what my range is going to be through here enter it gives me my medium median as 50. and mode so if i go ahead put my equal sign in notice that i get different ones this mode down here would be for older ones i'm going to use this one right here uh because i wanted to return the most frequently occurring and then i'm going to go and highlight what i want and you can see 50 because i have two different 50s in here so just some of the more functions some of the main ones that you are pretty common to use in excel

if you're looking for either the smallest number or the largest number the min max functions uh do this very quickly again imagine a lot longer list than these short ones that i have but if we go ahead click in here let's go back to formulas and use our autosum drop down here where it says min and max you can see as i click on min i get my answer back here i can click in here drop down we have max and go ahead and enter it in and it quickly gives that number back uh do take a look if min or max wasn't there you could go more functions at any time and you can type in here so if i was looking for anything i can type it in and go look through it here but i'll talk about the wizard a little bit later on in this too of how you can get more out of it

let's talk a little about date and time and i'm just going to use this right here and click in this spot and i'm going to enter today's date so i'll just write april 18th hit enter and it puts in today's date now if i look at the way it's formatted you can change that formatting if i right click on this and go to format cells you can see date and it's formatted to that so if you wanted it differently you could pick a different one but i'm just going to leave it like this for my purposes now i'm going to go ahead and delete this because i don't want to i just put today's date i want this to be able to change when it's tomorrow i want the spreadsheet to reflect tomorrow's date and so i'm going to do that by adding a function and if i use my equal sign to start with i'm going to type today and now i'm going to use this function this today function and you can see that there's a bracket open and all i need to do is close it don't put anything in between and hit return and it gives me today's date now the difference is even though it looks the same tomorrow if i open my work book it would give that date so it's always going to be changing based on what date so it depends on what you want if i would have just written out as today's date like i did the first time it wouldn't change now let's say my birthday was i'm going to say november 17th and i wanted to know the difference between how many days to my birthday i can do a formula with dates so i could go use my equal sign take this one the one further out i'm going to minus subtract this one from this one hit return and it's 213 days from there so i can do a simple formula like that using dates so another thing i could do uh let's say the bill is due on and i'm just going to actually copy this spot right here so i want to take it from today's date it's going to just reflect that cell and i'm going to want it to add how many days i say in the grace period so if i go plus and it's whatever i'm going to put into here so how this changes if i know there's a grace period days maybe for the bill maybe 20 days so i had 20 and here it's adding days to it and you can see it went from april now it's going to may based on the grace period that i added in there okay so let's look a little bit of time now so there is a function again for time so if i go ahead and put my equals in here and i'm going to start typing now and you can see there's now and again if i close it i get today's time our rate the moment right now is time so think about that could save you some time with inputting date there are shortcuts to this too so look at this so we have control plus semicolon if i hit those two uh together like so it quickly enters the date in and then you can see the time with control shift here and the colon like so enters the time so again if you get to know those shortcuts it can really save you some time when you're in inputting values into your spreadsheets so you can

use formulas to combine cells and let's say i have names in different columns but i want to put them into one cell together if i i'm going to use this spot over here this full name i want their full name here what i'm going to do is start with an equal sign just like all the other things i've shown you i'm going to click on the cell that i want like this and then i'm going to use the end symbol and click on the next one and hit enter so as i do that you can see it says nancy smith but notice there's no space in between on it and i want to correct that i can do a few different things i'm going to go back into here i'm going to double click and here and i'm just going to edit what i have if i go back into this spot and i add a quotation mark here and let's say we'll do a comma if we want it no we'll do a space like this and then we'll do quotation again and i put another n symbol like this notice now i have nancy smith with a space in between so with that space i could put a comma or whatever and it would put that there and i could go ahead and copy this down so you can use a formula to combine cells and using the end uh just to point out is a little tip here i really like flash feel and i have other uh tutorials on this uh too but if any time no matter if you're writing how i'm writing it and i'll write it backwards this time with a comma notice i'll write the first one if i go down here as soon as i start writing north andy and it recognizes there's a pattern and i hit enter it automatically fills it in if it's in the column beside it will do this so this was using a formula in this way but i just wanted to point out flash fill here for you too because there's a quick way uh to do that type of thing too

when you're using if statements what you're trying to do is make a logical comparison between conditions so what i mean is if you say if this is something do this or else do this and let's see here what i'm going to do is in this spot if the word apple is here it's going to say true if the apple if the word apple isn't there it's going to save false and so let's set up this condition so if i'm going to say equals we start with an equals it's going to be an if and notice when we choose these you just have to fill in kind of copy what they do with it right with the right brackets commas and everything so what is it so it's going to be this spot if this spot right here equals what's it gonna equals and since it's a word i'm gonna have to use quotation marks here apple so we'll type this like this so i'll close my bracket now then what's going to happen here then it will be true right so i'll type true like so but if not it's going to be false so i'll just write false and then i'm going to close my bracket and so right now you can see this is true because apple is in that spot so what i'm going to do actually is just copy this formula down to here and you can see it says false so i just copied the formula down because what it did it went over here into orange and checked so if i look at the formula it went over to c7 and then it said are you apple nope so it went to this part and no it's not true it doesn't match and it gave me back false so let's try it in a different way uh using uh with a greater than with this 50 here so what i want to have happen is when i change this number i want it to change what it will say here and i want it to either say less than 100 or greater than 100 based on whatever numbers entered at this spot so i'm going to go to my if statement so i'll say equals i'll put my equals then and get my if and i'm going to open this up and it's going to be this spot so what do i want to have happen in this spot so i'm going to say well if this spot is less than 100 like so what do i want to have happen well i'm going to put a comma here and you can see how it moves to the next one value if true so if it's true i want it to say something so i'll say this is less than 100 like so and i'll just close this up here put my comma and notice it moves to the other one here and then i'm going to go to my quotation mark again and say greater than 100 and end it like with another quotation mark and i can close the bracket and hit enter so at this point it looked at this and say if this is less than 100 say less than 100 here so if i change this number now and change the condition here so if i say 120 it's going to say greater than 100 so that's just a little intro in using if statements and how they work inside microsoft excel okay so now i want to

show you vlookup which is a super popular very powerful function inside microsoft excel how it works and i'll just before i show you how to set it up i'll just show you in action in this case i just have it set up in this cell right here if i click in it you can see up in the formula bar it's all set up and what it does if i type i'm going to type apples in this spot here so i'll type apples and you can see it returns the number the next column so i could have this long list and i could tell it to search for something and it will give me a corresponding column back to it so what i'm going to do here is set it up for this spot here where it's going to search this information so whatever i type in this spot it will give me back the corresponding column to it so what i'm going to do is go ahead and put equals to start it start typing vlookup like so and i'll just open this up and i'm just going to follow this across so what spot is it i'm going to pull from is this whatever's written right here so i'll click here give it a comma and now it moves the next one well what's the table or that i'm pulling it from so i'm going to highlight both columns because i need both columns for this and i'll give it comma again a comma now this next one is asking well where do i want to where's the next column so when it's either one number two you're going to put a number two in because if i put number one it's just going to pull it from this column here but i want it to pull it from here so i'm going to put a number two and give it another comma and is it true or false so true is approximate match or false an exact match i want it to be an exact match so i could type faults or i could just double click on this and close my bracket and hit enter so let's try let's type uh pork in here and see what happens i get returned 10. so that's vlookup it's a super powerful excel function very popular a lot of people use it check out my other tutorial on it if you want to dive deeper but give that a try with some small tables

conditional functions allow you to sum average count max min based on a condition or criteria that you choose so here it is an action if i write a word here one of these ones from the list it's going to go through this side and add up all the ones that relate to it so lemons you can see 120 if i type in apples here oops i better type in the right spot i'll go here and type apples it changes to 150. so it went through found the apples the corresponding number and then added them all up so i'm going to go ahead and delete this and just create this sumif function i'm going to hit the equals type sum if it's right here i'm going to open this up so what's the range that i wanted to go from so this is the range that i choose at the beginning hit comma and what's the now it's asking for the criteria so it's kind of hidden this spot but i know it's c17 and hit comma and what's the sum range so it's going to be out of this one these are all the sum range and i'm going to hit return of this one and then i get my uh my sum if function so you can see again if i change it to something out else here to oranges since i typed that wrong that didn't go through to anything and then it adjusted here so we have 64 oranges but you can also have multiple uh columns with this so if i have some ifs and i'm going to create one for this one it's going to be based on these two criteria here so if i go here and type equals this time it's going to be some ifs right here and you just follow along so what it's going to do is so what's my sum range this time it's going to be through this one comma criteria one range so the range is the the group of cells to it comma and what's the criteria one is right here so that's going to be f17 comma what's going to be my second criteria range will be this one and then i can hit comma and so my next one it's hidden but it's going to be g17 because it's just one over and i'm all done there i'll just close it up hit enter so now as i go through you'd be able to see if i type apples and then go over here it's zero until i type fuji and then it will go through and add up all the fuji because you can see there's only the one up there so you can have the multiple conditions that are happening and like i said you can have things like count if it goes through it can count and all those different functions from average the max the min that i showed you earlier you can add these conditional functions to it that you have the criteria uh that you can choose so i'm just going to end this tutorial here today talking about a

function the function wizard inside microsoft excel that can save you a lot of time if you're especially getting into more complex uh functions and it can help you walk through what are the correct steps to complete the function now i'm just going to use this vl lookup example that we've done previously in this tutorial here and my function's going to go in right here i'm under the formulas tab i'm going to go ahead and insert function right here now i'm going to use vlookup again and i'm going to just go and fill out the this information so as i click in each one so the lookup value will be and it gives you an information down here what you can read so is the value to be found in the first column this is going to be what i'm looking up right here so what's then i click on the next one it shows me uh tells me gives me an idea what to go and grab so i'm going to go grab the table and then the column index so i click in here and it tells me that it's going to be the first column of value as table is table column 1 so i want to pull it out of number 2 over here and then lastly the range lookup is either going to be true or false in this case i'm going to be using false and i can hit ok when i'm done and as we go through and type these ones it will adjust now accordingly to what i wanted so that's used in the wizard it can really be helpful when you're trying to construct formulas that you're having some problems with so i hope you like this tutorial today hopefully it comes in handy with your excel skills and i can build on things that you maybe need a refresher in or maybe you're learning for the first time take a look at my other tutorials i kind of dive deeper into a lot of these things thanks for watching this time on teachers tech i'll see you next time with more tech tips and tutorials