Seven ways to paste values in Excel. While I talk about many ways to convert formulas to values, Don from Orrville has come up with a method shorter than the other 6 methods I usually discuss. So...drumroll please... Episode 877 with method #7 for doing Paste Values!!!
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.
I just got back from one of my Power Excel seminars, and I always talk about using Paste Special Values, and we talked about different ways to Paste Special Values.
And normally, most people, they'll copy, and they'll use some method to copy-- you know, Ctrl+C, for example, or the Copy icon, or right-click and Copy-- and then we go into Edit, Paste Special, and choose Value.
And I always point out that it's a little bit faster to use Ctrl+C and then Alt+ESV.
In other words, E for Edit, S for Special, and Pay Special, V for Values and click Enter.
And you know, that works fine.
It's also possible to use Ctrl+C and then right here next to the Paste icon is Paste Values.
Alright that saves a couple of clicks.
Someone this morning pointed out that what they do is, they use Ctrl+C, and then have added the Paste Values icon to the toolbar; so basically, now, you know, in essence what you really have is you have two clicks-- the first click is to copy, the second click is for Paste Values.
But then Dawn, from Orville, Ohio, raised his hand and said, "You know, look, you talk all the time about little macros you can add to your personal macro workbook.
It's a two-line macro that makes this down to a single click." And he's absolutely right.
If we go to Tools, Macro, Record New Macro; and say "PasteValuesInPlace"; Shortcut Key-- I'm going to leave the Shortcut Key blank, we'll assign it to an icon; store it in the Personal Macro Workbook; click OK.
Now, make sure that you turn on Relative Reference and then just do your Ctrl+CLESV-- whatever you would normally do to Paste Special Values.
And that little macro now basically has two lines of code; copy the selection and paste the selection as values.
To get it up on your toolbar, we'll right-click and choose Customize; left-hand side Choose macros; and we'll stick with a smiley face for just one second-- right-click on a smiley face, assign the macro to Paste Values in place; we'll change that icon-- first of all, let's add a tooltip here, "Paste Values"-- and we can either change the button image to one of the canned button images or we can copy from an existing button-- so here I'm going to come up here to the Paste Values, I'm going to copy the button image and then on the smiley face, right-click and paste button image, click Close.
And now what I have is basically one click.
You see that we have formulas--2*F1-- click the icon, it converts it to values and it's a one click solution.
Now, of course I can't do this podcast without sending a shout out to the folks in Columbus, Indiana, and my very favorite trick of all time now: The fast way to change formulas to values.
Go to the right edge, right-click, drag right, drag left, let go; this brand new menu pops up, Copy Here as Values Only.
So there you have it, at least seven different ways to aste values.
Thanks to Don from Orville for just giving the suggestion of a simple two line macro that you can very easily record and put in your Personal Macro Workbook, and add an icon to the toolbar that will convert those formulas to values right in place.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I just got back from one of my Power Excel seminars, and I always talk about using Paste Special Values, and we talked about different ways to Paste Special Values.
And normally, most people, they'll copy, and they'll use some method to copy-- you know, Ctrl+C, for example, or the Copy icon, or right-click and Copy-- and then we go into Edit, Paste Special, and choose Value.
And I always point out that it's a little bit faster to use Ctrl+C and then Alt+ESV.
In other words, E for Edit, S for Special, and Pay Special, V for Values and click Enter.
And you know, that works fine.
It's also possible to use Ctrl+C and then right here next to the Paste icon is Paste Values.
Alright that saves a couple of clicks.
Someone this morning pointed out that what they do is, they use Ctrl+C, and then have added the Paste Values icon to the toolbar; so basically, now, you know, in essence what you really have is you have two clicks-- the first click is to copy, the second click is for Paste Values.
But then Dawn, from Orville, Ohio, raised his hand and said, "You know, look, you talk all the time about little macros you can add to your personal macro workbook.
It's a two-line macro that makes this down to a single click." And he's absolutely right.
If we go to Tools, Macro, Record New Macro; and say "PasteValuesInPlace"; Shortcut Key-- I'm going to leave the Shortcut Key blank, we'll assign it to an icon; store it in the Personal Macro Workbook; click OK.
Now, make sure that you turn on Relative Reference and then just do your Ctrl+CLESV-- whatever you would normally do to Paste Special Values.
And that little macro now basically has two lines of code; copy the selection and paste the selection as values.
To get it up on your toolbar, we'll right-click and choose Customize; left-hand side Choose macros; and we'll stick with a smiley face for just one second-- right-click on a smiley face, assign the macro to Paste Values in place; we'll change that icon-- first of all, let's add a tooltip here, "Paste Values"-- and we can either change the button image to one of the canned button images or we can copy from an existing button-- so here I'm going to come up here to the Paste Values, I'm going to copy the button image and then on the smiley face, right-click and paste button image, click Close.
And now what I have is basically one click.
You see that we have formulas--2*F1-- click the icon, it converts it to values and it's a one click solution.
Now, of course I can't do this podcast without sending a shout out to the folks in Columbus, Indiana, and my very favorite trick of all time now: The fast way to change formulas to values.
Go to the right edge, right-click, drag right, drag left, let go; this brand new menu pops up, Copy Here as Values Only.
So there you have it, at least seven different ways to aste values.
Thanks to Don from Orville for just giving the suggestion of a simple two line macro that you can very easily record and put in your Personal Macro Workbook, and add an icon to the toolbar that will convert those formulas to values right in place.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
