How to copy formulas, and lock cell references in Google Sheets (Relative & absolute references)

in this video i'm going to show you how to copy formulas in google sheets by using two different methods now in another video i'm going to show you a formula that will allow you to calculate an entire column by using one formula in one cell but in this video i'm going to focus on how to copy individual formulas from one cell to another and i'll also show you how to do shortcuts to apply this to an entire column very quickly so in this first example we have a very simple formula in cell c3 we're using a cell reference to refer to cell a3 so our formula is equals a3 and so cell c3 is showing the same name that is in cell a3 and what we want to do is copy this formula into the cells below it so that the formula will do the same thing for each different name in each row and we're going to do this without having to change our cell references over and over again as the rows go downwards and so again i'm going to show you two different ways to do this the first way is by using auto fill and so i'm going to click on cell c3 which already contains my formula and at the bottom right of the cell you can see that there's a small blue box and i'm going to hover my cursor right in the middle of this small blue box so that a plus sign or a crosshairs appears this is called the fill handle and so now i'm going to click my mouse and hold the click and then drag my cursor downwards until i've reached row 12 and then i'm going to release my click and so now i've just used autofill to automatically copy the formulas down the column and so in cell c3 we were referring to cell a3 in cell c4 the formula has copied downwards but it's automatically changed the reference to the row so that we're now referring to cell a4 and then in cell c5 we are referring to cell a5 now i'm going to show you another way that you can copy formulas and so i'm going to delete the formulas that i just copied i'm going to click on cell c3 and now i could do the same thing that autofill did by simply copying and pasting when you copy and paste a cell that contains a formula in google sheets just like with using autofill the cell references will automatically adjust when the formula is pasted into new rows or new columns so i'm going to hold ctrl on the keyboard and press c to copy cell c3 and you can see that a dotted line has appeared around the cell indicating that that's the cell that i've copied and now i'm going to click on cell c4 i'm going to hold ctrl on the keyboard and then press v to paste and so just like with autofill google sheets has copied the formula into the cell below it and the cell reference has automatically changed and if you want you can do this one cell at a time copy paste press down paste again or you can copy and then highlight the entire range that you want to paste into and then paste and again just like with autofill it's automatically copied our formulas and adjusted the cell references so down here in cell c12 we're referring to cell a12 now before we move on i want to show you a really useful keyboard shortcut that will save you a lot of time sometimes you'll want to copy your formula all the way to the bottom of the column and if your spreadsheet is really long you won't want to wait to drag your cursor all the way to the bottom of the column which again if your spreadsheet is really long can take a long time so here's a keyboard shortcut that will help you copy your formulas down really fast and this is called the fill down keyboard shortcut so what i'm going to do is select cell c3 because this contains the formula that i want to copy to the bottom now i'm going to hold shift and control on the keyboard at the same time and then press the down arrow key and that shortcut just selected all of the cells all the way to the bottom of the column now i'm going to use the fill down shortcut which is ctrl d d stands for down and as you can see that shortcut instantly copied the formula all the way down to row 1000 so here in row 1001 our formula is referring to cell a 1001 you can also do this horizontally by pressing ctrl r r stands for right and d stands for down now you can also use auto fill or copy and paste formulas to copy your formulas left and right and so here's a similar example where we want to copy our formulas to the right so that we are referring to the cells in row 1. so in cell b3 we are referring to cell b1 and we're going to copy the formula in cell b3 all the way over to cell h3 and so i'll quickly show you the two different ways of doing this first i'll use auto fill and i'll hover my cursor over the small blue box at the bottom right of the cell until the fill handle appears which again looks like a crosshairs or a plus sign i'm clicking my mouse holding the click and dragging my cursor to the right until i've reached cell h3 and then i'm releasing my click and just like when we did the same thing going downwards google sheets has copied the formulas to the right but in this case it's adjusted the column references instead of the row references and so in cell c3 we're now referring to cell c1 in cell d3 we're referring to cell d1 e f g and so if you copy a formula up or down google sheets will adjust the row reference if you copy a formula left or right google sheets will adjust the column reference so again you can also copy and paste to copy your formulas to the right so i'm going to select cell b3 copy click on cell c3 and then paste so here's an example of copying the if formula down the column so in cell c3 i have a formula that displays the word passing if cell b3 is greater than 60 or 0.6 if it is not greater than 0.6 it displays the word fail so this formula simply tells us whether the grade was passing or not and i'm going to use the fill handle copy the formula downwards and now the formula has been applied to each row the grades that are failing cause the word fail to show in column c and the grades that are passing cause the word passing to show in column c now i'm going to show you another example where we copy the formula downwards and to the right and i'm going to show you how to lock cell references so that when you want when you copy and paste a formula or you use autofill google sheets will not change the references that you have specified as being locked so in cell b2 i have a formula that says cell a2 times cell b1 cell a2 times cell b1 and what i want to do here is create a multiplication table and so i want to be able to copy this formula to the right and downwards so that the references change just how i want them to so that the multiplication table fills out and in this case there's something special that we need to do to make that happen so when we copy and paste our formula to the right we want to keep referring to column a and we do not want the column reference for a to change and so we'll use a dollar sign before the letter a to tell google sheets not to change this reference as we copy and paste the formula to the right however for the reference to cell b1 we do want this column reference to change as we copy and paste the formula to the right so that we can refer to number 1 2 3 4 and so on now the opposite is also true when we're copying the formula downwards for the reference to cell a2 we do want this row reference to change as we copy and paste the formula downwards so that we can multiply by 1 2 3 and so on for this same number at the top of column b but as we copy and paste downwards for the reference to cell b1 we do not want the row reference to change because again we want to keep referring to row one as we copy and paste downwards and we check to see what 1 times 1 would be what 1 times 2 would be what is 1 times 3 and so on so again in your formulas when you include a dollar sign before a column reference you're telling google sheets not to change that reference as you copy and paste the formula or when you use autofill if you include the dollar sign before the row reference you're telling google sheets not to change the row reference and in some cases you might want to include a dollar sign before the column and the row reference it just depends on what you're trying to do with your formula so now that i have my references set up exactly how i want them i'm going to copy my formula in cell b2 to the right and downwards so that the entire range from b2 to k11 is filled with these formulas so first i'll use the fill handle i'll select cell b2 hover my cursor until the fill handle appears click my mouse and hold the click and drag it right until i've reached cell k2 and then release my click so now i've filled the formulas to the right and before i fill them downwards as well let's go over what's happening when these cell references change so in cell b2 we have a2 times b1 which is one times one then in cell c2 we have a2 times c1 which is 1 times 2 and so the pattern continues because of the way that we set up our dollar signs and so as we go to the right what we're wanting to do is multiply 1 by 3 1 by 4 1 by 5 and that's exactly what we're doing a2 times d1 a2 times e1 so now let's copy our formulas downwards what i'm going to do is select the range b2 through k2 and then i'm going to use the fill handle to copy the formulas all the way to the bottom and so you can also use the fill handle to copy multiple columns of formulas at once and just like we wanted our multiplication table has now filled in now one more quick thing that i wanted to teach you is what these references are actually called when they're locked or when they're not locked or in other words when you use a dollar sign and when you do not use a dollar sign so references that are locked with the dollar sign are called an absolute reference you're telling google sheets i absolutely want to continue referring to this exact location no matter where i copy the formula now a reference that does not have a dollar sign in front of it which is not locked is called a relative reference and again this type of reference will be changed automatically when you copy your formulas or when you use autofill so absolute references are locked and relative references will automatically change when you copy your formulas so if you want to learn more about copying and pasting formulas or using autofill i've linked the article for this video below or you can also check out my video on using the array formula function which will let you extend the functionality of your formulas down an entire column by using one formula in one cell