If you need to copy the *exact* formula from the cell above, there is an amazing shortcut key that will copy this formula down one row. I mean...the exact formula...not a formula with different references, but the exact formula. Episode 692 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today a cool trick.
This came to me from someone in one of my excel seminars.
Remember back in the days of grade school.
There was a punctuation mark that meant the same as above.
If you would come here and put a quotation mark.
Basically, that was a way of saying I don't want to write this data again.
It's the exact same thing the ditto mark.
Well, it turns out that there's an excel shortcut that must be based on the ditto mark.
Let me come here to where I have some numbers and I'm going to put in a SUM formula.
So we'll get our SUM formula and now I want to create an average formula just below it.
Well, there is no good way to do this, the AutoSum button, even if I did auto "Average" it's not going to get it right.
I would have to reposition the area.
If I would copy cell B10 down, I would have the wrong range.
But instead watch what happens if I hit Ctrl and then the key that has the " and the ' it does an amazing feat. It takes the formula that is immediately above it and copies it down to the cell without changing the references at all.
So now I can press F2 to edit home and then type AVERAGE, get rid of the SUM and I very quickly enter the exact same reference as before.
Now there's a variant of this. If you would hold on Ctrl + Shift + that key.
Basically, you're doing Ctrl " it will copy the values from directly above.
So here Ctrl+Shift+ " Copies the values not as a formula just as the values.
Couple of amazing little tricks that I can see that I'll start to use all the time.
Well, hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today a cool trick.
This came to me from someone in one of my excel seminars.
Remember back in the days of grade school.
There was a punctuation mark that meant the same as above.
If you would come here and put a quotation mark.
Basically, that was a way of saying I don't want to write this data again.
It's the exact same thing the ditto mark.
Well, it turns out that there's an excel shortcut that must be based on the ditto mark.
Let me come here to where I have some numbers and I'm going to put in a SUM formula.
So we'll get our SUM formula and now I want to create an average formula just below it.
Well, there is no good way to do this, the AutoSum button, even if I did auto "Average" it's not going to get it right.
I would have to reposition the area.
If I would copy cell B10 down, I would have the wrong range.
But instead watch what happens if I hit Ctrl and then the key that has the " and the ' it does an amazing feat. It takes the formula that is immediately above it and copies it down to the cell without changing the references at all.
So now I can press F2 to edit home and then type AVERAGE, get rid of the SUM and I very quickly enter the exact same reference as before.
Now there's a variant of this. If you would hold on Ctrl + Shift + that key.
Basically, you're doing Ctrl " it will copy the values from directly above.
So here Ctrl+Shift+ " Copies the values not as a formula just as the values.
Couple of amazing little tricks that I can see that I'll start to use all the time.
Well, hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
