# Excel Formulas and Functions | Full Course

hi everyone kevin here today we are going to learn about formulas and functions in microsoft excel maybe you have a job interview coming up or a test where you have to demonstrate your knowledge of excel don’t worry i’ve got your back we’re going to start with the fundamentals of how you can even enter a formula into excel and then we’ll advance to the most commonly used functions by the end of this video you’ll be a pro with formulas and functions to follow along i’ve included a workbook down below in the description you can also jump around this video using the timestamps down below alright let’s get started here i am now in microsoft excel and i’m currently on the formula fundamentals worksheet just in case you want to follow along and i want to calculate how much revenue i earned from the three different types of cookies that we sell here at the kevin cookie company so i could use a formula to calculate this but even easier i want to add up these three different cells when i highlight these three different cells if we look down below here on the status bar you actually see that we have the sum right here it’s 23 and that’s how much revenue we made from these cookies so you don’t even have to enter in a formula to get some basic metrics back and in fact if i click on the sum here i can go into the total cell and press ctrl v to paste and here i’ve added up all of these different cells just like that just by using the status bar now down below when i have these three values highlighted i could also go to the status bar and right click and here if i scroll down this menu you’ll see that i also get the average the count here i can get the min the max and here i have the sum so here if i check this on here i can also see the max now down below on the status bar so this is one way to get some quick answers without even needing to enter in a formula now of course this video is all about formulas and functions and we should probably enter in our first formula so here i’ll come up and let me delete the 23 that we got from the status bar and here once again i want to add up 10 plus 8 plus 5. so the first thing i need to do to enter a formula is to enter the equal sign this tells excel that i’m about to enter in a formula and here i could take 10 and i could add 8 and i could add 5. here you could see my very basic formula now if we look up above here in the formula bar you’ll see the formula here as well to calculate this formula now all i need to do is to press the enter key congratulations you just entered your first formula in excel to go back and edit this formula here i can click into this cell and here we see the formula up above so i could modify the values up here or alternatively i could also double click into this cell and this will once again show me the formula and then i can make modifications here now one problem with just entering in the formula like this let’s say that now we made revenue of 11 on our chocolate chip cookie here i’ll press enter you’ll see that this did not update and the reason why is we hard coded in these values so as these values over here change my formula doesn’t change so instead of hard coding these numbers or values in instead i want to reference the different cells so here for example instead of typing in 10 i want to use cell b2 so here let me delete 10 and i can type in b and then i’ll enter 2. and here you see that the cell has now highlighted and here also for the eight instead of hard coding that here i’m going to type in c2 and also here i want to type in d2 for the sugar cookie revenue and now i can press enter and here you see i get the same results but the nice thing now is let’s say the revenue for the chocolate chip cookie changes let’s say it’s now 11 you’ll notice that the total automatically updates when i click back into the total cell here you’ll see that excel uses different colors so here you’ll see blue on b2 and this is also highlighted by blue here c2 is red and here you see red and here’s purple and here you see purple so the colors here in the formula correspond to the cells that they’re referring to now of course typing in a cell reference works but it also requires quite a bit of effort and instead we could use our keyboard or our mouse to reference specific cells here once again in the total cell i will enter the equal sign and instead of typing in b2 i can simply use the arrows on my keyboard to select that cell here i’ll type in the plus sign and i can arrow over and once again i can arrow over and then hit enter so that’s yet another way i can refer to specific cells but even with the arrow keys let’s say i have a very large sheet and it can take a while to get to the cell that you care about here once again i’ll click on delete here let me enter the equal sign and instead of using my keyboard i can also use my mouse here i can click on b2 and i can press the plus sign and here let me select c2 plus and then d2 and here too i could press enter and once again we had revenue of 23. now that we know what the revenue is let’s say i want to know what the cost is now once again i could type in the equal sign and i could select this cell i could add this and then i could add this so just like we did up above but that takes a bit of effort instead i can click into this cell where we already have a formula and i can click on this fill handle in the bottom right hand corner there you see that my cursor changes to a plus icon and i can simply click on that and then drag it down and here you’ll see now that the formula has updated where it adds up all of my different costs so the formula adjusted relatively i moved the formula down by one row so it also updated all the references by one row over here i can also use subtraction so let’s say i want to calculate the profit to calculate the profit that’s the revenue minus the cost once again i’ll enter the equal sign and here we had revenue of 10 for chocolate chip and let me subtract four and once again i could press enter and here the profit is six now just like we did before if i want the profit for all of these other columns once again i can click on the fill handle and i can drag this over and once again it automatically adjusts the formula relatively so i moved it over one so the formula references moved over 1 and here you can see how that worked for every single column that’s a quick way to reproduce a formula in many different columns or many different rows now this is some pretty nice profit but unfortunately you have to give up some of that profit towards taxes and to calculate the tax we’re going to use multiplication down here in cell b5 i want the profit of 6 and i’m going to multiply it by the tax rate of 10 so once again let’s enter the equal sign and here i’ll reference this cell with a profit and once again i want to multiply it by 10 percent now you might think the multiplication sign is an x but in excel that’s just a character to enter the multiplication sign we enter the asterisks that’s how you multiply something and here i’ll select 10 percent and then i can press enter and it looks like the tax is 0.6 or 60 cents now here once again i want to calculate the tax for all of my different cookie types so just like we did before we could use the fill handle here once again i’ll select that and let me drag it over one column but here you see that the tax is zero now from a business standpoint that’s good news but i don’t think it’s calculated correctly here if i click on the cell i can go to the formula bar and when i click on this i can see all of the different references and here it was making relative references so once again as i pulled the formula over here it adjusted it to look at this cell but if we look up here at the tax rate it also moved this reference over one but i want the reference to point at this specific cell or g2 so i basically want to lock this reference so let me go back to the original formula where we calculated the tax and in here once again i want to lock this reference in and we’re going to use a shortcut key to do this we can press the f4 key or the function four key on our keyboard and you’ll notice that it adds a dollar sign before the g and a dollar sign before the two the dollar sign tells excel that we want to lock both the column and the row here i’ll press enter and now if i pull this over you’ll notice the formula works as we expect it to and when i click in here you can see that it adjusts this first cell relatively and then it locks in the tax rate and here it did that for all the different tax calculations this is what’s referred to as an absolute reference as a quick note here when i’m in this cell and we see the formula up above with the dollar signs if i press f4 again now you just see a dollar sign on the row if i press f4 again you just see the dollar sign on the column so you could just lock the column or you could just lock the row and here if i press f4 again it removes it from everything and once again it’s purely just making a relative reference but once again i want to lock it so i’ll press the f4 key with this formula i’m currently referencing the tax rate that’s on the exact same sheet but you can also reference a cell on a different sheet so here once again let’s go back to this first cell here i’ll delete these different values and instead of referencing the tax rate here let me delete this and right now i need to enter in the tax rate and it turns out there’s another sheet called tax rate i can click on that sheet and here we see that tax rate of 10 i could click on it here and once again i could press f4 to make this an absolute reference here i’ll press enter and once again this works and here you see that it uses the worksheet names so it’s pointing to this tax rate sheet and then the cell on that sheet you can even refer to a completely different workbook if you want and you’ll see the notation appear right up here in the formula bar now once again i can take the fill handle and let’s calculate the tax across all of these different columns now this seems to work well where i’m referencing a different sheet but when you look at the formula itself it it’s a little bit messy you have the sheet name and then you have the cell reference i’ll go back to the tax rate sheet and i can give this cell a name so i don’t have to refer to it as a2 but instead i could use the name reference right up here where you see a2 here i can simply type in tax rate and then press enter i’ve now assigned a name to this cell let’s go back to formula fundamentals and here instead of referring to the sheet and the cell i’m going to delete that and instead i’m going to type in tax rate and here you see a hint that this is a known name now i can press enter and that too works and it’s a lot cleaner now now i simply refer to tax rate and it’s really easy to understand what that is now let’s say that i’m entering in a formula and i forget that i had a name called tax rate i can press the f3 or function three key to see all the different names when i press f3 here i get all the different defined names and here i see tax rate i can simply double click on that and that inserts tax rate into my formula and then i can press enter so once again that’s another way to get back to your different named cells we’ve looked at addition subtraction and multiplication and you can also do division as well here i’ll go down to the profit split sell and here at the kevin cookie company we made 14 of total profit for now i’m going to ignore taxes and let’s say that we have two owners of this company it’s me and patty so here once again i’ll type in the equal sign then i will select the total profit that we earned and i want to divide it by two to see what my share is to do division we’re going to enter the forward slash and then here i will simply type in two and press enter and here we can see that my share of the profit is seven and paddy’s share of the profit is also seven next let’s move on to the order of operations worksheet and here once again i see the revenue the cost the profit and the tax for chocolate chip cookies now let’s say i want to calculate all of this in just one cell to see what the tax is i’ll enter the equal sign and first off i need to take the revenue i want to subtract the cost and that should give me the profit and next i’m going to multiply this by the tax rate of 10 percent and then i’ll hit enter but whoa what’s going on here my taxes 9.6 but my profit was only 6. that’s ridiculous i mean i i know tax rates are going up but it shouldn’t be more than my overall profit the problem here is the order of operations and when i click into the cell let’s double click in we could take a look at how excel calculates this excel looks for multiplication and division first and then it evaluates that part of the formula so here for example it’s taking 4 and then it multiplies it by 10 percent or 0.1 and that gives us 0.4 as the result in fact if you want to calculate a portion of your formula here i have this portion highlighted i can press the f9 key and here i can see that it evaluates to 0.4 i’ll press ctrl z to undo that so it’s taking 10 and then it’s subtracting 0.4 and once again that gives me 9.6 now i wanted to first calculate 10 minus 4 and then take the result of that and multiply it by 10 percent to do that i can use parentheses and that way i can define what i want the order to be here i’ll put a parenthesis around b2 minus b3 that way excel will evaluate this it’ll take the result and then it’ll multiply it by 10 now when i press enter i get 0.6 and that’s exactly the same as what we got on the previous sheet so one thing to be aware of when you enter formulas there is an order of operations now up to this point we’ve purely been looking at formulas and next we’re going to look at what’s called a function but how is a function different from a formula well with a function you can pass in a parameter or an argument and then you get a result back now i know that probably doesn’t mean that much so why don’t we make this real and look at some examples here on the basic functions worksheet i want to answer the question how many cookies did we sell here i see all the different cookies we sold so i want to add up this list now we could just use a simple formula to do this once again i could enter the equal sign and i could take this and i could add it to this and i could add it to that and i could go through this whole list but that takes a lot of time instead we want to use a function and that’ll make this entire job a lot easier so how do we insert a function well one thing we could do is up here we could click on formulas or the formulas tab and over on the left hand side there is the option to insert a function as i hover over you’ll also see that the shortcut key is shift f3 so you could use that as well let’s click on this this opens up the insert function dialog and right up on top it says type a brief description of what you want to do and then click on go now once again i want to add up all these values or another way of saying that i want to sum up all these values so let’s type in sum then i’ll click on go and here it looks like there’s a function called sum there are also some other options and we’ll get into these later but for now we just want to sum these up let’s click on ok here now it asks me what numbers i want to sum up i’ll move this over just a little bit so we can see the numbers we want to add up and here it says number one number two now i’ll delete this value and here i can click on this cell number two i can click here but you’ll notice that this isn’t very much faster than just adding up all of the different cells using formulas so here let me delete those again instead here i can highlight this entire column so here i can highlight all of these different values and you’ll notice here it says b2 colon b8 so i’m passing in all of these different values basically this range and here you can see the range of values that i pass in these are referred to as the arguments or the parameters that i’m passing into the function and right here at the bottom you can see what they add up to so when i sum all of these different numbers that gives me 421 and this is a basic function next i can click on ok and here you see that i just summed up all of these different cells one thing to call out when i double click into the cell you can see the notation of a function so once again you start with the equal sign that lets excel know that you’re entering either a formula or a function then you type in the function name and then you open the parentheses here you pass in the arguments or the parameters and then you close the parentheses at the end so instead of using the insert function helper you could simply type this in now interestingly there’s also a function that you could enter in that shows you a function so here i could type in equals and let’s type in formula text and then here i’ll open the parentheses and i have to reference a cell so let me reference this cell and then close the parentheses so i’m following this same format that we saw with sum and when i press enter here now we can see the function that’s being used in this cell so just to help you see what’s going on to get this 421 now excel has many many many different functions that you can use and here if we click on formulas up on top here you can see some of the different categories you have financial functions you have logical functions text functions and there are many many different options here but once again if you want to search for a function once again you can click on insert function and then you can use the search command now there’s one category here called autosum and when i click on the drop down this contains some of the most commonly used functions here for example we see sum again but you have average count numbers max and min and then once again you can get to all of your functions so let’s say for example here at the bottom of the list let’s say i want to sum it up here i can click on formulas and then simply click on auto sum and it’ll default to sum and here i can simply press enter and i get the sum now autosum is used so frequently that it’s also on the home tab here i could click on home and i could go over to editing and here once again we see auto sum now when i hover over this you’ll see once again that it defaults to sum but here too you’ll see the shortcut key i could press alt together with the equals key so here i will click into this cell and i can press alt and equals and once again that will also give me the sum so that’s an even quicker way to get this sum now once again this is one of the most commonly used functions in excel over on the right hand side i want to continue answering these different business questions and i want to know the most number of cookies sold here once again i can click on formulas and let me go back to this autosum group because it has some of the most common functions and here’s the function for max i could click on that and here it enters max but it’s looking at this cell and instead i want the max number from this so here once again i’ll highlight this list so this is the argument or the parameter that i’m feeding in and i could press enter and here i could see that chocolate chip had the most number of cookies sold at 97 and here we could validate that by looking at this list i’ll delete the 421. right over here i also want to know the least number of cookies sold and if we just look at this list we see that it’s fortune with 36. i’ll click into this cell and once again i can go to formulas autosum and here i can select min but instead let’s just type in the function once again i’ll type in the equal sign and let me type in the function name min open the parentheses and then we have to pass in the argument and that’s this list of values and then i can close the parentheses hit enter and here we see that fortune cookie only had 36 cookies sold let’s say instead of knowing the max or the min i want to know what was the second most sold or what was the second least sold and for that we could use the function large or small here i’ll type in equals and i can type in large and then open parentheses and here it asks for an array and a k and if you’re not quite sure what these mean to make it a little easier we could click on this fx or insert function and this once again opens up the function arguments and here we see some hints on what those values need to be so first off the array is the range of data so here i’ll click into this cell and let me select my data or what’s referred to as the array and then k this refers to the number that we want back so here i want the second largest so let me type in a two and here we can see that it’s 77 for oatmeal raisin i could click on ok and here i get the result back so as you enter different functions in if you need help with them remember you can click on this fx right up above and that will open up the function helper and this will help you to compose your function down below i want the second least sold so let me type in the equal sign and for this i’ll type in small and once again it wants the array that’s this list and i want the second smallest so once again i will type in a two hit enter and here i can see that peanut potter is the second least sold cookie with the large and the small functions we entered two separate parameters so here once again when i open the function helper here you see that it’s asking for an array and it’s also asking for the number that we want so with various functions you may have many different arguments that you can pass in and this is just one example where you can pass in two different arguments or two different parameters next let’s say i want to know how many cookies had any sales at all and there’s a function for that called count here i could type in count and we see there are also some other options and in a moment we’ll get to what some of those are but here i’ll type in counts once again open parentheses here i can highlight this list then close parentheses and then hit enter so i can see that seven different cookies had at least some sales now let’s take a look at the count of the cookie names so here once again i’ll type in count and let me highlight all the cookie names so how many different cookie types do we have and here i could press enter but here it shows me zero so why is that well when we use the count function this is counting numerical cells and these cells over here have text based values so it’s returning a zero and once again if i click into this let’s take a look at some of these other count functions once again i’ll type in equals count and here i see that there’s an option called count a and when we read this this will return back all the cells in a range that aren’t empty so let me select that and here once again i could highlight this list close the parentheses hit enter and here now it tells me that there are seven different cookie types and just like you could count cells that have a value in it you can also count all of the blanks so here i could type in count blank and let’s say i select all the cookie names plus one additional blank row then i’ll close the parentheses and here it tells me that there’s one blank value so yet another count function that you can use if we move down just a little bit more there are a few more questions that i want to answer and this will show us even more functions i want to know across all of my cookies what was the average sold to enter the average you could probably guess it but the function name is average and once again if we go up to formulas and autosum here we’ll see average here so you could also get to it here i could select average once again i could highlight the list and i could hit enter so i don’t always have to close the parentheses i can just press enter and that too will calculate so that saves you a little bit of time and here i can see that on average we sold 60.14 cookies along with your average you can also calculate the median or basically which number is in the middle of this list if we just look at it we see that it’s 52. here i could type in median open the parentheses let me highlight this list and press enter and there we see that it’s 52 and we could also use something called the mode and this tells us which number occurs most frequently in this list now when we look at this list 97 shows up once 77 shows up once but here we have 52 that shows up twice here i could enter equals mode once again i’ll select this list hit enter and here we see that 52 shows up the most often in this list moving on now to the conditional functions worksheet we’re now getting a little bit more advanced and here we have a function called sum if so here we can sum up values if it meets a certain criteria so let’s say for instance that i want to know how many cookies we sold in the united states so if it’s the united states i want to sum up all of these values and for that we’re going to use the sum if function now once again probably the easiest way to enter a function is to click on the insert function icon and this opens up the dialog that will help us write this function here i could type in sumif click on go and here i see it down below i’ll click on this one here it asks me for the range and so what is the range well here i can just look down below and it tells me exactly what that is it’s the range of cells that i want to evaluate now remember i want to evaluate whether it’s in the united states so here i could simply highlight this column right here and for my criteria i want it to be the united states so i’ll click in this cell now i could also type in united states here but in this case i’ll simply click on this cell lastly it asks me for the sum range so if it finds united states in this column then i want to add up these different cookies sold so for the sum range i’ll select this column and here i could already see that it’s 362. i’ll click on ok and just to confirm i can highlight all of these different cells and down below i can see that the sum is 362. so this is how you can use a conditional function down below you’ll see some additional functions like average if and here’s count if and these work the exact same way as some if if some condition is met then you’ll average out all the values or if some condition is met we could count all of those items so once again they work exactly like some if except in this case you average or you count i want to show one neat thing that you can do here within the function for some if let me open up the function helper again and for the criteria here i’m going to type in united states and once again i’ll click on ok and we see that the function works the exact same way but let’s say that i want to sum up all the values for countries that end in an s now it’s an arbitrary situation but i want to show you how you can search based on a wild card so here i’ll remove most of the united states but i’ll leave the s and here i can insert an asterisk or the wild card symbol so it’ll look for any characters before the last character which needs to be an s here if i press enter i get a sum of 802. so this is the sum of all the cookies sold in the united states which ends in an s and also the philippines which also ends in an s and here if i highlight all of these cells i can see that the sum here is 802 and that matches up with 802 right up here so this is yet another way that you can sum different values based on a condition next we have another formula called sum if s so it looks like the same thing as some if but why do we have an s on the end well you can think of this as the plural version so you can look for multiple criteria so let’s actually test this out to see how it works here i’ll click on insert function and here let’s type in sum if and s i’ll click on go and then select this function and here first it asks me for the sum range so what do i want to add up here i’ll select the cookies sold column and here it says the criteria range 1. so i want to evaluate the country column first so let’s say i want to know how many cookies were sold in the united states i’ll select this column and then my criteria is united states but now i can add additional criteria so maybe here i want to check was it a chocolate chip cookie so here i could select this range and for the criteria i will select chocolate chip and then i can click on ok and here i see the sum of chocolate chip cookies sold in the united states is 28. building on this let’s say i want to know how many cookies were sold in the united states that were either chocolate chip or oatmeal raisin and i can do that as well with some if and s once again i’ll open up the function arguments and right here where i have my criteria for the cookie type here i chose chocolate chip cookie i’ll remove that and here let me highlight chocolate chip and oatmeal raisin so i could select two different values as my criteria and then i’ll click on ok and here i get two values back one is 28 which is the sum of chocolate chip cookies and then i get 32 which is the sum of oatmeal raisin cookies sold in the united states now i get them back separately so each criteria comes back as a separate entry now if i just want to get this back as one right up here within the formula i can click here type in sum insert a parentheses around the sumif function then hit enter and here it’ll add up those two values and so the sum of both chocolate chip and oatmeal raisin in the united states here if i highlight these cells we see at 60 and here i get 60 back down below we also see average if and s counts if and s and then also min and max so once again for all of these different functions you can have multiple criteria now you might be wondering well with sum if and s you could just have one criteria at which point it’s exactly the same as sum if so you could just always use sum if and s and then ignore sum and if because this not only does one criteria but it also allows you to add in multiple criteria moving on to the next worksheet we’re going to look at some functions that allow you to manipulate text and these can be a big time saver here’s the first one we have the kevin cookie company name but i forgot to capitalize the first letter of each word now of course i could go through and i could change that or i could use the proper function here i’ll type in equals proper open parentheses and here i can place this text in close the parentheses hit enter and here it applies proper casing to every single word the next function is the trim function and here once again we see the kevin cookie company and here i have some leading spaces in front and i don’t want those now of course i could simply delete those but once again if you have a lot of rows of data that might take a lot of time here i can type in equals trim select this text close the parentheses and here it’s automatically removed those leading spaces next we have a set of functions that allow you to concatenate or join or combine text together so here i have kevin cookie company and each one of these references is in a different cell but i want to bring them all together i could eat i could enter an equals concat and you’ll see that there are two versions there’s concat and then also concatenate can cat is replacing concatenate so i’d recommend using this one this is simply here for backwards compatibility concat has a few advantages over concatenate and that’s the preferred one to use here i could simply copy all this text close my parentheses and hit enter and here you see that it brings together all of that text now of course i don’t have spaces between these different words so up here once again let me go back to the function i’ll delete the text or the range that i entered there and here i can click on the first one place in a comma then i could insert a quote a space a quote another comma and then i could click on the next text and i could also go through and do the rest of it but i’ll just leave it at this when i hit enter now you’ll see that it includes the space so here i can cat this word with a space with the next word now instead of typing in concat you can also use an ampersand so here for example i will enter equals kevin insert an ampersand and then cookie and then ampersand and company and there you see it combines all of them now of course once again i have that same issue with the space missing so here i could insert a quote a space a quote another ampersand and then hit enter and here you see it’s added to space and i could do the same between cookie and company but by far the easiest way to join together text is to use the text join function here i have kevin cookie company i’ll enter equals text join open parentheses and here i get to specify what the delimiter is or what’s going to separate each one of these cells full of text i’ll enter a quote a space a quote a comma and then i could say what should i do if i encounter an empty cell here i’m just going to ignore them put in a comma and now i can select all the text that i want to join i’ll highlight these three and then hit enter and here i get kevin cookie company back with a space in between each word so out of these three options of concatenating or bringing together text text join is by far the most powerful one moving on we have some additional functions here right left and mid and these allow us to take out text from the right hand side the left hand side or the middle so what do i mean by that well let’s say that i want to pick out the zip code from this address i can use the write function i’ll enter in right and then i also like this address as my text a comma and then i need to define how many characters do i want to take out from the right and here we can see the zip code is five characters i’ll type in five and then hit enter and this pulls out the zip code next we have another function called len and this is an abbreviation for length so i could use len to figure out how many characters make up the kevin cookie company i could type in equals len open parentheses and then i can put this text in close parentheses and there we see that it’s 20 characters long next there’s another function called find and then there’s one called search and they essentially do the same thing but there’s one key difference find is case sensitive and then search is not case sensitive here in this example let’s say i want to find out what is the position of the at character within this email address and i could use either find or search to find this since at isn’t case sensitive here i can insert the equal sign and let’s use find i’ll type in the function name and i want to find the at symbol here i’ll insert it in quotes since i’m searching for text put in a comma and i want to find it in this email address i also have an optional parameter at the end but i’ll leave that just blank here i’ll press enter and i can see that the at symbol is in the sixth position of this text now you might be wondering what good is it knowing what position the at symbol is in well you can really get some nice power when you combine this with other functions so let’s say that i just want to pick out the name portion of this email address and i don’t care about the at or the domain at the end here i can start combining some of these text functions for example i could use left together with find let’s enter in the left function now remember with left it’ll keep the leftmost characters and i have to define how many characters on the left i want to keep here i’ll select this text then i’ll enter a comma and now i have to define how many characters i want to keep now i just want to keep five basically right up to the at symbol and i could use the find function to tell me where that at symbol is so the same function that we entered right up here that gave us six back here i’ll type in find and once again i want to find the at symbol here i’ll enter the at symbol and i want to find that within this text now i’ll close the parentheses and close the parentheses again and when i run this now i see that it has kevin and at and that’s because here it kept the left most six characters so one thing i could do if i go back into the formula or the function i can subtract one from the results of the find function and then i can hit enter and it just keeps kevin because i don’t want to include the at symbol the nice thing now is this is completely dynamic so if i change the name to say sue here it automatically updates to sue so this shows you the power of when you start combining these different text functions together moving on to the logical function sheet here we’re going to see how you can add some logic to your spreadsheet and first off we could simply check is something true or false so here i’ll enter in the equal sign again and is the kevin cookie company equal to miss fields now i hope all of you know the answer to this but of course not the kevin cookie company is way better than miss fields now this simply compares those two text values to see if they’re equal and of course they’re not so it says false but you can also use this for numbers so is 10 equal to 5 and no that’s false here i could also say is 10 greater than 5 and that’s true here i could also say is 10 greater than or equal to 5 and that’s also true and here is 10 let’s say less than 5 and that’s false so just a quick look at how you could compare different values within your spreadsheet here too you could also check if multiple criteria is true or false i’ll enter in an equal sign and then type in and open parentheses and here i could say let’s say is 10 greater than 5 then i can insert a comma and let’s say is 10 greater than 11. and then i can close my parentheses and that’s false 10 is not greater than 11 but it is greater than 5 but both conditions have to be true for this to come back as true so let’s say i entered a 9 in here that is now true now instead of using and i can also use or i’ll modify the function to or and here it says is 10 greater than 5 or is 10 greater than 9 so here if i put the 11 in it’s still true because 10 is still greater than 5 even though it’s not greater than 11. moving down now we’re going to write an if statement this is kind of feeling like programming a little bit we get some if statements on our spreadsheet and here’s the business question that i want to answer do we sell more chocolate chip or oatmeal raisin cookies here we just look at these numbers and we sell 97 chocolate chip and we sell 77 oatmeal raisins so of course we sell more chocolate chip but can we use an if function to answer that question to make this easy let’s click on the function helper up here and let’s type in if and here i see the if function let’s select this one now here i can run what’s called a logical test so let’s say is 97 greater than 77 that’s my test and here it comes back true and then i can define well what happens if it’s true so here i’ll say we sold more chocolate chip but if that’s false and this is not greater then i could say let’s say oatmeal raisin and then i could hit enter so do we sell more chocolate chip or oatmeal raisin well we sell more chocolate chip but let’s say that oatmeal raisin let’s say we had a big day of sales and i sold 150 well now it automatically updates and we’ve actually sold more oatmeal raisin now moving down we can also use and together with our if statement so here for example does chocolate chip sell more than all other cookies so let’s once again insert an if statement here i’ll click on the function helper and i’ll select if again so here i can enter my logical test i’ll type in and remember this is just like we did up above and i want to run two separate tests first off is 97 greater than 150. and for my second test i want to check is 97 greater than 50 then i’ll close my parentheses and if true let’s say yes and if false i’ll enter no then i can click on ok and here we see that does chocolate chip sell more than all other cookies no we don’t oatmeal raisin sells more but let’s say i typed in 96 here now it’s yes now just like we looked at previously here i can take this function and let’s use it for this or statement so does chocolate chip sell more than oatmeal raisin or sugar cookie i’ll paste in this function and instead of using and i’ll type in or then hit enter so does chocolate chips sell more than oatmeal raisin or sugar now let’s say oatmeal raisin sold let’s say 150 well it still sold more than sugar cookies so it’s still yes but let’s say sugar cookie also sold 150 now it shows no so that’s how you can use and and or within an if statement now you might be wondering can you include an if statement within an if statement and you absolutely can and here in this example let’s say i want to know the price of a chocolate chip cookie so here we see all the cookie types and the associated price and i can use a nested if to figure out what the price is for this cookie type let’s work through it here i will enter the if function and first i want to check is this cookie type this one right here and if it is well i want to get this price back but if it’s not well we want to then check the next row so here i’ll type in if again and here i want to check is this name equal to this cookie type and if it is we get this price back and if it’s not then we want to run one more if statement so is this cookie type equal to this cookie type and if it is this is our price and if it’s not then we’ll say unknown and then i can close the quotes close the parentheses close the parentheses again and close the parentheses again one thing that’s really helpful when you’re closing the parentheses you’ll see color on the parentheses so the first one’s black the next one’s red the next one’s purple and here you can tell that i’ve inserted all the right number of parentheses now i could hit enter and i could see that the price of chocolate chip is 10. but here i could type in sugar cookie and there i see the price is 5. so that’s how you can use nested ifs to do that here i’ll enter in formula text just so we can see what all of the formula looks like so you’ll notice it’s a fairly long formula and it starts to get pretty complex now there’s something new called if s that makes this a lot easier here i’ll type in if s and here first i could do the first logical test so is this cookie type equal to this one and then if true i want this price the next logical test is this cookie equal to this one and if true i want this price and lastly is this cookie type equal to this one and if so i want this price now i can close my parentheses and hit enter and here i get five back i’m going to move it over one and once again let’s show the text for this function and you see it’s a little bit shorter and easier to read than what you see up above and in fact with if s there’s really no longer a need to use nested if functions anymore instead you could simply use if s and i think you’ll find that it’s a lot easier moving on to the next worksheet we’re going to look at look up functions and this allows you to look things up so here for example i have a table with cookie types the associated price and the associated cost and let’s say i have the question well what is the price of an oatmeal raisin cookie so to look that up i would say well oatmeal raisin well here’s oatmeal raisin and oh here’s the price so i just looked that up but we can have excel do that for us using the function vlookup here once again let’s click into the function helper and i’ll type in vlookup and here it is let’s click into that one here first it asks me what is the lookup value so what do i want to look up well i want to find the price for oatmeal raisin i will select that then it asks me well what is the table where you want to find this well this is my table so let me select the table and then it says well what is the column index number what is a column index number well that’s what column do i want to get back when it finds a match so here it looks in this table it’ll look at the first column and when it finds a match here i want to get the price back and the price is the second column of this table so i’ll enter a 2 here now for range lookup i could have an exact match or a partial match now i want just an exact match so i’ll type in false but once again this is optional so you don’t have to fill this in it’s true if it’s omitted i’ll click on ok and here see the price of oatmeal raisin is eight now here i could type in sugar cookie and here you’ll see that the price is five so here it automatically does the lookup for me now right down below there’s another function called x lookup and this replaces vlookup it’s a newer function that’s available in the newer versions of excel and it has some key advantages over vlookup but first let’s do the same example and see if we get the same result once again let’s open up the function helper and here i’ll type in x lookup let’s see if it shows up and there it is here it asks me what is the lookup value and that’s what i want to search for and i want to search for sugar cookie and then it says well what is the look up array and so this is a little different from vlookup i want to look in column a for sugar cookie here i’ll select column a but i could also select these values but i want to select the column and then it wants to know well what do you want to return and i want to return the price here i have some optional parameters like what if it’s not found do i want to give an error message back i have match mode here if i scroll down you’ll see a few other options so we have a few more options compared to vlookup i’ll click on ok and here we see i get the exact same thing back and it works the same way here i could type in chocolate chip and you notice that i get the price of 10 back so why would i ever use x lookup compared to vlookup well let’s look at some of the advantages what if i wanted to get the price back and i wanted to get the cost back once again let’s click into the function helper and for the return array right now i have it set to just give me the price back but here i can highlight these two columns and here i get two values back i get both the price and the cost so i can get an array of values back also let’s say that the cookie type column was the third column and price and cost came first vlookup wouldn’t work anymore when you use vlookup the column or the value that you’re looking for has to be the leftmost column and then the values that you return have to be to the right of it but with x lookup you can return any column even if it’s over to the left next i want to show you some date and time functions and these are really simple they’re actually functions that you don’t even pass any arguments or parameters into so let’s say you want to get today’s date back you simply type in equals today open and close parentheses and the reason why is you’re not passing any values in when you hit enter you get today’s date back and down below you can also type in now it works the same way as today except instead of just getting today’s date back you also get the current time back here we are on the very last worksheet you’ve made it just about to the end and so i wanted to give some bonus tips of how you can work with formulas and functions on this sheet i have a number of different numbers and some of them have formulas and some of them are just the number on its own here you’ll see it’s a mix of both so how do you quickly identify all the formulas and all the numbers well you can press the shortcut key control together with the tilde character and that’ll automatically show all of the different formulas and functions on your sheet here you’ll see that i have a few different formulas and here i reference a few different cells and i can press ctrl tilde again and i can toggle back and forth between those views so that’s a nice way to just view all of your formulas now let’s say you just want to see all of your formulas and maybe you want to highlight them let’s click on the home tab up on top and over here let’s click on editing and then here we could click on find and select here you’ll see the option for find formulas i could click that and that will highlight all the formulas on my sheet and now here i can apply some color if i just want all of my formulas to stand out and once again if i press ctrl tilde once again you can confirm that these are all of the formulas on the sheet now one more thing i want to show you when i click into this cell you’ll notice that it references two other cells and when i click into this cell this also references other cells so let’s say that you’re troubleshooting a function and you want to find all the precedence or you want to find all of the dependents you could very easily do that here with this cell selected i could click on formulas up above and here i could trace all the precedence so this cell relies on these two other cells but here if i go back to formula here i can trace the precedence again and it looks like this cell relies on these two other cells to get this value so this is going to help you as you’re auditing all of your different formulas here if i click down into this cell once again i can click on formulas and here i can trace all of the dependents and this is the dependence if i want to undo all of this here i can click on remove arrows but a nice little bonus trick to help you audit all of your formulas alright well hopefully now you are a pro at formulas and functions in excel to watch more videos like this one please consider subscribing and i’ll see you in the next video [Music] you