Import data from web to excel

How to import data from web to excel. This can be via the internet or an intranet.

Related-View our course on cleaning data with Excel

Import data from web to excel

With Excel it is possible to import information from an Internet or Intranet site.

For example, here I have got a webpage with some currency and stock exchange information. I want to import it into Excel. What you must do is copy the web address and when you go to Excel, you’ll see under the Data tab, there is a button called “FromWeb”. When I click this button, you’ll see Excel brings up a browser (in this case Google) and in the Address bar I will paste the address I copied off the website. Excel will bring up the webpage and you’ll see little yellow blocks with arrows – When you hover over them they look green. These arrows will allow you to instruct Excel what to bring through. I will highlight the block of data I want to bring through – so if I click it – you’ll see a tick where the yellow block was and you can work through in this way and take whatever information you want. What I can then do is click on the Import button and then Excel brings up a screen asking you where you want to put the data – in some existing worksheet or a new spreadsheet. In this case I am going to go to an existing worksheet and I will click on the cell where I want it to be placed. When I click ok, you’ll see Excel has picked up the information and placed it where you have specified. Even though this data looks like a normal Excel spreadsheet, it is important to note that it is fact connected to the internet. If I right click on it, you’ll see there are a couple of new options- I can Edit Query, look at the Data Range Properties or I can click on Refresh. If I click on Refresh – it remembers the web address I sent it to. It will go and collect the information off the internet and if there have been any changes, it will pull the change through. Also on the Data Tab you’ll see there is a Refresh button, it you click “Refresh All” or highlight an area you want refreshed. If you right click again and go to Edit Query, it brings up the query as you specified it or the Table you specified, etc. Just to point out some other buttons within this Tool – in the Options Tab there are a number of things you can choose, for example in formatting in this example we have none specified (it came through as straight numbers and text) but it can be Rich Text or Full HTML formatting. There are a couple of other items which may assist you. Also its possible, again if you right click on your spreadsheet with the imported data – there is an item called “Data Range Properties”, when I click on it you’ll see there are some more options – you’ll see the name of the Table – you must tick Save Query Definition. Under Refresh Control, you can tick Enable Background Refresh, or you can get it to Refresh every so many minutes if you require it or to Refresh the data when opening the file. With formatting you can see we can Preserve Cell Formatting or else every time you Refresh it will take the website’s formatting or you can Adjust Column width. There are other options which could just make your importing a whole lot better.