Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

how to break vlookup links?


I inherited a worksheet from someone who made thousands and thousands of vlookups between separate workbooks and they take forever to update and they update each time Excel starts. The thing is, we don't need the links to be kept anymore. Is there an easy way to stop these links or stop the vlookups from running without disabling links for the entire workbook?

Thanks in advance!




To scratch the surface you might look at File > Options > Formulas > Workbook calculation. If you set this option to 'Manual' no calculation. The same setting is also available from the Ribbon's Formulas Tab in the Calculations section. There you will also found buttons to manually calculate either a single sheet or the entire workbook.

Look at File > Options > Advanced > When calculating this workbook. There are options for 'Update links to other workbooks' and 'Save external link values'. It seems that you might disable the update here and show static values instead. I haven't ever used these properties and recommend that you try them out.

The basic question is what do you want in the cells with the Vlookups to other worksheets instead of the looked-up values. If all you want are static values the best method should be to remove the links. This would have to be done column by column. After determining that the column shows the values you want, select all of the values, copy, Paste > Paste Values and all formulas are irretrievablky converted to the values they last calculated.


Quick and simple way is to select everything and copy/paste values like this:

Select data > Ctrl+C > Alt+E+S+V > Enter

That will take almost no time and will literally "freeze" your values.


Answer the Question

You must create an account to use the forum. Create an Account or Login