Get ALL External Links with This SIMPLE Excel TRICK (as NEVER seen before!)

With this Excel trick you can use a formula to show all the external links in your Excel workbook in one place. This way you can keep an eye on where the data is coming from and if any link is pointing to the wrong workbook.
With this Excel hack you can also write a formula to get the list of files from a specific directory and bring it to your Excel sheet. So if you're waiting for your colleague to add a file to a directory, you'll be able to see it in your Excel formula results once you refresh the list.

The fun part about this Excel trick is it combines 4.0 Excel Macros with Excel dynamic arrays. You can use the Excel Transpose function to get a vertical list of external links for file names.

🔑 Key Points:
- Track External Links: Learn a swift method to list all external links in a workbook, bypassing the traditional step-by-step approach.
- Dynamic Array Spill Feature: Utilize Excel's latest calculation engine to get a spilled range of links, displaying them horizontally or vertically using the transpose function.
- Creating Custom Excel 4.0 Macros: Discover how to create names for Excel 4.0 macros to perform specific functions, such as 'Get Links' and 'Get Files'.
- File Directory Listing: See how to generate a list of all file names in a specified directory, using wildcards and dynamic arrays.
- Sorting and Filtering Data: Explore the use of the SORT function and file extensions to organize and filter the list of files.
- Saving Files with Macros: Understand the importance of saving files with Excel 4.0 macros as an XLSM file to retain new functionalities.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[FarhanMerchant]

Impressive !! You're absolutely correct - there is always so much to learn in Excel. Great trick, appreciated.

[manideep]

You teach literally things which saves time and life. Appreciate all your work 😀

[rasonline]

I didn't even know Excel 4.0 was a thing! You just opened up a new world with this. Thanks.

[excelisfun]

Thanks for the Bob-Inspired Macro Functions!! I agree, we NEVER stop learn and finding new tricks. Fun : ) But I disagree with Help when it says we should migrate away from Excel 4 Macro Functions - there are still so many uses for some of those functions!!

[wayneedmondson]

Hi Leila.. what an incredibly clever set of tips. Thanks for sharing them. For external links, I have always used the Find feature searching on ".xl" and Within Workbook.. then CTRL+A to highlight.. or Edit Links through the ribbon menu, as you demonstrated. Getting the list right on the worksheet is great. And, the GetFiles trick is super helpful. For those using legacy EXCEL, wrap your named functions (GetLinks or GetFiles) in TRANSPOSE and just remember to highlight a range of cells first and press CSE to mimic the spilling behavior vertically in the range. If you highlight a greater range than needed, you can use Conditional Formatting to hide the #N/A errors in the larger range that you have defined to handle future additions to the list (note that IFERROR and IFNA don't seem to work with TRANSPOSE, nor can you trap with standard IF and ISNA). Not as elegant as in new dynamic array EXCEL, but gets the job done. Thanks again for these innovative and hidden tips.. always something new and interesting coming from LeilaG :)) - Thumbs up!
PS - You could also get there using VBA either as a SUB or a UDF, as in:
Sub ListLinks()
Dim aLinks As Variant
aLinks =
Dim i As Integer
If Not IsEmpty(aLinks) Then
Sheets.Add
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
End Sub

[EmilJayaputra]

As you put on the title, "never seen before"! Really helpful, thanks Leila. Now I can have these vexing links in my Excel workbooks under control.

[TheMuraleee]

This was I searching for a long time.. Love you LG...

[mariaalcala]

Wow it’s super useful!! Thanks Leila for sharing your knowledge!!

[m.ssharma]

Thanks for the details on Links in excel. Really helpful.

[LeilaGharani]

Update 2020: The new functions are available in Excel for Office 365. For those that don't have Dynamic Arrays - use this formula instead: =INDEX(getlinks, ROW(A1)) and then drag down. To avoid the #REF error, wrap it inside the IFERROR function.

[Pegasus]

Amazingly helpful trick! Seriously, you guys are more than experts. You invent new uses of excel. Keep it up. Thank you so much!

[AweshBhornya-ExcelforNewbies]

Once again you have given some thing new to learn thanks a lot Leila. Keep it coming

[pabloacosta]

Just what I needed, spot on! Thank you, it worked like a charm.

[remediationdepartment]

All your videos are useful, love you videos !

[martinemaurizio]

Thank you Leila you are a life saver and a fantastic teacher. Always something new to learn.

[traceylulu]

Thank you so much for sharing the tricks. It is really useful.

[cboyda]

Nice easy way to improve functionality, thanks for the share!

[marcellennartz]

This trick saved me so much time. Another person was explaining to write code in VBA whereas this did it within seconds

[DougHExcel]

Neat-O! Thanks Leila and Bob for the tips!

[stevannsevellec]

Hi Leila, nice trick when translated... LINKS becomes LIAISONS in french. Works perfectly. Thanks