Microsoft Excel Tutorial: Excel External Link Trick: Extracting Data from Closed Workbooks (Excel SoCal Meetup Trick).

Welcome to another episode of the MrExcel podcast! In this episode, we will be discussing a trick that was shared at the Excel SoCal meetup by Ryan, who now has a great job as an Excel data analyst. This trick involves using external links to closed workbooks and how they are cached in the workbook. It took me a couple of weeks to try it out, but it’s definitely worth sharing with all of you.

In this demonstration, we have two computers – a desktop and a podcast computer. On the desktop, we have a workbook called EmployeeTable.xlsm, which contains ID, base pay, bonus pay, and name. On the podcast computer, we have another workbook where we want to set up an external link to the EmployeeTable. This is done by using the VLOOKUP function and setting the link to the closed workbook. Even when the workbook is closed, the link continues to work as expected.

But here’s the interesting part – we save the podcast file to Dropbox and open it on the podcast computer. Even though the workbook is closed and the computers are not networked, the external link still works. This is because Excel allows external links to work if there are less than 10,000 cells in the closed workbook. However, Ryan discovered that it is possible to extract confidential information from the closed workbook using this method.

To demonstrate this, we carefully select the formula after the comma and paste it into a new cell without the dollar signs. This creates a relative reference to the closed workbook. When we press Enter, Excel prompts us to find the closed workbook, but we can simply click Cancel and the value will be returned. This means that it is possible to extract the entire table, including confidential information, from the closed workbook.

To prevent this from happening, it is important to only link to the necessary columns and not include any confidential information in the linked area. This way, when the workbook is saved as a .zip file, only the necessary values will be sent along. I want to thank Ryan for sharing this tip and I hope it helps you in your Excel work. Don’t forget to check out the /”Excel Daily News/” for more Excel videos and blog posts. Thanks for watching and we’ll see you next time for another episode of the MrExcel podcast!/”

Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) External Workbook Links
(00:13) Trick Learned at Excel Meetup
(00:26) Trick for Learning Excel
(00:39) External Link Example
(01:31) External Link Continues to Work
(01:54) External Link on Different Computer
(02:40) External Link on Non-Networked Computers
(03:02) External Link with Confidential Information
(04:35) Warning about External Links and Confidential Information
(05:00) Addendum: Examining External Link File
(06:00) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Closed external workbooks
Excel Daily News
Excel data analyst
Excel formulas
Excel resources
Excel SoCal meetup
Excel tips and tricks
Excel tutorial
External links in Excel
Extracting data from closed workbooks
VLOOKUP function

Say that a co-worker writes a VLOOKUP to a closed workbook and refers to $A$1:$S$99,19 to grab a value from column S. The co-worker sends you the linking workbook but not the linked workbook. Perhaps there is some confidential information in columns B through R of that workbook.
Everyone involved might be initially surprised that you can take the linking workbook to a new computer, without access to the closed workbook on the original computer, and drag out the entire table stored in A1:S99 of the closed workbook, including the confidential bits.
I will admit I was initially surprised, but I guess I shouldn’t be surprised. Everyone should be aware of this, and think before you set up wide VLOOKUPs to closed workbooks.

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153595/

Please take the opportunity to connect and share this video with your friends and family if you find it useful.