Copy a cell down without changing references.
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 2145: The DITTO Key.
I love this one.
Alright, so, right here, we have a formula that is totalling those numbers, and if I would come right below that cell -- this goes back to elementary school, where we would say something like, if we would write a word and then, beneath that, we would press the “ key, right, that “ key was saying that we're just…you know, it's the same word as above, right, at least back when I was in 1st grade.
So, it was a great way to not have to write so many letters.
You could just use the “.
So, here, we're going to do the same thing.
CONTROL+SHIFT “ -- in other words, the quotation mark -- and it will bring that formula down as a value, alright, and so I can lock a value in, kind of CONTROL+C, ALT+E, S, V in a new spot, but the place where this is really handy, and I learned about this at General Electric in Cleveland, I was doing my seminar there, and the engineers would use this one all the time because they would have to total something and average the exact same thing, and so I have a total formula here, =SUM, and if you would just copy this down, right, it's going to change the reference.
It’s going to be referencing the wrong thing, alright?
So, that's not effective, but what they would do is CONTROL UNSHIFT the “, so CONTROL ’. It will bring the exact same formula down and leave it in edit mode, and from here they would actually press F2, HOME, RIGHT, SHIFT, RIGHT, RIGHT, RIGHT, and then type AVERAGE and press ENTER, and they would end up with an averaging AVERAGE that was averaging the right reference, alright?
So, CONTROL ’ will bring that formula down and keep the reference exactly the same; a couple of cool, great little tricks.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I love this one.
Alright, so, right here, we have a formula that is totalling those numbers, and if I would come right below that cell -- this goes back to elementary school, where we would say something like, if we would write a word and then, beneath that, we would press the “ key, right, that “ key was saying that we're just…you know, it's the same word as above, right, at least back when I was in 1st grade.
So, it was a great way to not have to write so many letters.
You could just use the “.
So, here, we're going to do the same thing.
CONTROL+SHIFT “ -- in other words, the quotation mark -- and it will bring that formula down as a value, alright, and so I can lock a value in, kind of CONTROL+C, ALT+E, S, V in a new spot, but the place where this is really handy, and I learned about this at General Electric in Cleveland, I was doing my seminar there, and the engineers would use this one all the time because they would have to total something and average the exact same thing, and so I have a total formula here, =SUM, and if you would just copy this down, right, it's going to change the reference.
It’s going to be referencing the wrong thing, alright?
So, that's not effective, but what they would do is CONTROL UNSHIFT the “, so CONTROL ’. It will bring the exact same formula down and leave it in edit mode, and from here they would actually press F2, HOME, RIGHT, SHIFT, RIGHT, RIGHT, RIGHT, and then type AVERAGE and press ENTER, and they would end up with an averaging AVERAGE that was averaging the right reference, alright?
So, CONTROL ’ will bring that formula down and keep the reference exactly the same; a couple of cool, great little tricks.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
