Sean wants to Paste Values for all formulas that have external links in Excel. While that might be possible with a macro, you can use Break Links to solve the problem.
We have a workbook that has a mix of formulas, constants, and external links
How can you convert all external links to values?
Tip: Use Ctrl+` to show all formulas (press again to exit Show Formulas mode)
Do you write some VBA to detect the opening square bracket in the external link?
No! You use the very hard to find Break Links button!
Save the file first.
Save the file with a new name.
Alt+E K
Click Break Links
Edit Links is also on the File, Info panel, lower right
Thanks to Charles Williams and Debra Dalgleish for this tip
We have a workbook that has a mix of formulas, constants, and external links
How can you convert all external links to values?
Tip: Use Ctrl+` to show all formulas (press again to exit Show Formulas mode)
Do you write some VBA to detect the opening square bracket in the external link?
No! You use the very hard to find Break Links button!
Save the file first.
Save the file with a new name.
Alt+E K
Click Break Links
Edit Links is also on the File, Info panel, lower right
Thanks to Charles Williams and Debra Dalgleish for this tip
Transcript of the video:
Hey, welcome back. It's time for another… Learn Excel Podcast from Mr. Excel. This is our Episode 2070: Paste Values but Only for External Links Now this, this question came up at one of my seminars, it's Spring seminar season: Louisville, Knoxville, Columbus, Indiana. Oh, we had a great crowd in Columbus. And coming up this month, I’ll be in Bloomington, Indiana, Indianapolis and Sarasota, Florida. If you're anywhere near there, talk to your manager.
I say you'll save $100 a year by coming to this Excel seminar.
So the question that was posed to me, someone said, “Alright, I have this spreadsheet.
It has formulas, it has constants and it also has external formulas.” Alright, so check this out. There's just some regular formulas here but then the VLOOKUPs are linking to external files. And the question that came in is: Before I send this off, I need to Paste Values all of the external formulas. Alright, only if there's a linked formula I want to Paste Values. The other formulas need to stay and they're all mixed in. Ahh, I was like, wow, that is a tough one. And in my head, I started to think about how am I going to do that, right? I know that all external formulas have the left square bracket and the right square bracket, so do I write some VBA? That's going to go through and try and detect if the cell dot formula has a left square bracket.
And if so, then convert to its Value. And I really just - it threw me for a loop. So it was an all-day seminar, morning and afternoon, and I said to the person, I say, “Hey, you're going to be here this afternoon?” “Yeah, yeah. I'm going to be here this afternoon.” I said, “Alright, let me check with some folks.” So I went out to my Excel friends and Charles Williams and Debra Dalgliesh, both reminded me of this awesome thing. I mean, I was completely looking at it at the wrong way. I wanted to do Paste Value so I was looking for a special way to Paste Values but, you know, even when we keep on here to Find & Select, and chose Formulas, alright, well that's all the formula cells including the internal formulas, not just the external formulas, and there's no way even in Go To Special to say that I want the formulas that have external links. Right, so you know, that's just not going to solve the problem.
So, I was thinking about it completely wrong.
How am I going to Paste Values? But I don't have to Paste Values, I have to do break links.
So as soon as Charles and Debra told me that, I instantly remembered the Alt 2003 way to do this. It was Alt+E, Alt+E to open the Edit Menu and then K, Alt+E K would get us to the Edit Links box, and sure enough, right here is something called Break Link. Break Link, although - Then I started looking for it in the Ribbon. Where the heck is it today? Where is the equivalent of Alt+E K today.
I'll show you but what we want to do is - So we have our file with the live links, I want to Save that file. And then I want to do a File, Save As and I'll create a version with no links. Alright, because you want to be able to come back to the one with links. Now that we have that, Alt+E K and right here, this box with Break Link and it warns you: Hey, breaking links is going to permanently convert formulas and external references to their existing values. You can't undo it, save a version of this file with a new name.
Are you sure you want to break links? Click Break Links, click Close, alright.
And now, our formulas that are in here - Let's see, I'm sure there's still some formulas, Ctrl and the grave accent will put us in.
Yeah, we still have some lots of formulas.
Those formulas stay, any formula that was not pointing to another cell stayed, but any formula that was, there was a VLOOKUP to another workbook has now been converted to Values.
What a cool, cool trick.
And so now, after the seminar someone came up to me and said, “Oh, no. We knew where to find that. We know where to find it.” And I had to go back to the one with links.
This only works if there's links in the file.
You go to the File menu, and when you go to the File menu, down here in the lower-right hand corner under Related Documents is Edit Links to Files, and that's where they've hidden.
How it was used to be under Alt+E K.
Well hey, this tip and all kinds of other tips like it, Power Excel with MrExcel. Click that “i” in the top-right hand corner to buy the book.
Today's wrap-up: Alright, so we have a workbook that has a mix of formulas, constants and external links. How can you convert all external links to values? Now, one tip I showed there is Control in the backwards accent, the grave accent - accent aigu, to show formulas, press that again to exit Show Formulas Mode. And I started to think, do we write some VBA to detect the opening square bracket in the external link? But, no. You can use the- I call it the very hard to find Break Links button.
Save the file first and then save the file with a new name. Alt+E for edit, K for links.
Click Break Links. Yeah, it's supposed to be in the File panel. File, Info panel to the lower-right.
And thanks to Charles and Debra for setting in that tip, and thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
I say you'll save $100 a year by coming to this Excel seminar.
So the question that was posed to me, someone said, “Alright, I have this spreadsheet.
It has formulas, it has constants and it also has external formulas.” Alright, so check this out. There's just some regular formulas here but then the VLOOKUPs are linking to external files. And the question that came in is: Before I send this off, I need to Paste Values all of the external formulas. Alright, only if there's a linked formula I want to Paste Values. The other formulas need to stay and they're all mixed in. Ahh, I was like, wow, that is a tough one. And in my head, I started to think about how am I going to do that, right? I know that all external formulas have the left square bracket and the right square bracket, so do I write some VBA? That's going to go through and try and detect if the cell dot formula has a left square bracket.
And if so, then convert to its Value. And I really just - it threw me for a loop. So it was an all-day seminar, morning and afternoon, and I said to the person, I say, “Hey, you're going to be here this afternoon?” “Yeah, yeah. I'm going to be here this afternoon.” I said, “Alright, let me check with some folks.” So I went out to my Excel friends and Charles Williams and Debra Dalgliesh, both reminded me of this awesome thing. I mean, I was completely looking at it at the wrong way. I wanted to do Paste Value so I was looking for a special way to Paste Values but, you know, even when we keep on here to Find & Select, and chose Formulas, alright, well that's all the formula cells including the internal formulas, not just the external formulas, and there's no way even in Go To Special to say that I want the formulas that have external links. Right, so you know, that's just not going to solve the problem.
So, I was thinking about it completely wrong.
How am I going to Paste Values? But I don't have to Paste Values, I have to do break links.
So as soon as Charles and Debra told me that, I instantly remembered the Alt 2003 way to do this. It was Alt+E, Alt+E to open the Edit Menu and then K, Alt+E K would get us to the Edit Links box, and sure enough, right here is something called Break Link. Break Link, although - Then I started looking for it in the Ribbon. Where the heck is it today? Where is the equivalent of Alt+E K today.
I'll show you but what we want to do is - So we have our file with the live links, I want to Save that file. And then I want to do a File, Save As and I'll create a version with no links. Alright, because you want to be able to come back to the one with links. Now that we have that, Alt+E K and right here, this box with Break Link and it warns you: Hey, breaking links is going to permanently convert formulas and external references to their existing values. You can't undo it, save a version of this file with a new name.
Are you sure you want to break links? Click Break Links, click Close, alright.
And now, our formulas that are in here - Let's see, I'm sure there's still some formulas, Ctrl and the grave accent will put us in.
Yeah, we still have some lots of formulas.
Those formulas stay, any formula that was not pointing to another cell stayed, but any formula that was, there was a VLOOKUP to another workbook has now been converted to Values.
What a cool, cool trick.
And so now, after the seminar someone came up to me and said, “Oh, no. We knew where to find that. We know where to find it.” And I had to go back to the one with links.
This only works if there's links in the file.
You go to the File menu, and when you go to the File menu, down here in the lower-right hand corner under Related Documents is Edit Links to Files, and that's where they've hidden.
How it was used to be under Alt+E K.
Well hey, this tip and all kinds of other tips like it, Power Excel with MrExcel. Click that “i” in the top-right hand corner to buy the book.
Today's wrap-up: Alright, so we have a workbook that has a mix of formulas, constants and external links. How can you convert all external links to values? Now, one tip I showed there is Control in the backwards accent, the grave accent - accent aigu, to show formulas, press that again to exit Show Formulas Mode. And I started to think, do we write some VBA to detect the opening square bracket in the external link? But, no. You can use the- I call it the very hard to find Break Links button.
Save the file first and then save the file with a new name. Alt+E for edit, K for links.
Click Break Links. Yeah, it's supposed to be in the File panel. File, Info panel to the lower-right.
And thanks to Charles and Debra for setting in that tip, and thanks to you for stopping by. We'll see you next time for another netcast from MrExcel.
