Importing Data from a Website
To import data from the web, you first need to identify the web address (URL) of the website with the data you want to import. Then you can use the import tools in Excel to import the data directly from the webpage into your worksheet.
Let’s say we want to import currency exchange rates from the web into our worksheet.
Below is how the data looks on the website after we’ve used the filters on the website to narrow down our search and display the data we want. We can now use this URL to import the data tables on the website.
On the Data
tab, in the Get & Transform Data
group, click on the From Web
button. Alternatively, on the Data tab, navigate to Get Data
> From Other Sources
> From Web
.
Excel then opens the From Web
dialog box with a URL field where you enter the address of the web page containing the data you want to import into Excel.
When you click OK
, Excel will establish a connection to the website.
Note
: If this is the first time you’ve connected to the website, Excel may display an Access Web-content
dialog box with different connection options. Connect with the default which is Anonymous
.
Once connected, Excel will display the Navigator
screen, listing the data tables on the Selection pane on the left. When you click on a table in the Selection pane the data is displayed on the preview pane on the right.
To import more than one table of data from the web page, select the Select Multiple Items
check box and then click the checkboxes against the table names you want to import.
Once you’ve selected the table(s) you want to import on the page, you have the following three import options:
- The Load
button imports the data as seen in the Navigator preview pane into your workbook (in a new worksheet).
- The Load To
option (on the Load button’s drop-down menu) opens the Import Data
dialog box which gives you more options for how you want to import the data and where to place it. You can choose to import it as a worksheet data Table, PivoTable, PivotChart, or to just establish a data connection without importing the data. You can also choose the worksheet where you want to place the data.
- The Transform Data
button opens the data in the Excel Power Query Editor
, which allows you to query and transform the data before importing it. For example, you may want to import only some of the data columns or filter the data with a criterion to only import a subset of the data.
After importing the data, you can manipulate and work with the data as you would with any other Excel table.
Refreshing Web Data
When working with tables imported from websites with live data, for example, financial websites like the Nasdaq or Dow Jones (while the markets are still open), you can refresh the data to reflect any changes in the data. When you import the data, Excel automatically stores information about the connection, so you just need one button click to refresh the data.
To refresh data imported from a website, on the Data
tab, in the Queries & Connections
group, click on the Refresh All
button. This will automatically re-establish the connection and refresh the imported data with the latest data from the website.
