Excel copying formulas to other cells - excel relative reference excel absolute reference 2013 2016

uh next I want to show you how to copy your formulas to other cells uh this next feature is really really going to save you a lot of time in when we're building our spreadsheet uh so if you don't know how to build uh basic formulas you should go back to watch uh the two videos on basic formulas so anyway uh you can see in this cell that we're taking C4 and adding $300 well let's say that the rest of the February column is going to be the corresponding January Colum column plus $300 what we can do is just copy this formula all the way down the column now the way we're going to do that is with something that's called a speed fill and there's going to be another video uh about how to use the speed fill in more detail so uh what I'm going to do is I'm going to go to the bottom right hand corner of that formula and I'm going to get this black cross and then you're going to hold down the left Mouse button and then drag it down now if you'll notice each number in column D is $300 plus the corresponding number in column C uh now let me show you what happened this is really important that you get this the original formula said equal C4 + uh 300 when I copied it down to D5 it changed that to say equals C5 plus $300 when I clicked on um when I clicked on D6 it says equal C6 6 Plus $300 so we were able to type in that formula one time and then because of the speed fill we were able to just copy it right on down and it changed each cell relative to that formula that's called a relative uh or relative to that cell we call that a relative formula so we're going to use that Concept in our spreadsheet quite a bit in fact watch this let's say that March is going to be a $300 increase over February and April is going to be $300 increase over March and so on and so on what we're going to do is we're going to copy that formula all the way across very easily so I'm going to get the black cross over here uh in the corner of um of D4 actually before I do that I'm going to highlight this column using the white cross so uh there's another video about highlighting the cells but I'm going to uh use the white cross and just drag that down now I'm going to get to speed fill and I'm going to drag it over to column e f g and H and I'm going to lift my finger now notice that each of these numbers is $300 more than the previous column so uh we can see 8300 is $300 more than the 8,000 and so on so let's see what happened to the formula over here now it says equals F4 plus $300 this one says equals G7 + $300 so we were able to type in that form formula one time over here and then because of the speed fill we were able to um copy that formula very very quickly and then each cell got adjusted the formula relative to that cell as we can see uh so that's called a relative formula and uh when we do that speed fill it makes you it helps you fill in your spreadsheet very very quickly now I don't care if you're a whiz at a calculator there's no way I mean there's no way you could have copied those that quickly but with the speed fill we can uh so once you have that concept we're going to use that over and over again so for example over here we did the uh the total the way we got that particular total is with this uh the auto sum over there now I could go to this uh total in column D and do another um another Auto auto sum and do that for each of the columns but here's a quicker way I'm going to click on uh the total for column C notice how it says sum C4 for C11 I'm going to get the speed fill and just drag it right on over and now I have the column totals for each of those other columns so notice that this one now says equals D4 uh through d11 this one now says equals E4 through E11 so we were able to uh T use that formula one time and just do the speed fill and quickly fill in our spread spreadsheet so if you look at this cell over here it says equals C4 + 300 of course that formula can be as long as you wanted it to be but uh then we did a speed fill through throughout that column and throughout the rest of the columns so that all of those would be the previous sell plus $300 it's called a relative formula and the speed fill really helps uh speed up entry into your data uh into your spreadsheet let's try a row total now I'm going to go over to I3 and maybe in that case we'll have uh the word totals and that'll be our column total I mean that'll be the row totals now the um the the to make a row total it's going to work work the same way as the column total you're going to use the auto sum over here I'm going to click on that now notice how this time the auto sum did the row total instead of the column total usually it guesses properly what you want to do there so that that it has some intelligence built right in now if you need to pick a different range you can hide it a different range at this point but quite frankly usually it does pick the proper range that you want to sum up I'm going to hit the ENT K now notice how we have the row total it says sum 34 through H4 now what I'm going to do is I'm going to get the speed fill and drag it all the way down to the bottom to row 12 now uh now I have the row total for each row and and I'm going to uh see the zero here that's because all these are blank so I don't really need to see the zero I'm going to delete that by clicking on that cell and hitting the delete K now here in i12 I actually have a grand total because if you look at the formula it says sum C12 through h12 so Bally basically that's the sum of all the other totals that's going to give us a grand total and now yeah you um very very quickly we are able to fill in the spreadsheet because of the speed fill the auto sum and then uh you know another speed fell over here and you know all of a sudden now we have row totals column totals and Grand totals so you can start to see how we can copy our formulas and uh fill in your spreadsheet very quickly