Did you know that you can get data stored on websites directly into Excel and refresh it with a click of a button?
Here’s how you do it
Before you get into the method, it’s important to know that not all websites will be open to be imported into Excel. The data in the website has to be in the form of a table. The best way to determine if the data can be imported in Excel is by trying to import it. You can also get an indication if it would work if you visit a website and you see a table. Many Wikipedia pages can be imported in Excel due to the table structure. But also many other websites.
Let’s try to import a Wikipedia page. Browse to Wikipedia and find a list of anything. For example, best movies in year 2000 in Australia.
You’ll find something like this:
Seeing a table is a good indicator that it can be imported in Excel. Here is how you can get this data in Excel.
To import, go to the Data tab and click on the “From Web” button from the Get &Transform Data section
In the window opened, paste the address from the address bar of the browser you are using (you can select it and copy it with copy)
and paste it in here:
Leave the option button on Basic and click OK.
Excel will take a few seconds to establish a connection and then present you with all the tables found on the page. Click on the tables to find the table that contains the data you need and click on Load
Your table is now linked from the page to Excel. You may need to get rid of some of the duplicates in case of merged cells, maybe get rid of unwanted columns, etc.
If this is a table that updates often, all you need to do it to setup the table to update automatically when opening the file. You do that by clicking on the Connection Properties in the Design Tab. (The Design Tab only shows when your active cell is inside the table)
Set the option below to refresh when opening the file or Refresh Manually by using the Refresh button
