Today's tip comes from Dave in Columbus, Indiana. Dave was at my Power Excel seminar last week and passed along a better way to invoke the Paste Special Values command. In the process of showing Dave's trick, I also talk about joining text using the concatenation operator, changing text from upper case to proper case, copying a formula by double-clicking the fill handle, using Ctrl z to undo. Episode 394 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I have a better way to use the Paste Special Values, but let me set up the tip first of all.
We have here a couple of columns, first name in column A, last name in column B.
If we want to join those two columns into a single column, we would use the concatenation operators.
That's A2&, that's a Shift+7, and then maybe “ “&B2, and that would join our text together into a single column, I'll double-click the fill handle to copy that down.
Just another tip here before I go on to Paste Special Values.
If you need to put that in upper and lower case, you can use the PROPER function, so =PROPER(A2&” “&B2) will give us the names in upper and lower case.
OK now, we have this data in column C, and you might be tempted to go through and delete columns A and B because you don't need them anymore.
Fortunately, if you do that, all of our formulas change to reference errors, I'll hit Ctrl+Z to undo and get that data back.
Now, many of you know that we probably need to use Paste Special Values in order to change these live formulas to their current values.
And normally we would use Ctrl+C and then Edit, Paste Special, Values, in order to convert those values.
But I saw a great trick, I actually learned this in Columbus.
Indiana from some one hour seminar there, a fellow named Dave, show me this great trick.
He said “Basically what you do, is you’re going to right-click on the border of the range containing the formulas, drag it to the right one cell, and then drag it back to where it was.
When you let go, a brand-new item pops up, saying Copy Here as Values Only, click OK, and now we have the static values instead of live formulas.” Let me repeat that, we're going to copy our range of values, right-click on the right border, drag to the right, drag back, and then a context menu pops up where we can say Copy Here as Values Only.
Much quicker way than choosing Ctrl+C and then Edit, Paste Special, Values, right-click, drag over, drag back, and choose Copy Here as Values Only.
Want to thank Dave for coming to the seminar in Columbus, Indiana, and passing along that great trick, really obscure one, clearly a trick for the Excel pros out there.
Thanks for stopping by, we'll see you on Monday for another netcast from MrExcel!
Today I have a better way to use the Paste Special Values, but let me set up the tip first of all.
We have here a couple of columns, first name in column A, last name in column B.
If we want to join those two columns into a single column, we would use the concatenation operators.
That's A2&, that's a Shift+7, and then maybe “ “&B2, and that would join our text together into a single column, I'll double-click the fill handle to copy that down.
Just another tip here before I go on to Paste Special Values.
If you need to put that in upper and lower case, you can use the PROPER function, so =PROPER(A2&” “&B2) will give us the names in upper and lower case.
OK now, we have this data in column C, and you might be tempted to go through and delete columns A and B because you don't need them anymore.
Fortunately, if you do that, all of our formulas change to reference errors, I'll hit Ctrl+Z to undo and get that data back.
Now, many of you know that we probably need to use Paste Special Values in order to change these live formulas to their current values.
And normally we would use Ctrl+C and then Edit, Paste Special, Values, in order to convert those values.
But I saw a great trick, I actually learned this in Columbus.
Indiana from some one hour seminar there, a fellow named Dave, show me this great trick.
He said “Basically what you do, is you’re going to right-click on the border of the range containing the formulas, drag it to the right one cell, and then drag it back to where it was.
When you let go, a brand-new item pops up, saying Copy Here as Values Only, click OK, and now we have the static values instead of live formulas.” Let me repeat that, we're going to copy our range of values, right-click on the right border, drag to the right, drag back, and then a context menu pops up where we can say Copy Here as Values Only.
Much quicker way than choosing Ctrl+C and then Edit, Paste Special, Values, right-click, drag over, drag back, and choose Copy Here as Values Only.
Want to thank Dave for coming to the seminar in Columbus, Indiana, and passing along that great trick, really obscure one, clearly a trick for the Excel pros out there.
Thanks for stopping by, we'll see you on Monday for another netcast from MrExcel!
