Excel formula Basics Tutorial for Beginners - Excel 2007, 2010, 2013, 2016

today we're going to talk about a very important feature in Microsoft Excel uh doing some basic math uh so we're going to start right here at the beginning and rule number one all formulas start with an equal sign so anytime that you want to do math in the Excel spreadsheet you're going to start by typing in an equal sign so I want to click here on Cell C3 and I'll type in um equal 76 + 54 they always start the equal sign and you want to avoid spaces in most cases so I'm going to go ahead and hit the enter key now notice how the spreadsheet will give you the result of the formula and the formula will be up here in the formula bar so this say equal 76 + 54 you did the math and you can see it put it right there in your spreadsheet so let's try a subtraction I'll say equal 98 minus 65 so we have equals 98 minus 65 I'm going to hit the enter key and it gives us the result there as you can see so uh notice how the formula always starts with the equal sign so now let's take a look at rule number two after you type in the equal sign you can either type in the number that you want like we just did or you can click on the cell on the spreadsheet that contains the number that you want to use so let's try two different examples here I'll say let me add those two numbers together I'm going to say equals 130+ 33 and it's going to give us the answer of 163 and that is correct but let me show you a different way we're always going to start with the equal sign now follow my mouse I'm going to click on the cell that contains the 130 which is cell C3 in this case then I'll type in the plus sign then I'll click on the 33 which is really sell C4 now the formula says equals C3 + C4 now that's still going to give us the 163 but watch what the difference is going to be this is going to be really important so I'm going to go up here to the 130 and change that to uh 225 now when I hit the enter key notice how this 163 did not change Chang because those numbers are hard coded in there this one changed because the formula is looking at cell C3 and cell C4 if either cell C3 or C4 changes like it just did then that's going to recalculate so that's really the better way to do your formulas watch again we're going to start with the equal sign now you could type in the number or if the number is already on your spreadsheet somewhere you're going to click on that cell that contains the number that you want which in this case is cell C3 by the way you could have typed in C3 but I find it easier just to click on that cell and I'll type in the plus sign and then I'll click on the cell that contains the number 33 which is really cell C4 so that means use the numbers that are in cell C3 and C4 and the reason that you do it that way is if one of those numbers changes then notice how the form we calculate so that's very important now let's take a look at rule number three you're going to tie things together with your mathematical operators which are listed over here and the formula will follow the mathematical order let's see an example of that so I'm going to type in equal 4 + 5 * 6 now times in uh Excel is going to be the asterisk as this listed over here it's going to be the asterisk so if you didn't know what I was about to tell you you would think it's going to take it left to right you would think it would do the four + 5 first which is 9 time 6 you would think it's going to give you 54 but when we hit the Anarchy it doesn't say 54 it says 34 so let's see what happened with that it doesn't take the math in the mathema in in the left to right order it does it in what we call the mathematical order and I wrote the these down in the mathem itical order so it's going to handle anything in parentheses first then the next one is going to be exponents and the exponent is like two to the 4th power the way you do an exponent is with this up arrow and it's going to be a shift six on your keyboard so if we look at this example here it says equals 2 and the up Arrow four uh I believe the the word for that up arrow is called a carrot the way you get to that is by doing a shift six on your keyboard and notice how that would give us 16 um then the next one is going to be multiplication which is the asterisk uh equals 7 * 8al 7 asterisk 8 is going to give us 56 division is going to be the slash here the slash is on the question mark key so if we say equals 99 SL1 it's going to give us the result of nine addition of course is the plus sign and subtraction of course is the minus sign so it's going to follow this mathematical order now if I want the addition to have first here then we're going to use the parentheses so I can either change the formula up here in the formula bar or you can also double click on a cell if I double click on a cell then I can see the formula that way as well either way it's perfectly fine now I'm going to type in a a parenthesis before the four and a Clos parenthesis after the five so because of the parentheses it's going to do the addition first you see according to the mathematical order parentheses anything in parenthesis is going to go first so now it gave us 54 instead of the 34 all right so when you have a a longer mathematical expression that has more than one mathematical operator it's going to follow the mathematical order and the way you control it is you put parentheses around the part that you want to go first so here's the mathematical order parentheses exponents multiplication division addition and subtraction and then I showed you some examples over here so this is a really good screen that print out perhaps like take a screen print of this and print it out uh because it has the rules that you're going to follow for every mathematical formula and also the mathematical operators in the mathematical uh order here are some examples of the different mathematical operators here's the results of what they would give what they're going to give you if if you typed it into a cell now there's a way to remember the mathematical order and I learned this a long time ago and as I go around the country teaching these classes many people learned it this way as well so we have P for parentheses e for exponents M for multiplication D for division s for uh a for addition and S for subtraction so notice how that's the first letter of each of the words in column F and then here's the phrase that they gave you to help you remember it please excuse my dear Aunt Sally right so what possibly you could have and Sal you have to do with Excel well it helps you remember the order notice how those words start with the same letters of the abev abbreviations from the other words so it's just a way to memor the mathematical order when you have a longer mathematical statement which many of you will have then that's going to become really important for you so rule number one they always start with an equal sign no matter what the formula is even the more complicated ones always start with an equal sign rule number two after you type in the equal sign you can either type in the number that you want or you can click on the cell that that uh contains the number that you want to use like we did over here equals C3 plus C4 right that's going to use the numbers that are in cell C3 and cell C4 so if they change then the formula will we calculate and then rule number three you're going to tie things together with your mathematical operators and some of which are over here there there's more than that but those are the ones you're going to use most of the time and the formula will follow the mathematical order which is the order I wrote this down in the way you remember the order is please excuse my dear Aunt Sally and um when you want something to go first in the formula you're going to surround it with parentheses like we did over here so that show you the basic premise for all of your mathematical formulas in Microsoft Excel no matter how complicated or how simple they are all right so the next thing I want to talk about is how to do percentages I'm going to type in uh 800 here and I like to get 10% of that well you can do 10% one of two ways I can say equals 10 and type in the percent sign times remember how times is the asterisk right and I'll click on the 800 equals 10% time C10 which is going to give me 10% of that number and it says 80 now the other way to do percentages would be like equals this number times 0.1 0.1 is the same thing as saying 10% both of those mean the same thing and they both say 80 as you can see take a look at the first one here it this equals 10% I'm using the percent sign time C10 and take a look at this one it says equals C10 * 0.1 0.1 and 10% mean the same thing so if I change that number to say 1 1200 both of those are going to change because they're both pointing to cell um C10 of those examples all right so this is a really good screen if if you're uncomfortable with this you should watch that part of the video over again and also you should uh practice you know practice some simple formulas and then start to add some U uh you know more complic uh complicated arithmetic and then the way you can control the uh order is with the the parentheses but you want to see what starts to happen with these formulas now let's put it into a real life practice so I'm going to go over here to a different sheet of this workbook okay so here is a simple budget and uh actually I want to change this to company budget 2016 because that's when I'm recording this that's not a problem so actually uh now that I thought about I want to leave that back in 2014 there'll be a reason to do that is another example that's coming up all right so here I have column A which shows the name of the Departments and across column um across Row three I have the names of my months by the way let me show you how I did that uh I'm going to erase these I'm just going to highlight those cells and then hit my delete key on my keyboard I didn't type in all those entries where it said January February March when I did this a long time ago I typed in the word January I'll even do it again I'll type in January Now watch how I'm going to make it with all the other uh month names so I'm going to click on the January I'm going to get the black cross in the corner you have to be right on the corner so that's going to be um called the speed fill you can either drag it to the right or you can drag it uh down so when I get the black cross I'm going to hold down my left Mouse button and just drag it over as far as I want to go and notice how the month names are now there watching again I'm going to undo that I typed in January I going to get the black cross you have to be right on the corner and then hold down your left Mouse button and drag it over and now you can see the rest of the month names uh so that also let me show you some other things that that can work with here's my basic math again but in this sheet I'll type in January I'm going to make this bigger for you uh I can zoom in over here in the corner as you can see so I'll type in January I want to show you that we can also drag it down so I'll get the black cross and drag it down it works that way as well notice how when we got to January it starts again with I mean when we got to December it starts again with January so it works that way so it also works with days of the week like Monday and I'm going to click on that and get the black cross and drag it down it also works with numbers now with numbers we have to do something special I'll type in one I'm going to click on the one I'll get the black cross in the corner and we'll drag it down now they all say ones right and that's not what I wanted to do so watch what you do when you do a speed fill you get this little box at the bottom so you click on that box and you say fill series and then it'll say one 2 three four five it also works with dates now I'm going to do um I can type in today's date or here's a keyboard shortcut I'll do control semicolon control semicolon gives me today's date and and that is the date that I'm recording this video so at this point control semic col gave me today's date now if you try that and if it gives you a different date than what you're expecting it's because your computer clock needs to be reset but usually control semicolon will give you the current date I'm going to get the black cross in the corner of that cell and drag it down and now we have the rest of the dates now check this out with the dates we could do something special as well so uh it turns out that the 19th is a Thursday and the 20th is a Friday Friday so the 21st and 22nd are Saturdays and Sundays well what if you just want to show this uh uh representing work days or weekdays so I'm going to go ahead and click on this box at the bottom of the um speed fill and then we'll say fill weekdays see that and then watch what happened it took off the weekend so it went from the 20th to the 23rd the 27th to the 30th and and so on so that was kind of interesting I clicked on that pull down at the bottom this speed fill and I said fill weekday the speed fill also works with um let's say I want a team one this could have been any word I could have said schedule one or person one or anything f with the number one and we'll just drag it down and just number that series for you right so those are all great examples of the speed fill now let's go back to our sheet so that's how I had it when I first did the spreadsheet that's how I made it say January February March it's just a quicker way to do it now notice how I have some numbers in column C first of all let me show you a really quick way to add up some numbers we're going to do something that's called the auto sum so I'm going to click you're going to click on the cell where you want your sum to appear usually at the bottom of the column or on the right side of a row and then we'll pick on the home menu up top home now on the home menu you come way over here now this icon has been there forever but now it says the word Auto sum so I'm going to click there and notice how it's going to put a formula and usually it's the correct formula it says equal sum C4 through C10 when you see that colon that represents a range of cells and the way you read that is Su C4 through C10 and that is what I want to do so I'll hit the enter key and it gave us the result so watch again I'm going to hit my delete key on my keyboard I'm going to click on the cell where I want my total to appear and and I'll pick on the auto sum and in most cases it's going to guess the right formula for you uh so I'll say equal sum C4 through C10 and then it has the right answer now if I change one of those numbers let's change it to say 17,000 then notice how the bottom number recalculated which is really great so let's say in February I want to have a 10% I I'm hoping for a projection of 10 % increase over January so it's going to be another formula right so I'll type in the equal sign remember all cells all formula start with the equal sign now I'm going to click on that number in cell C4 and it says C4 in my formula by the way I could have also typed it in I just find it easier to click on that cell then I'll say times which is the asterisk 1.1 so it says equals C4 * 1.1 which is really going to give us a 10% increase I just hit the enter key so 10% of 17,000 would be 1,700 and then you add that on top of the 177,000 and that's why we get 187 so that's a 10% increase over that number so the reason I do my formula that way is if this number changes to say 16,000 two things are going to change here the number down here is going to change and the number over here here is also going to change because both of those formulas are pointing the cell C4 so if C4 changes they both update as we can see now I want to show you something very very important that's called relative formulas or relative references so in this case I want the whole rest of the February column to be a 10% increase over January so I let the copy that formula all the way down here's the quick way we're going to get the black cross in the corner of that cell and I'm going to drag it down I'm going to hold down my left Mouse button and drag it down to the rest of the column now it turns out that each one of those numbers is a 10% increase over the corresponding number in column C but we have to see what happened to the formula the first one that I typed in was C4 * 1.1 you can see that up in the formula bar but when we copied it down it changed now this one says equals C5 * 1.1 the next one down says equal C6 * 1.1 that's called a relative formula because it changes relative to the new cell the power of that was I already had to type in the formula one time and then I was able to get the speed fill and just drag it right on down and it made the adjustments for me that's called a relative formula that's going to save you a lot of time because I just had to type in the formula once and just be able to copy it down and it made the adjustments for me when you see most people larger spreadsheets it's hardly ever the case where they would type in each formula one by one whenever they can they do that they they type in maybe the top one and drag it down and they it makes the adjustments for them now let's do that again once you learn that technique you're going to use it over and over again in your Excel spreadsheets so let's say March is going to be a 10% increase over February and April is going to be a 10% increase over March and so on so I'm going to go ahead and highlight these cells now when I highlight I'm using the white cross right here's the white cross I just dragged it down now I'm going to go ahead U and get the uh the black cross in the corner and then I'm just going to drag it over let me get the black cross again there it is and I'm just going to drag it over to the July column now each of those numbers turns out to be a 10% increase over the number two it's left so let's see what happened to the formula this one says C4 * 1.1 that's the one that we typed in the next one over says D4 * 1.1 the next one over says E4 * 1.1 notice how it's always the cell to the left so I was able to type in that formula one time and then I just dragged it down with the black cross and then I just drag that all the way across and it changed all those form this formula that's called a relative formula it's going to really save you a lot of time instead of typing each one of those in one by one uh now once you learn that technique you're just going to use it over and over again let's come over here this cell I made originally by using the auto sum up here now I could do the auto sum for each one of those right across and that'll be fine but the quicker way is to use the speed fill or the autofill I'm going to get the black cross in that cell and I'm just going to drag it over to column I and now we have the column totals for the rest of the columns now when you see the pound signs in Excel and you're going to see them all the time that means you have to make that column bigger that's all that it means whoops I'm sorry I called pound signs you might call them um you know number signs or hashtags right they have different names but uh it just simply means we have to get make the column bigger so here's how to do that I'm going to move my mouse between letter D and letter e and I'll get the black cross right there now you could drag the black cross back and forth but the quicker way is to double click on the black cross so I'm going to double click and notice how that made the column bigger and that makes the pound signs go away so you move your mouse between the two columns and then you double click the uh the black cross now let's do all of these at the same time I'm going to start at column e I'm going to highlight all the way over the column I so they're all select it I'm going to go between letter H and letter I and get the black cross and double click and that made all those columns bigger which means the pound signs went away now back to the point that we were making with the speed fill this one said sum C4 through C10 when I dragged it over with the black cross then this one says sum D4 through d10 that's a relative formula made that adjustment automatically this would says sum E4 through E10 and so on right across so that's really going to save you a ton of time now let's try another Auto sum I'm going to click on the this cell right here clearly I want to get the sum of the numbers going across well we can use the auto sum for that as well so I'll click on the word Auto sum on the right side of the home menu now this time it guessed it properly again usually it does it says equal sum C4 through I4 and that is what I want to do now if it doesn't get to if it doesn't get the right form um cells for you then you can just highlight what you want to do so if that didn't guess the right thing I would click on Cell C4 then just highlight over and then it'll just do formula you do that before you hit the enter key so I'm going to hit the enter key at this time and now I have the sum for that row Watch How I did that again I'm going to delete that I'll pick on the auto Su usually it guess is the right thing for you but if it doesn't you're going to click on the cells and highlight the ones that you want to actually sum as you can see and then it'll it'll come out correctly now this formula says equals sum C4 through I4 well let the copy that right on down again so I'll get the black cross and drag it down now I have a sum for all the rows remember how the pound signs mean we're going to make that column bigger so I'm going to go between letter J and letter K get the black cross and double click and you can see how that made that uh bigger and now we have the grand total for the spreadsheet as well now if I look at this cell right over here it has a zero because it's it's summing everything in in row 10 and there's nothing there so it gave a zero to me that zero is extraneous so I like to get rid of that now I want to hit my delete key on my keyboard to get rid of that as you can see some people use a space bar to get rid of a cell but uh that doesn't really make it officially empty I would prefer to use the delete KY now if you ever delete the wrong thing of course you can always undo uh undo is way up there on the top and the nice part about undo is you can keep on undoing to the last time that you your document uh it's such a great tool undo now if you undo too far and you want to redo what you undo then we also have redo right so we have undo and redo up there so make sure you know about those and use those when you can now I have uh column totals row totals and even the grand totals so here we were able to use that formula and then copy it right across using the speed fill and then I did the auto sum there and I copied that right across I use the auto sum right over here and I copy that down so watch this video over and over again until you can do the same kind of things that I'm doing here with a little bit of practice you will definitely get there now I talked about a relative formula now I'd like to talk about something that's called an absolute reference these come up as well so I want to calculate the percent of total now the percent of total is calculated by taking the row total for that row and dividing it by the grand total all right but when I copy the formula down I want each one of those to refer to that same grand total so first I'm going to do it the wrong way I'm going to show you why it's wrong and then we'll do it the right way but this is a trap that we all fall into when you're first learning Excel or even after a while right A lot of people still do this so want to start the formula of course with an equal sign I'm going to click on the cell that I want to use which is cell j4 and I'll hit the slash for division and I'll click on the grand total which is cell j11 so the formula says equals j4 / j11 and it says 14.95% now by the way uh I had previously formatted this column as percent the way I did that was I highlighted that whole column and then I picked on the percent sign up here thought to do it again I put the hit the percent sign and now I like to add some decimal places which is uh this icon over here can add some uh decimal places so I'm going to click there once and then twice so another format as percents but let's go back to to the formula it says equals if you look at the formula over here it says equals j4 / j11 you would think that I can just copy that down like I've been showing you so I'll get the black cross and drag it down now let's see what happened here it says division by zero why is it trying to divide by zero well this is the point that I'm trying to make so if I double click on this form on this cell it will show me the Formula it says equals j4 / j11 that's the one that I typed in uh I'm going to hit the Escape key to get out of that cell now I'll double click on this one and it says equals J5 / J12 you see it's still a relative formula so it moved the whole formula down by one cell but there's nothing in cell J12 that's why it says division by zero however I don't want to divide by J12 I still want to divide by j11 so somehow we have to tell it that when I copy the formula down to not change that part of the formula this one's getting even further away this one says j6 / j13 except there's nothing in j13 that's why it says division by zero so somehow we have to tell it that when when I copy that formula down not to change that part of the formula to keep it uh static or absolute or constant okay this is when we start to use the dollar signs in our formulas so let's take a look I'm going to double click on that cell and you can can type in the dollar signs or I know that's another way which I can show you soon as well so right before the the J I'll type in a dollar sign and right before the 11 I'll type in another dollar sign now when you see the dollar signs in your formula we are not talking about money what that means is that part of the formula is an absolute reference which means when we copy it down it's not going to change it's going to stay it's still going to say j11 because of the dollar signs but you have to see it to appreciate it I going to hit the ENT key there now the dollar signs don't make a difference until I copy it down again so I'm going to click on that cell I'm going to go ahead and get the black cross and we'll drag it down so notice now they have real numbers this time so let's see what happens this is the important part this one says j4 divided by dollar sign J dollar sign 11 this one says J five but it's still dividing it by j11 because of the dollar signs that part of the formula did not change it remain constant or absolute this one says j6 and it's still dividing it by j11 because of the dollar signs the first part of that formula is relative here it say j4 and then the next one says J5 CJ5 and then the next one says j6 so the first part of that formula is relative the second part of the formula is absolute they all say j11 going all the way down because of the dollar signs so watch what I did again let's make sure we get this this is one that you're going to use all the time I'm going to start from scratch here so right up here we'll say equals this number divided by this number and it says equals j4 / j11 when I copy it down right now there's still relative references so the j11 turned to J12 and then j13 and j14 and so on however I always want them to say J1 so I'm going to click I'm going to click on that cell I can also type in the dollar s up here in the formula bar of course either way it's perfectly fine now make sure when you do that you recopy it down so I'm just going to copy it down again and now they're all pointing to cell j11 because of the dollar signs we call that an absolute reference so make sure you watch that part of the video over again because uh really is a a technique you're going to use all the time let's do another example of that I'm going to type in something here that's called tax rate now I'm recording this in in Pennsylvania in the United States and the sales tax rate is 6% actually that's not true I'm I'm recording it in Philadelphia Pennsylvania Philadelphia Pennsylvania the uh sales tax is 8% it's really high you know so uh here I'll type in sales tax so I want to take the number for that row which is here and multiply it by the tax rate however I want all of them to point to that 8% so watch how we're going to build the formula I'm going to start with the equal sign I'm going to click on this number here I'll do the asterisk for multiplication and I'm going to click on the 8% now when you're on the part of the formula that you want to make an absolute reference you can type in the dollar signs but a quicker way is the F4 function key so I'm going to uh on my keyboard on the top row I'm going to type in F4 and notice what that did that typed in the dollar signs for me so the F4 is a shortcut to do the dollar signs which makes it an absolute reference so now it says equals j4 * dollar sign L dollar sign 2 let's go ahead and copy that down now and I'll get the black cross and drag it down now now let's see what happened to the formula this one says J5 time dollar sign L dollar sign 2 this one says j6 time dollar sign L dollar sign 2 the first part of the formula is changing from cell to cell that's called a relative reference the second part of the formula is not changing they all say L2 because of the dollar science so let's say in this case let's say uh the next mayor of Philadelphia is going to lower to down to 3% that would be that would be very great I would welcome that in a big way it would get a lot of votes so I'll type in 3% watch column L they're all going to change because they were all pointing to that sell because of the dollar signs let's say the mayor changes to 3% the city really needed those revenues who knew so let's change it back to 8% and now watch column l they all changed because they're all pointing to the dollar signs I mean they're all pointing to L2 because of the dollar signs so that was a relative reference whereas the ones over here this says F4 * 1.1 the next one says F5 and then F6 if you look in the formula those are called relative references so that was a very important part of this segment now another thing that people ask me is uh how can we make make it so that if we update one sheet another sheet will also update at the same time how can we link our sheets together let me show you two ways to do that the first way is the following we're going to copy from this sheet then we're going to go to a different sheet and instead of just saying paste we'll say paste special and I'll show you why so here I am on sheet 2014 and I'm going to highlight these numbers over here and you can highlight as many as you wanted to and I'm going to go ahead and do a copy just a normal copy and in Excel it's going to highlight what cells are being copied now I'm going to go to sheet 2015 and you can see let me make it bigger for you uh down here is the zoom in the corner so I'll make it a little bit bigger so you can see what's going on that actually made you made the uh pound signs happen but we know what to do about that now right want to highlight those col colums I'll go between two columns to get the black cross and then double click the black cross that makes the pound signs go away but notice over here I have a space where uh let's see if my my items are still highlight and they are okay so now I did a copy I'm going to come over here I'm on sheet 2015 I'm going to right click on that cell we'll pick on the word paste special I'm going to click there and now it'll open up a different window and over here it says paste link paste special and then paste link now notice how it now has the numbers from sheet 2014 but more importantly look at the formula it says 2014 that's the sheet name the exclamation point separates the sheet name from the cell so this really means that if Cell C11 changes on that other sheet this number will also change to the same time they're now linked together uh so I'm sure this one says 2014 d11 as you can see in the formula so the sheets are now linked together because we did a copy from one sheet we came over here and we said paste special and then paste link now I'm going to go ahead and highlight these cells and I'll tell you what uh i' like to get the number format from the other sheet as well so I'll do another past special I'm going to goe and right click on those cells and we'll pick on Pace special but this time I'll pick on the formats which is going to be the number formats why it doesn't do it in one step I'm not really sure but I'll just do a pay special again with the formats I'll click on okay and now the number formats came over as well so it's telling me that in sheet 2004 uh 2014 column C has a total of 107,000 let's see if that's the case I'll pick on sheet 2014 and you can see it says 107,000 there now when you have those blinking cells like that I'm going to hit the Escape key on my keyboard to get rid of that I just hit escape and they go away so if I change this number a lot of things are going to change this column total is going to change the row total over here is going to change the percents are going to change a lot of things are going to change here um because of our formula so I'll type in 20,000 now before I do that watch the bottom number here this is 107,000 in fact everything is going to change now it says 111,000 and I'll change that 15,000 to say 18,000 now the new Total says 114,000 as we can see let's see if it changed on sheet 2015 and you can see that it did all right so one way to link your sheets together is to do a copy highlight yourselves and then do a copy from one sheet then you go to the other sheet and you say paste special and then paste link by the way that'll work between two different workbooks as well you can copy from one workbook go to a completely different workbook and say paste special and then paste link that way if you change the first workbook the second one will also change even if the second one isn't open at that time uh you know the next time you change it it'll the next time you open it it'll have the newest numbers because of U paste special and then paste link now here's the second way to link your sheets together here I'm going to type in Grand totals uh so I'll type in uh 2014 Grand totals so in this case I'm going to start the formula on this sheet with an equal sign like we've been talking about now as you're building your formula you're going to click on a cell on another sheet so I just typed in an equal sign now I can go down to another sheet like 2014 and I'll pick on the grand total there so I'm still building the formula in the other sheet by the way so notice the formula up here I can I can do further math from there I can do whatever math that I needed to but uh when I hit the enter key it's going going be bring me back to the other sheet because that's where I started the formula so I'm going to hit the enter key so notice this formula it says equals the sheet name and the exclamation point and cell J 11 so in that method I started the formula on one sheet with the equal sign then as I was building the formula I I clicked on the cells on the other sheet and that would be another great way to tie things together so for example I'm going to go to sheet 2014 now that number said 108,000 uh we can format that like a with the uh commas of course um of course I can make that larger now okay so actually was a a million 81,000 so I'm glad I have the commas there so I'm going to go ahead and and click on sheet 2014 and again if I change this number the column totals will change and that'll flush out to the grand total so I'll type in 25,000 and you can see a lot of the numbers changed now the total is 1, 128,000 if I go to sheet 2015 then we have the same number there as well so two ways to link your sheets together one is the copy from one sheet and then go to the second sheet and say paste special and then paste link and the second method to link your sheets together is to type in an equal sign on one sheet and then as you're building your formula you click on cells on the other sheet so in this uh in this spreadsheet in this video I showed you the basic math those rules are always going to apply no matter how complicated the formula is there's the rules again and then here's the mathematical operators and it's going to follow the mathematical order the way you can remember the mathematical order is uh please excuse my dear a s because it's the first Noti how it's the uh the same first letters of of these words over here uh so look at this look at the sheet make sure you practice with that then we start to do some good math over in the other sheet so in this case we did the auto sum I showed you how to do the relative formulas by by typing in the first Formula and then copying it down with the black cross and they change automatically over here we did another Auto sum over here I copied the formulas over because of the speed fill then I talked about the the absolute references those are the ones with the dollar signs so that means if I copy that formula down then um you know they're all going to say j11 there because of the dollar signs and the same thing here with the tax rate and then I showed you how to uh link your sheets together uh in two different ways so that should go a great way in getting your started with your Excel formulas now pretty soon I'll have some more videos about more complicated formulas but this will definitely get you started let's do a real life example one thing that somebody always ask me to do would be like a percent of change from one month to the next month or one year to the next year so I'm going to go to a different sheet now and uh this is where we did our Auto fills let me go to sheet six even okay and I'll make this sheet bigger for you by zooming in so let's say over here I have um year 2015 and I have year 2016 and I'm just going to go back to uh 2015 for a second and I'm just going to highlight these titles so I don't have to retype those where it'll just give us some things to to use so I'm just going to copy those and just paste them right to over here now if I wanted to make that column bigger of course I'm going to go between letter b and letter C get the black cross and then double click like we've been talking about all right so let's say in 2015 again I'm just making up numbers so I'll have uh 10,000 12,000 18,000 let's say the computer Department gets 30,000 they should always get more money I'm just kidding and then their warehouse should have 14,000 and the accounting department gets 15,000 now let's say in 2016 they had 12,000 uh they had uh 15,000 research and development usually gets a lot it get them 22,000 say computer the department got 40,000 that year maybe the warehouse went down for some reason and maybe accounting uh stayed the same so over here I want to type in the difference and then pretty soon we'll have the percent of difference percent of change so the difference is really a basic mathematical formula say equals I'm going to click on this cell which is D3 minus this cell so I have D3 minus C3 and we get the difference clearly that's uh off by 2000 and we're just going to copy that down with a speed fill now I have the difference of each one of this notice how the warehouse is a negative and that's correct and uh the eony didn't go down or didn't change at all so that was just a basic formula we have equals D3 minus C3 now let's get the percent of change percent of change now the percent of change is going to be uh it's very important to take the difference divided by the the difference divided by the first number not by the second number so I'm going to say equals this number divided by this number all right so it's the difference divided by the first number not by the second number it's really important that you see that and here we have 0 2 now in a minute we'll just do some formatting to the spreadsheet so I should be able to copy it down now let's see what we have uh so now I like to really format these to look a little bit better so I'm going to go ahead and highlight those numbers and I'll click on the percent sign up on the home toolbar and now it looks a whole lot better and if I wanted to add some decimal places then we can use this icon right over there good now they might say can you make the ne negative numbers in red or something like that so what I would do about that is I would highlight all these numbers with the white cross and then we're going to use something that's called format cells I've been using this for so long I'm going to right click on those numbers first of all actually uh we'll just do um we'll just do these numbers because the percents will be different so I want to highlight these numbers and I'm going to right click and in this case we're going to go back to something that's been around for a long long time it's called format cells this gives you a lot more formatting options so um I'll pick on the number Tab and you see different number formats in this case I'll pick on currency and notice how I can change the number of decimal places here if you're working uh in different countries of course you might have different uh currency symbols and then your negative numbers can be shown either with a minus sign or in red or in parenthesis or red and parenthesis so check with your accounting department and ask them how they want to see their negative numbers now what they're going to tell you is they don't want to see negative numbers at all but when they do display how this going to display so we'll make it um let's say with the red a lot of people like the red format for the negative numbers and you can see now the 2000 became red and everything has a nice dollar sign there let's do the same thing on this column so I'm going to hide at that column if they didn't already have percent signs we can do this as well go back to format cells after I right clicked I'll pick on percentage and then here you can change the number of decimal places as well as you can see all right so I'll click on okay and now those came up as well now if I wanted that number to be in red um I could just format that one in red uh by itself or we can even do something that's called conditional formatting uh which might be in another video but anyway so you can see now I have the 2015 total the 2016 total to get the difference I just subtract it I said equals D3 minus C3 that gives us the difference and this one show showed up with um you know a negative number because it's in red there to get to percent of change I started another formula and I said equals E3 the difference divided by the first number C3 you don't want to divide that by the second number you want to divide that Difference by the first number E3 divided by C3 and then we can see the percent of change uh so that was a a halfway decent example of doing some math in Excel now what I might do is add a total at the bottom here by doing an auto sum and then I'll get the total for this one as well by doing an auto sum and then of course I want to format those the same way so I'll just highlight those two cells and we'll pick on here I can just pick on the currency format that would be another way to format your numbers and now they look a little bit better so that was a real life example of doing some math in Microsoft Excel