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

Рет қаралды 228,283

4 жыл бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
⯆ Blog post is here: www.xelplus.com/excel-externa...
Bob's Excel Magic book on Amazon: amzn.to/2oVFRln
All about Excel's NEW Dynamic arrays - check out the complete playlist: • Excel for Office 365 &...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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

Пікірлер: 250

@LeilaGharani 4 ай бұрын

Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter

@manideep693 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

I'm happy to hear that :)

@FarhanMerchant 3 жыл бұрын

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

@rasonline 4 жыл бұрын

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

@ahmedstarman777 4 жыл бұрын

Great trick! Thank you! How can I locate the source cells that refer to these links?

@mariaalcala5159 3 жыл бұрын

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

@harycan6923 4 ай бұрын

Leila first of all you are unbelievable beautifully, second thing you are amazing your explanation is so cool and understandably , thank you wery much , you are again open for me another world .

@marcellennartz8762 2 ай бұрын

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

@LeilaGharani 2 ай бұрын

Glad it was helpful!

@1877Pegasus 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Glad you like our little trick :)

@TheMuraleee 4 жыл бұрын

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

@EmilJayaputra 4 жыл бұрын

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.

@LeilaGharani 4 жыл бұрын

Exactly! Good to have things under control :)

@martinemaurizio978 3 жыл бұрын

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

@LeilaGharani 3 жыл бұрын

You are so welcome!

@wayneedmondson1065 4 жыл бұрын

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 = ActiveWorkbook.LinkSources(xlExcelLinks) 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

@LeilaGharani 4 жыл бұрын

Thank you Wayne for sharing the additional tips in finding links. Also the VBA to get the job done. Love it!

@stevannsevellec1692 9 ай бұрын

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

@AweshBhornya-ExcelforNewbies 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

I will do my best :)

@remediationdepartment4332 4 ай бұрын

All your videos are useful, love you videos !

@pabloacosta9 Жыл бұрын

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

@cboyda Жыл бұрын

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

@traceylulu4860 3 жыл бұрын

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

@sukhdevsanghera2208 Жыл бұрын

HI Leila, THANK YOU THANK YOU, I this is amazing! I kind of hated Edit link box...can't expand to see the full path or get the the long file names. So much to learn every day in Excel even though we have all become experts by watching your videos! lol again Thanks you! keep up the wonderful work you do and teach us!

@excelisfun 4 жыл бұрын

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!!

@LeilaGharani 4 жыл бұрын

Agree : ) I love Bob's out of the box thinking. The great thing about these functions is they are easy to use...

@NTSTESTMASTER 4 жыл бұрын

kzfaq.info/get/bejne/h7qGatiLmsezqmg.html

@aflores7212 3 ай бұрын

👍 Awesome trick Leila, it is very helpful. Could it be crated as a private function instead as a name?

@monte4955 4 жыл бұрын

I like this. Those external links drive me mad. Thanks Leila. 👍

@LeilaGharani 4 жыл бұрын

I know, same here. Always makes me uncomfortable :)

@Achievefull 3 жыл бұрын

Absolutely loving this trick

@DougHExcel 4 жыл бұрын

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

@LeilaGharani 4 жыл бұрын

Our pleasure Doug :)

@arturzamilov9572 4 жыл бұрын

Hello Leila, but how I can find those links in my workbook?I mean in specific cells.

@ashoksahu9546 4 жыл бұрын

Excellent video. You are great. Thank you so much MM.

@LeilaGharani 4 жыл бұрын

Our pleasure. Glad you like it.

@NewbieInForeignLand 4 жыл бұрын

Much needed tutorial 😊👍

@walelaiouattara4882 4 жыл бұрын

Thanks Leila, good trick

@ugabrew 3 жыл бұрын

Good stuff. Thanks for sharing this.

@farithahamed3599 4 жыл бұрын

Hi Leila thanks for your valuable teaching and information. Is there any ways to compare data between 2 sheets against a particular column value and mention the difference results in conditional formatted cell. For eg: In Sheet 1 and Sheet 2, Column A has the staff No and from B to Z have other basic info of employees. How to spot the difference in Sheet 1 against Sheet 2 using conditional formatting

@thebesttechnical3608 4 жыл бұрын

Supper explanation and very helpful

@wesleylim3871 4 жыл бұрын

You never fail to amaze me. 😊

@LeilaGharani 4 жыл бұрын

That's good to hear :)

@eurrahjoy 4 жыл бұрын

I used this before, didn't know its excel 4.0... Thanks for information

@LeilaGharani 4 жыл бұрын

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.

