Hi, this is Wayne again with a topic “Using Linked Data Types in Excel”.
In this article, i’m going to show you how to work with the new linked data types that you can find in excel now. This is for microsoft, 365 versions of excel, but this also works with excel for the web. So, even if you don’t have microsoft 365, you can still give this a try. So, let’s take a look at my workbook, you can see that i have three different worksheets. The first one has a list of movies, just some random movies and, let’s say i’d like to add some information about those movies, maybe the year that they came out. Maybe how much money each movie made while it was in theaters, maybe who directed each movie – and i can certainly look this information up. I can open up wikipedia or imdb or some other website and gather that information and put it into this spreadsheet and in the past.
That’S exactly what i would have done, but now, with a recent microsoft, 365 update, look what you can do in excel. I can highlight the range of data and as long as i’m signed into my account, look what i get on the data tab in the data types group. There’S a button here for stocks, one for currencies and if i click on this button, i get even more or this button pops open with the full list of linked data types that i have access to in excel.
So basically, what this means is that excel can automatically identify some terms or some of the items that fall into these categories, so things related to chemistry, things related to plants or animals space, so stars planets things like that: information related to cities, locations and even yoga. So, let’s try it out with our list of movies, i’m going to click on movies and at this point it seems like excel has frozen. It doesn’t look like much is going on, but if you look at the bottom of the screen, there is a message saying that it is converting the data into data.
That’S linked. It simply means that there is information being linked from the internet into microsoft, excel and much of that information comes from wolfram, which is a website that you can go to, and this information is updated on a regular basis. So when new movies come out, that information will be added statistics from the cities and information about population and things like that will be updated on a regular basis as well.
So let’s take a look at the results of us using linked data types in this spreadsheet. So you’ll notice in my list, any movie that has this camera icon next to it, is recognized by microsoft. Excel here’s one back to the future.
That excel is not sure what to do with there’s multiple movies with that name back to the future. So this is the one i’m talking about, so i just click on it. Click select and now that movie has the movie icon next to it.
What about gremlins same thing? That’S the one i’m talking about same with jaws, so this is mostly happening with movies that have sequels excel is not sure which of the movies i’m really talking about now. In the case of this particular movie, the sparks brothers, this recently came out and it’s kind of a smaller movie. So it’s not yet part of the linked data and so that one’s just not going to work for us.
That’S okay, i’ll move on to the birds, and in some cases you may need to change the titles, so i just changed it to birds and then it did recognize it. So now all of the movies, except for one, are recognized by excel. Now that that’s the case check out what i can do, i can click and drag to highlight the range and then go to this little icon, that pops up where it says, insert data, and i can click and look at all this information. That’S linked to the text that i’ve typed into excel and that’s been recognized and linked to this online data, so i want to know the genre of each of these movies. I click genre and it fills it in for me, except for sparks brothers. Unfortunately, at this point i probably should expand column b so i’ll, just double click between b and c, and it expands out.
Let’S try this again i’ll just highlight the range go back up to that icon click. I would like an image of each of the movies. Take a look at that and let’s try it again. I’D like to know the release date and i’d like to know the director and i’d like to know the international box office total receipts. So how much total money did this movie make across the world and so very very quickly? Thanks to the linked data types, i’ve been able to create a spreadsheet full of information about those movies and very quickly. I can format the column headings just the way i want them to be now.
As you can see, there are some parts of this spreadsheet that it didn’t work for. Obviously the sparks brothers, but also there’s not information about the box office, totals for some of these movies. So i can just delete those out and either add in information from another source or just keep it blank.
Okay, let’s try it again, but this time with a different spreadsheet, this one is a list of states in the united states, so i’ll click and drag to get the range. And this time i’m going to include a1. So i’ll go down to a21 and let’s go back to the data tab there in the data types group we’ll go here and choose geography once again excel kind of freezes for a second and that can take longer than you would expect, especially if you have a Lot of data in your spreadsheet, okay, so this time all of the states in my spreadsheet are recognized.
I can tell because there’s a little map icon next to each state excel, didn’t know what to do with the word states. It’S the column heading, and so it just puts a question mark there, that’s fine! Now i want you to see what, if, instead of selecting the entire range, what if you just pick one of the states and then click this button, it brings up a bunch of linked information, but this is only going to add this additional information in this case. For the state of alabama, so if you want all of it to be brought in, you need to select the entire range and then go up and click this icon.
I would like to know the leaders of those states – the largest cities, the number of people per household and the housing units in that state, and once again, all of this data is regularly updated. So this is the information for today, but in six months some of this information may change. It’S all kept fairly up to date. One last example: i’m going to go to this foods spreadsheet and let’s try it again now. In this case i don’t have a list of just 10 or 20 foods. I actually have looks like 118 different foods listed here so instead of clicking and dragging i’m just going to go here to the upper left area in the name box and i’ll just click and type a 2, because i don’t really need the heading selected through and You can indicate through with a colon a 118 i tap enter, and it selects just the range that i want selected i’ll go here to the data, tab, data types group and i’ll click to identify this range as foods.
There. I get a little bit of a delay, but when excel is done thinking about this information, i will again have linked data that i’ll be able to pull into this spreadsheet directly from the internet. It looks like it’s finished how many of these foods were recognized? It looks like almost all of them, which is amazing. It looks like potato chips that was not recognized, so if i click there, i might have to pick a specific kind of potato chip. I’Ll just go with this one. Here, click select and now it’s recognized now. If you looked closely some of these foods that i had in the spreadsheet, they were named a little differently than what excel was expecting, but excel was able to identify them and it changed the wording to match the linked data. I’Ll go here to the name box. I’Ll type in a2, through a118, tap enter, it selects the data, and then i can use this button here to click.
I would like to know the total calories of each of these foods. I also want to know the cholesterol. The total protein i’d like to have an image of each that would be good and i’d like to know how much vitamin c each of these foods has. So this information looks like it came in beautifully, with the exception of image, it looks like excel is having some trouble getting images for most of the foods, but i am very excited about this feature in microsoft, 365 versions of excel and also excel for the web. This will help me tremendously to be able to add in information quickly from the internet and be able to use it right away in my spreadsheets now, because this is all linked data to the internet. What if the information changes online and i open my spreadsheet – would it have the new information? Well, not necessarily, if you want to make sure you have the most recent up-to-date information. What you would want to do is select the data and then go to the data tab on the data ribbon here in the queries and connections group you can go to refresh all and if you click on the bottom part of that button, you have some options. Refresh, what’s selected or refresh all either way so i’ll, just click refresh – and this will take some time now to refresh the data but it’ll be the most recent information. That’S available online and it’ll be pulled into my spreadsheet thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do so through my patreon account, or by buying channel merch and you’ll, see information about those options in the description below this video .