today i want to show you how you can import data from the web directly into microsoft excel maybe you want to pull in data from wikipedia maybe you want the latest stock prices or maybe you want to pull in cookie sales data on your competitors in the cookie industry you can do all of that you don’t have to manually copy and paste data over into microsoft excel instead you can connect excel to the data source this way when the data updates your spreadsheet will automatically reflect the latest changes this way you can focus on higher value activities like watching videos on youtube all right let’s check this out here i have a website with cookie sales data for some of the top players in the cookie industry i’m sure you recognize at least some of these names see the kevin cookie company right down here at the bottom see i knew you would recognize at least one name on this list now i could copy and paste all of this data and bring it over into microsoft excel but once again if the data changes or maybe there are some updates i would have to copy and paste again and i have far too many youtube videos to film to spend my time doing that let’s go over to excel to see how we can import this data within microsoft excel to pull data in from the web up on the top tabs let’s click on the one called data all the way over on the left hand side under get and transform data there’s the option to pull data in from the web let’s click on this option this opens up a prompt where we can type in a url of a website where we want to get the data from this could be wikipedia this could be a finance website it could be any type of website for this to work though the data has to be contained within a table on the website if it’s not in a table unfortunately excel will be unable to retrieve the data here i’ll type in the url and then click on ok excel has now made a connection to the website and this opens up the navigator over on the left hand side i can see all of the different elements on the site that excel found here i see the overall document and down below it found one table and look at that this is the table with all of the cookie industry data this is exactly what i want to import into excel i could also look at a web view and here it’ll show me the website with all of the data highlighted down below i can load it directly into microsoft excel as is right over here i can also transform the data it’s basically a fancy way of saying i can make edits to what this data looks like before i bring it into my spreadsheet i’m okay bringing it in as is so i’ll simply click on load and look at that all of the web data is now in my spreadsheet and it really pains me to see russell stover ahead of us in the cookie industry russell stover is the cookie that you get as a gift for the holidays that let’s be honest no one is really that excited about the great thing about pulling data into excel using this technique is that excel is now connected to the data here when i have the table selected within table design i can refresh the data so once again let’s say you have stock data or maybe wikipedia data let’s say that it updates often you can simply click on this to refresh your data if we click into connection properties over here i can also define how often this sheet should refresh currently it’s set to a background refresh but i could also define the amount of time where i wanted to refresh i can even have it refresh every single time that i open this file this here is 2020 data and this is really before i started putting out youtube videos promoting the kevin cookie company so let me go up above and i’ll click on refresh to see if the latest 2021 data is available yet and look at that the kevin cookie company is in the top spot it looks like all of these youtube videos promoting the kevin cookie company have really been paying off i’ll have to tell my manager patty now let’s say that as part of this analysis i no longer want to include the most popular cookie now i could delete this column and there i’ve removed it however if i go to refresh my data now it’ll pull in all of the data again and here the most popular cookie is back again but i don’t want it to appear here so we can use the transform option that we saw earlier to change the way the data looks before it comes into our spreadsheet to transform the data up on the top tabs let’s click into query and on the far left hand side you can click on edit this is another way to get back to all of those transform capabilities this opens up the power query editor and with this you can transform your data before bringing it into excel basically you can make the data look how you want it to look power query is really powerful you can run calculations you can append data and so much more i’ve included a video up above and also down below in the description that goes into far more detail than what i’m going to cover in this video here i simply want to remove the most popular cookie column i’ll go to the top of the column i’ll right click and all click on remove that column is now done and that’s the only change i wanted to make over here i’ll click on close and load back here on the excel sheet i can now see that that column is gone if i click back into table design up on top and i refresh my data you’ll see that that column no longer reappears i’ve applied that change so anytime i refresh my data it’ll reflect that change all right and that’s how easy it is to get external data from the web into microsoft excel like i’ve always said there is money in the cookie business to see more videos like this please consider subscribing i’ll see you next time [Music]