MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Paste Values for External Links


November 14, 2017 - by Bill Jelen

Paste Values for External Links

In Excel, can you break all external links? Imagine if you could Paste Values, but only for the links that point to another workbook.


Watch Video

  • 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

Auto-Generated Transcript

  • hey welcome back it's time for another
  • learn Excel podcast from MrExcel
  • desire episode 2070 paste values but
  • only for external links now this this
  • question came up with one of my seminars
  • it's spring seminar season louisville
  • knoxville columbus indiana hall we had a
  • great crowd in columbus and coming up
  • this month i want to be in bloomington
  • indiana Indianapolis and Sarasota
  • Florida if you're anywhere near there
  • talk to your manager I say you'll save a
  • hundred hours a year by coming to this
  • Excel seminar so the question that was
  • posed me someone said all right I have
  • the spreadsheet it has formulas it has
  • constants and it also has external
  • formats all right 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 all right only if there's a
  • link formula I want to paste values the
  • other formulas need to stay and they're
  • all mixed in I was like wow is a tough
  • one in my head I started to think about
  • how am I going to do that right I know
  • that all external form of us have the
  • left square bracket in 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 and converted
  • to its value and I really just threw me
  • for a loop so it was an all day seminar
  • morning and afternoon i said to the
  • person hey you're going to be here this
  • afternoon yeah yeah i'm gonna be here
  • this afternoon i said all right let me
  • check with some folks i went out to my
  • excel friends and Charles Williams and
  • Debra dog leash both reminded me of this
  • awesome thing I mean I was completely
  • looking at the wrong way I wanted to do
  • paste values I was looking for a special
  • way to paste values but even we keep on
  • here to find and select and chose
  • formulas all right well that's all the
  • formula cells including the internal
  • formulas not just the external form this
  • 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 that's 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 pay so i use
  • i have to do break links and suz
  • Charles and debra told me that I
  • instantly remember the all 2003 way to
  • do this it was alt e' alt e' to open the
  • edit menu and then k all e k would get
  • us to the edit links box and sure enough
  • right here is something called break
  • link Frank 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 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 all
  • right 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 would break link and it warns
  • you hey breaking links is going to
  • primarily convert formulas and external
  • references to their existing values you
  • can't undo it save a version of this
  • file with the new name are you sure you
  • want to break links click break links
  • click close all right and now our
  • formulas that are in here let's see i'm
  • sure there's still some promos control
  • and the drawback sent will put us in yet
  • we still have some lots of formulas
  • those form of the state any formula was
  • not pointing to another cell stayed but
  • any formula that was it 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
  • and said oh no we know 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
  • how you go to the file menu and we go to
  • the file menu down here in the lower
  • right-hand corner related documents is
  • edit links to files and that's where
  • they've hidden what used to be under alt
  • e' k well hey this tip and all kinds of
  • other tips like it powers that with mr.
  • excel click that I on the top right hand
  • corner by 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 was control in the backwards
  • accident can draw backs an exit a GU to
  • show form this press that again to exit
  • show formulas mode I started to think we
  • read some vba to detect the opening
  • square bracket and external link but
  • you can use the high call it 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 it's supposed to be in the
  • file panel file it info panel it oh
  • right and thanks to Charles and Debra
  • for sending in that tip and thanks to
  • you for stopping by we'll see you next
  • time for another net cast from MrExcel

Download File

Download the sample file here: Podcast2070.xlsm

Title Photo: Didgeman / Pixabay


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.