Patrick from Alaska asks how to copy a chart but detach it from the original data set. In this Dueling Excel podcast, Mike and Bill show you various solutions. Learn Excel 97-2007 from MrExcel
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, welcome back, it's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun will be joining us.
Hey Mike, how's it going back in Ohio?
A lot more fun when we were there together.
Today's question sent in by Patrick, Patrick is from Alaska, and Patrick has to create a bunch of charts, and so he's created the first chart here.
And, it figures he would take that chart and copy it, paste it off to the side, Ctrl+V, and then paste the new data into this.
Right, so Ctrl+C and paste here, which of course fixes this chart, but oh shoot, that other chart that's out there is still connected back to that first data set.
And so Patrick's question is “Hey, how can I paste this and disconnect it from the data?” Alright, so you know, just first right off the top here, that one thing I'm going to suggest is copying the chart, the range that the chart’s in, and then come over here.
And instead of doing a paste, doing a paste as picture, not a link, we want to do Paste as Picture, and now I get a picture of that.
And then we can come here, and then do copy the next one up, Ctrl+V, alright, and we'll repeat that same process again.
Notice that because this is a static picture, we are not getting the change every time, so that's one way to go.
Mike, let's see what you have!
Mike: Thanks, MrExcel!
Hey, but I thought you would come into my office again today to shoot, yeah, it's a lot more fun when we're doing it live, but hey, we got a great trick here today.
Here's our chart, and how do we make it static?
I'm simply going to point to the edge and hold the Ctrl key to copy the chart, click and drag.
You notice that little plus sign, that means you can- that’s Copy, I'm going to drop it right here.
Now with the chart selected, I'm going to use my arrow keys, the arrow keys can cycle through the chart elements.
You can see up here, there's the title, and then there's the SERIES function, which connects this chart dynamically to the ranges.
I'm simply going to highlight this, hit the F9 key to evaluate, it converts those linked cells to static values, you can see all the values right there, and hit Enter, so that's one way to do it.
Now what is an advantage of this?
Well certainly, this is still a chart, so you could do whatever you want.
Ctrl+1, you could format it, it's not a picture, it's a chart, so let's say a solid fill, yellow or something like that, or whatever you want to do.
Another way to do this, I'm going to steal MrExcel’s trick, copy, paste as a picture, not linked.
Let me show you some 2010 keyboard shortcuts!
Now, I'm going to copy, click on the chart, Ctrl+C, and then I'm going to click right here.
Actually, I'm going to click down here, so you can see the smart tag because it won't show up on the video, Ctrl+V to paste the chart- Well you still can't see it, I'll have to drag this up here.
But watch this, this new smart tag has a (Ctrl) key on it.
So if you hit the Ctrl it pops up this menu.
This one right here, once you get the hang of it, is U.
That means Paste as Picture, so Ctrl and then stop and then U!
Let me do that again in high speed motion.
Ctrl+C, because notice when you Ctrl+C Ctrl+V, you hold Ctrl and hit C, Ctrl+C, but watch this, Ctrl+V, I'm holding Ctrl and hitting V.
But now, Ctrl separately, and then U separately.
One other cool trick, and I actually learn this from MrExcel, copy, and I'm going to click right here, and now, instead of Ctrl+V, and then Ctrl U, the right-click key.
Now, the “right-click” key is between the Window and Ctrl key on the right, so now I'm going to hit right-click key, notice- There they are, so it's just right-click U!
Let's try that again in high speed, so I'm going to copy, click, and then right-click U. Alright, I'll throw it back over to MrExcel!
Bill: Yes, that's beautiful!
F9!
Mike, I knew that you're going to use F9.
I love it when you use that trick where you take a big range and turn it into an array right in the formula of using F9.
I said “I bet he's going to use F9 for that!” You know, so one more way to go here, is we can copy this chart out here as a live chart for the first customer, then come back and click on the first chart, and grab this green box and drag it down, drag the numbers down.
Alright so hey, then the chart now is for customer #2, Ctrl+C and Ctrl+V to paste, and if the data is really set up like this, probably just dragging those boxes every time might be a good way to go.
Of course, you know, using VBA is probably the fastest way to go, but you know, just trying the old school method here, so lots of different ways to go.
Patrick from Alaska, thanks for sending that question in, Mike, great to be doing Dueling podcasts again, thanks to everyone else for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey alright, welcome back, it's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun will be joining us.
Hey Mike, how's it going back in Ohio?
A lot more fun when we were there together.
Today's question sent in by Patrick, Patrick is from Alaska, and Patrick has to create a bunch of charts, and so he's created the first chart here.
And, it figures he would take that chart and copy it, paste it off to the side, Ctrl+V, and then paste the new data into this.
Right, so Ctrl+C and paste here, which of course fixes this chart, but oh shoot, that other chart that's out there is still connected back to that first data set.
And so Patrick's question is “Hey, how can I paste this and disconnect it from the data?” Alright, so you know, just first right off the top here, that one thing I'm going to suggest is copying the chart, the range that the chart’s in, and then come over here.
And instead of doing a paste, doing a paste as picture, not a link, we want to do Paste as Picture, and now I get a picture of that.
And then we can come here, and then do copy the next one up, Ctrl+V, alright, and we'll repeat that same process again.
Notice that because this is a static picture, we are not getting the change every time, so that's one way to go.
Mike, let's see what you have!
Mike: Thanks, MrExcel!
Hey, but I thought you would come into my office again today to shoot, yeah, it's a lot more fun when we're doing it live, but hey, we got a great trick here today.
Here's our chart, and how do we make it static?
I'm simply going to point to the edge and hold the Ctrl key to copy the chart, click and drag.
You notice that little plus sign, that means you can- that’s Copy, I'm going to drop it right here.
Now with the chart selected, I'm going to use my arrow keys, the arrow keys can cycle through the chart elements.
You can see up here, there's the title, and then there's the SERIES function, which connects this chart dynamically to the ranges.
I'm simply going to highlight this, hit the F9 key to evaluate, it converts those linked cells to static values, you can see all the values right there, and hit Enter, so that's one way to do it.
Now what is an advantage of this?
Well certainly, this is still a chart, so you could do whatever you want.
Ctrl+1, you could format it, it's not a picture, it's a chart, so let's say a solid fill, yellow or something like that, or whatever you want to do.
Another way to do this, I'm going to steal MrExcel’s trick, copy, paste as a picture, not linked.
Let me show you some 2010 keyboard shortcuts!
Now, I'm going to copy, click on the chart, Ctrl+C, and then I'm going to click right here.
Actually, I'm going to click down here, so you can see the smart tag because it won't show up on the video, Ctrl+V to paste the chart- Well you still can't see it, I'll have to drag this up here.
But watch this, this new smart tag has a (Ctrl) key on it.
So if you hit the Ctrl it pops up this menu.
This one right here, once you get the hang of it, is U.
That means Paste as Picture, so Ctrl and then stop and then U!
Let me do that again in high speed motion.
Ctrl+C, because notice when you Ctrl+C Ctrl+V, you hold Ctrl and hit C, Ctrl+C, but watch this, Ctrl+V, I'm holding Ctrl and hitting V.
But now, Ctrl separately, and then U separately.
One other cool trick, and I actually learn this from MrExcel, copy, and I'm going to click right here, and now, instead of Ctrl+V, and then Ctrl U, the right-click key.
Now, the “right-click” key is between the Window and Ctrl key on the right, so now I'm going to hit right-click key, notice- There they are, so it's just right-click U!
Let's try that again in high speed, so I'm going to copy, click, and then right-click U. Alright, I'll throw it back over to MrExcel!
Bill: Yes, that's beautiful!
F9!
Mike, I knew that you're going to use F9.
I love it when you use that trick where you take a big range and turn it into an array right in the formula of using F9.
I said “I bet he's going to use F9 for that!” You know, so one more way to go here, is we can copy this chart out here as a live chart for the first customer, then come back and click on the first chart, and grab this green box and drag it down, drag the numbers down.
Alright so hey, then the chart now is for customer #2, Ctrl+C and Ctrl+V to paste, and if the data is really set up like this, probably just dragging those boxes every time might be a good way to go.
Of course, you know, using VBA is probably the fastest way to go, but you know, just trying the old school method here, so lots of different ways to go.
Patrick from Alaska, thanks for sending that question in, Mike, great to be doing Dueling podcasts again, thanks to everyone else for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