@Ryuzakari 4 жыл бұрын

I love you Leila!

@ashoksahu9546 4 жыл бұрын

You are right MM.

@rachanamanoj3089 4 жыл бұрын

Thanks Leila

@1877Pegasus 4 жыл бұрын

Awesome!

@danilochavez4167 4 жыл бұрын

This option should be the main idea in the video. Dynamic arrays is not available for everyone yet

@cristianlusci4658 4 жыл бұрын

Brilliant! thank you very much Leila!

@LeilaGharani 4 жыл бұрын

My pleasure Cristian.

@Madd0g5148 4 жыл бұрын

Hi. Have you got any tips for using the maps? Can you report on multiple sales data per province/country?

@RayRay-lj9zb 9 ай бұрын

Amazing! thank you!!

@khalidrehman5045 4 жыл бұрын

Thanks LG for another cool trick

@LeilaGharani 4 жыл бұрын

Thanks to Bob for coming up with this genius solution.

@cdyard1 4 жыл бұрын

Great trick!

@office4u791 4 жыл бұрын

Wow, Leila! As I am a fan of you checking most of the videos you uploaded, it's progressively improving not only the presentation but also your make up... Thumbs upppppp Nice profile IG

@LeilaGharani 4 жыл бұрын

Doing my best :) Glad you like it.

@robertovelicaz7719 4 жыл бұрын

Brilliant! And so useful

@LeilaGharani 4 жыл бұрын

Glad to hear that Roberto. Thanks to Bob's great find.

@yurd563 4 жыл бұрын

Thanks for video!

@binhlephuoc685 4 жыл бұрын

Great video! Leila

@reachhari 4 жыл бұрын

Too good....great work. I keep learning

@LeilaGharani 4 жыл бұрын

Glad to hear that :)

@bobumlas6562 4 жыл бұрын

Nice! I do, too!

@gborka 2 жыл бұрын

Very impressive!!

@jeffborup3231 4 жыл бұрын

Is it possible to use GetFiles on a Sharepoint folder that is not mapped as a drive? Also wondering if other file attributes could be obtained, like last modified date and time for example?

@valueStocks 4 жыл бұрын

Beautiful and smart!

@LeilaGharani 4 жыл бұрын

Thank you!

@wallacelee5818 3 жыл бұрын

You saved my day!

@rory1653 4 жыл бұрын

Will always 👍 your vids Leila!

@LeilaGharani 4 жыл бұрын

Thank you for liking and supporting :)

@mihirjani8824 4 жыл бұрын

Hi Leila, Your videos are very innovative & always new things to learn. Can you help me solving how to identify all links together when we press ctrl+[ to go that specific cell but it goes to only 1 cell. Is there any shortcut tht can help me idendify all the cells that are linked from.

@stephenbrincat7124 4 жыл бұрын

Wow, this is mind blowing thanks for sharing 👍

@LeilaGharani 4 жыл бұрын

My pleasure Stephen. It's really a greating finding by Bob.

@bobumlas6562 4 жыл бұрын

Thanks!

@moscowwrestlingteam8000 4 жыл бұрын

THANKS 😊! IT'S very useful!

@LeilaGharani 4 жыл бұрын

I'm glad to hear that.

@davidfick4181 4 жыл бұрын

Thank you for this video. I am still struggling with something though - how do I determine the link for a Picture that I have inserted into a cell, that was inserted via Insert | Pictures, and then inserted as 'Link to File'. Those links don't show up using this method. How would I get such a link?

@ken2338 4 жыл бұрын

Excel features always amaze me.

@LeilaGharani 4 жыл бұрын

There's always something new to learn.

@big1975E 4 жыл бұрын

Can you use Excel 4.0 macros to get one or multiple values from a closed workbook?

@ravikashchand7941 4 жыл бұрын

Will it show hidden and temp files? Example if I need to cleanup network folder and want a list of all files in root and sub folders. Layered folder approach. And then incorporate the date last changed. For maintenance.

@user-lz4xi8md3q 11 ай бұрын

Hi. Great tips thanks. Unfortunately my GetFiles didn't quite work, it only lists one file out of 20 odd in the folder. Do you know what else I need to do please? Thank you.

@vanhustle9715 3 жыл бұрын

Thanks so much

@huseinaccountant4418 4 жыл бұрын

Thank you so much

@luissantos9071 2 жыл бұрын

Leila I thoroughly enjoy watching your KZfaq videos on Excel. I have a question that I cannot find the answer to. I have a stubborn external link that no longer exist. I tried breaking the link but it won't let me. Do you have any recommendations?

@danielle03201 4 жыл бұрын

Is there equivalent function or shortcut use in excel 2016? Without using VBA code? Thanks

@mariaalcala5159 3 жыл бұрын

I was wondering if there is a way to use the get files in directory for writing a macro. So that you get maybe a specific page of the listed files.

@chrism9037 4 жыл бұрын

Wow! Cool tip thanks

@LeilaGharani 4 жыл бұрын

Glad you like it Chris.

@bobumlas6562 4 жыл бұрын

Also glad you liked it!

@valentineelias7299 3 жыл бұрын

Hi Leila, is it possible to move an excel file with links on my desktop to one drive without breaking the file links? Some links referred are in my documents folder.

@bryang1 4 жыл бұрын

this is the kind of stuff that would driven me cuckoo bananas when taking over someone else's workbook. I would have never in a million years imagined you could reference a built-in macro like this.

@LeilaGharani 4 жыл бұрын

It really is genius.

@bobumlas6562 4 жыл бұрын

@@LeilaGharani Thanks!! :-)

