Import Data from the Web into Excel

You can import data from the web into Excel for analysis and reporting. When data is imported from the Internet, a connection is established. This means that the link can be updated, and you will not have to repeat this import process every time the data is needed.

For this example, we will look at importing data from www.soccerstats.com. This is a favourite site of mine. It contains up to date statistics from soccer leagues all over the world. We will import the La Liga top scorers table.

Import Data from the Internet into Excel

1. Click the Data tab on the Ribbon and then the From Web button.

2. The New Web Query dialog box appears. Enter the address of the website containing the data you want to import into the Address field and click Go.


3. Use the links on the webpage as normal to navigate to the required page if necessary.

4. When on the page you want, Excel will display small black arrows inside yellow boxes by the parts of the page that you can import (This depends how the page was structured and may not meet your needs).

5. Click inside the yellow box for the section of the page you want to import. It will change to a black tick inside a green box to signify your selection.

6. Click the Import button.

7. The Import Data dialog box is shown prompting you for the destination of your import. Select the required cell of the spreadsheet and click Ok.

8. The imported data may take a few seconds to appear.

The imported data will quite often require a little formatting and tidying up. If this connection is updated regularly then this can become a very repetitive task. Therefore the formatted and tidying of imported data is a very common task for a macro (Find out more about macros).

Now that your data has been imported a connection was created between the spreadsheet and that section of the webpage. You can manage that connection by using the commands in the Connections group of the Data tab.

The Refresh button is used to update the connection or link. You will not have to import the data again as the existing connection can just be refreshed whenever it needs updating.

The Properties button can be used to modify the properties of the connection. Popular properties to modify include the connections name, which in the example below looks quite unfriendly, and the refresh control options.