Using Links in Mac Numbers

Hi, this is Gary with MacMost.com. Let's talk about the different ways that you can use Links in your Numbers documents. 

MacMost is brought to you thanks to a great group of more than 2000 supporters. Go to MacMost.com/Patreon. There you could read more about it. Join us and get exclusive content and course discounts.  

Now there are a variety of different kinds of Links that you can include in any Numbers spreadsheet. Most people are probably thinking of links to webpages but let's start with talking about links inside of the Numbers document from sheet to sheets. 

So in a Numbers document you can have a number of different sheets. Here in this example I've got a Summary sheet, Inventory, Sales, and On Order. So the summary here has some calculations based on the other sheets. So here's the total inventory and that is the sum of everything in this table and this sheet. So it would be nice to actually have a link to jump to this sheet right here. We can easily create that by just putting some text there and making it a link. 

Let's type Link To Inventory as just some text. I'm going to select the text. The links are way to format text so you want to select the text itself, not just the cell. So this link will just be for the entire piece of text. But I could just assign a link to one word if I wanted to. Then I'm going to go to Format and then Add Link. You can see here I've got the ability to add a webpage link, email, phone number or sheet link. I'm going to use Sheet right here. Then it is going to prompt me with the Text To Display and I'll leave it at what the text was originally. This is the type of link and then this is the sheet to link to. So it lists the four sheets that are here. So I'm going to link to the Inventory Sheet. Now I've got a link. If I click on it you could see it jumps right to that sheet. 

So I could assign a link like this to the other two rows here. I could even put the link here assigned to this text instead of adding a new column if I wanted. When designing a document with links like this it is useful to make sure you've got plenty of sheets and you only use each sheet for one purpose. Usually just one table or maybe one table and a lookup table. Something like that. That way you can create a link to that sheet and jump right to the information you want. 

Another type of link that you can use is an email link. So this will just take you to the Mail App and start a new email using that link. So, for instance, here I've got a table that just has some names and email addresses. It would be nice to be able to click on this email address and jump right to a mail composition window. So I could do that by selecting the email right there and using this same Add Link Menu here and I can select email. I'm going to select that and since I've selected an email address it recognizes that and immediately creates the email link. So now when I click on that it goes to the mail app and you can see it starts a new email message to that person.

You can do the same thing with a phone number. So I can select this phone number here and I can go to Format, Add Link, and then Phone Number and it will create a link there. If I click on it, since I'm on a Mac, it's going to Launch FaceTime and try to make an audio call to that number. 

Now let's finally get to webpage links. So just like with email addresses and phone numbers you can have a URL, like this. You can select it and then you can go to Format, Add Link and then Webpage. When I do that it will convert this to a webpage link. Now when I click on it it will launch a web browser and take me to that webpage. But let's say instead you wanted to use some other text. So you could just have some text like this and if you select that and then use either the menu item or the keyboard shortcut, Command K, it's going to ask you for the Display Text. I'll leave it like that. You see it is set to Link To A Webpage. I can paste the link in there. Now I'll click elsewhere to dismiss that and you can see I've got that link right there. It works the same way. 

But that's not the only link functionality in Numbers. Because you can use Functions to do this as well. So the key Function to use is simply Hyperlink. So I'm going to start a formula by pressing the equals key here. Then on the right I'm going to Search for Hyperlink and look at the Help information about it. You can see it takes a URL and some Link Text as the two parameters. So let's go ahead and use this and as the URL here I'll put in quotes that URL to that Wikipedia page. Then as the Link Text in quotes I'll put the text that I want to appear. This simple formula here, with one function in it, will give me the same results as actually manually putting in a link. But you can see here it is using Hyperlink function. If I click on this it works the same way. 

Let's go and do something more creative since we're using a function these can be variable values. So let's go ahead and instead of having this text here we're going to just simply use the text from the first column there. So you can see how it picks that up right there. Instead of using this link here I'm going to remove this last part, I'm going to use ampersand to concatenate some more text, and then I'm going to use the value from here. So it should end up being the same thing. wikipedia-org/wiki/ and then the word apple. Then using apple as a link. We can even go ahead and put an ampersand quote space link, like that, and now we've got text like that. If I click it it goes to the same webpage. But if I were to copy that and paste it down here it's going to grab this word instead for both the link part and the text part. So if I click here you could see how it goes to this page. So now I could actually copy and double click on the column heading right there and paste in and you could see how I get all these different links and new links would be added every time I create a new row. I don't have to manually create all these links. Very useful if you're linking to some sort of online inventory system where the item ID or name is always going to match the name here in the first column. 

You could do some clever things with this like, for instance, instead of this text here to go to a specific page I can paste in a typical goggle search URL. So goggle.com/search?q= and then whatever this is here. So now when I click on this it actually takes me to a goggle search for that word. 

Another thing you can do is use a Lookup Table for this. So here in Sheet 3 I've got a Lookup Table with specific URL's for specific items. So this is very useful if there is no real pattern like there is here for wikipedia pages. So you want to have manual URL's here in this table full of links. Then here I can use a basic lookup to get the URL. So I'm going to use VLOOKUP for this and then I'm going to lookup this value here and I'm going to look it up in this Table right here. So I'm going to select the whole table like this. Then the value it's going to get is from the second column, if the first column is matched. Then I'm going to use False to make sure the match has to be exact. So that's what my VLOOKUP looks like. So now you can see here it grabs that one. If I Copy and Paste it down here you can see it grabs the next row, the next row, and then this one here will give me an error because nothing matches this word right here. Now we can turn these into Links by enclosing this then in Hyperlink. 

So I"m going to type Hyperlink and then I'm going to use this as the link and then for the text I'm going to just have Link, like that. So now it just has the word Link and it will do it for all of those and it would take me to the Proper Link from this sheet. But this one here would still give me an error because nothing matches that. We can fix that by putting IFERROR around this entire thing. Then the  comma here and then just two quotes for leave it blank if there is an error. So now you can see that's blank right there. So I can Copy and Paste into all of those there and if I were to add a new one, like for instance go here and add mango and then let me go and create some text for a link here, like that. Notice I go back to this sheet and it does find a link there for mango and it does work to take me to that page. 

A few more tips. One is that you can use Hyperlink for phone numbers and email addresses as well. So, for instance, I can do Hyperlink and the URL here would have to be mail to colon. Very specifically that. Then use an ampersand to append this and then put the link text and let's just put the word email like that. Now this link will work pulling this here and it will go to the Mail app. The same thing for a telephone number except you need to use the word tel with a colon after it and then the specific phone number like that. Then we'll put Call. Then that will work. 

You can even do it with some other things if there is an app that supports it. So, for instance, the Dictionary App supports dictionary lookup this way. So I can do Hyperlink and for dictionary I need to do dict colon and then two slashes have to be here. Then an ampersand and then the value here. Then I could put dictionary like that. Now this will bring up the Dictionary App right to that word. 

One more tip I want to show you. If you actually have a LOOKUP table that has links in it, so this is just text right now. But if I do Command K it will convert it to a link. I'll do it for each one of these. Selecting the text first and then doing Command K to change it to a link. Then I could do the LOOKUP and it will actually be a link. So I can go back in here and just do the LOOKUP. I don't need to convert it to a Hyperlink, and you can see it brings it in and it is actually a link because it's a link here. If this wasn't a link, I'll remove the link like that, then you could see the result there isn't a link. So the value of the cell can be a link versus text. 

There are a bunch of different ways you can use links in Numbers to enhance your spreadsheets. You can build on these basic ideas to do even more. I hope you find it useful. Thanks for watching.