@madebeen 3 жыл бұрын

Lov your vids but How can you specify just the price value for example from that dynamic table?I want to be able to specify one single specific value i.e. stock price not the whole table to be automatically updated ... I cant seem to find any filters for that, do you know how?

@johnborg5419 4 жыл бұрын

Great Staff : ) : )

@yashsomaiya8141 3 жыл бұрын

Hi Leila, please can you please make video on Pivot through VBA?

@shambhupatil55 11 ай бұрын

I have Excel workbook file in that around 40 plus sheets or tabs and around 40 plus external links. I want to know in which tab or those external links used as formula can you help me in this.

@haseebansari1966 4 жыл бұрын

That's great, I m facing an issue related to link. If i am keeping all linked files to one folder and then later copy the whole folder some where else, some of the files links moved to newly copied files but some remain linked to old location, not able to figure out this. Would you please assist.

@mgomaa9001 3 жыл бұрын

Thanks 😊

@rwfrench66GenX 11 ай бұрын

Thank you for this video! My Edit Links is greyed out. First I got a message saying I had to save the workbook as a macro enabled workbook so I did but no joy on finding the links or getting the formula to work. On another worksheet I have about 100 links to emails and I have each one in a text box so I can have multiple links in a single cell. I haven’t tried this on my home computer or Excel online yet, maybe it works better there.

@priyankagoyal1232 3 жыл бұрын

Hi, do you know how to create a macro for sequenece in excel versions which do not have inbuilt sequence formula

@Pygmygerbil88 4 жыл бұрын

Awesome

@samlee6740 2 жыл бұрын

How do i tell which cells of linked spreadsheet has been updated? IS there anyway to highlight or track when changes have been made and i refresh Data all? I know you can go into individual spreadsheet to Trace, but i have many worksheets linked to Masterfile and have hard time tracking which are updated or not.

@sherwincona7230 2 жыл бұрын

I've been searching KZfaq videos on how to export the sheet to save as another workbook using VBA but it should be already breakLinks so that if there mighty be an specific alteration for the exported new sheet as workbook it would be easy to deal with it. Can you help me how? thank you

@Pip3queak 2 жыл бұрын

I have a few excel workbooks to go through to find their links. Is there a way to find all links for all excel files without going into each one of them to type getlinks? Also is there a way to count how many external links to each external file?

@sandeepkothari5000 4 жыл бұрын

Leila, this is amazing. What are the other excel 4.0 functions which can similarly be used in a macro enabled excel sheet xlsm without using VBA code?

@bobumlas6562 4 жыл бұрын

You can buy the book and look at page 92!! (Available on Amazon) - "This isn't Excel, it's magic"

@sandeepkothari5000 4 жыл бұрын

@@bobumlas6562 Thanks Bob. It indeed is magic.

@sandeepkothari5000 4 жыл бұрын

@@Up4Excel Sure. I'll wait for that.

@stavrous100 4 жыл бұрын

a problem i am facing often is broken links inside data validation list reference. is there a way to display those?

@IcanCwhatUsay Жыл бұрын

Is there a way to get it to tell you what cells have the external links?

@mohamedfawzy8441 Жыл бұрын

thanks

@shridhar2763 4 жыл бұрын

Ma'am I have one question One of my friend sent me the file which has external link and I want to download that external file Is it possible? Pls help

@heikoheimrath7514 4 жыл бұрын

Another great video Leila. What is the name =Links() for the german version of Excel? Have a great evening. Greetings from Germany

@carlosborba3453 4 жыл бұрын

Same question, but in portuguese...

@MalgorzatByrska Жыл бұрын

Can you advice how to list all formulas on the spreadsheet

@hosseinhosseinpoor4845 3 жыл бұрын

thanks...

@IvyPlans Жыл бұрын

Is it possible to get the links from a cloud directory, like one drive?

@paulthesaintsfan9137 4 жыл бұрын

Great tip, so in Excel 2013 would they need Ctrl Shift Enter to work?

@LeilaGharani 4 жыл бұрын

Check out the pinned comment. That will work in your version.

@paulthesaintsfan9137 4 жыл бұрын

Leila Gharani thanks so much love the channel just started the Dashboard course as well

@adnanwarea5249 4 жыл бұрын

Thanks alot Is there a way to group dates twice in a month in pivot I.e 1st period : 1-14 days 2nd period : 15- end of month I hope u answer

@jodyburton0351 4 жыл бұрын

One way is add a column (use PERIOD for the header) and formula. If your dates are in column A and the first record is row 2, then the formula would be =CEILING(DAY($A2)/14,1). Any day 1 through 14 would round to 1, while day 15 and up would round to 2. Now you can group on the PERIOD field in the pivot. EDIT: I'm an idiot. Day 29 would round to 3. Better to use =IF(DAY($A2)

@laurajohnson7240 4 ай бұрын

This is super helpful but for some reason it's only giving me one link. any idea how to fix?

@mohamedchakroun4973 4 жыл бұрын

Nice and useful leila :-)

@LeilaGharani 4 жыл бұрын

Glad you like it Mohamed.

@mohamedchakroun4973 4 жыл бұрын

@@LeilaGharani I try it out GetLink with name manager, it gives me only one link not all links in the workbook (I use Office365 not the insider version), how could i fix that?

@bobumlas6562 4 жыл бұрын

@@mohamedchakroun4973 As it says in the video, use =INDEX(Getlink,ROW(A1)) and fill down

@mohamedchakroun4973 4 жыл бұрын

@@bobumlas6562 Thanks a lot, I didn't catch it from the video :-) Sorry :-)

@katerina6495 4 жыл бұрын

Wunderbar Liebe Leila, Viele Liebe Grüsse 🤗 💞

@LeilaGharani 4 жыл бұрын

Hallo Katerina :)

@YesMayhem7 11 күн бұрын

Is there a way to, not only capture the list of links, but use the list to update the domain. I need to do this for a migration. In SharePoint the domain will change after migration. There are alot of spreadsheets in a library that reference links to other excel files in the folder. I need to go through each file, find all the links, I'm thinking Queries and Connections, but I also need to change all the urls by replacing the previous domain with the new post-migration domain

@reinhardkkoehler 4 жыл бұрын

Thanks, cool trick. Just 2 questions for clarificaton: - You mentioned this command links at 2:46. Does this change in localized language versions? Your mentioning "links" is in the German Version the command for left, well because left translated to German would be links. So I'd imagine there is a language conflict, how if at all this could be resolved? - In 3:16 you speak of the insider edition. When will those commanders be widely accessible to the public? I thought they would be automatically updated to 365? What do I need to do to take advantage of these new commands? Thanks again for a great video!

@LeilaGharani 4 жыл бұрын

ad 1) It's probably something like "Verknuepfung" or something like that. I don't have the German list. ad 2) Microsoft did not give any specific ETA for it. They will come to Office 365 automatically with an update as soon as Microsoft releases them. In the meantime you can check the pinned comment above for an alternate solution.

@HyzZzz 10 ай бұрын

Same here for the Dutch version of Excel. These functions are not on the translations list, so I have no idea how to make it work.

@malcolmbacchus421 4 жыл бұрын

Nice.

@JatinS0mpura 4 жыл бұрын

This is mind blowing, very useful to pull data from that particular files using cell address formula in nest... But it's showing only 1st file from given directory. How to fix this?

@JatinS0mpura 4 жыл бұрын

I am using MS MS office 2019

@henryg5735 4 жыл бұрын

Leila, this is a great solution for a problem that I am sure bugs all of us from time to time, particularly when getting a spreadsheet from a 3rd party. But how, or can I, get the name/function saved in my personal.xlsb? Kudos to Bob!!

@henryg5735 4 жыл бұрын

Saved in Book.xltm instead which did the trick

@bobumlas6562 4 жыл бұрын

Thanks for the kudos!

@rajeevreddychavva968 2 жыл бұрын

Hi, I have requirement where I need to check the status of url's like 200,404 somelike that and need to send that as report every hour is there anyway to do that using excel