Taking data from a web page and linking it to a spreadsheet has never been easier. Here is a website with the latest currency exchange rates. This data at xe.com refreshes regularly. We want to bring this data into cells in the spreadsheet, even if it currently isn’t laid out the way we want to display it in Excel.
Use Get & Transform to connect to a website and import data
Use Excel 2016’s Get & Transform to turn it into usable data.
- Back in Excel, Data tab > New Query > From other sources > From Web.
- Type in the URL of the site, click OK.
- The Navigator launches, showing all the objects on that web page that can be imported into a spreadsheet.
- In this example, Table 0 is the table containing the currency rates we want to import. However, it needs to be tidied up before its brought into Excel.
- Instead of loading the data directly into Excel, click Edit.
- Query Editor launches.
- Select the first column.
- On the Home tab, select Remove columns.
- Column 1 disappears.
We also want to remove rows from the data. Every second row needs to be deleted. We don’t need the inverse rates for our purpose.
- Home tab > Remove Rows > Remove Alternate Rows
- First Row to remove. Is row 2.
- You only want to remove 1 row, and you only want to keep 1 row. Click OK.
- What remains is the data that we want to bring over to the spreadsheet.
- Home tab > Close & Load. The query editor closes and the data is brought into Excel.
- Because the data is connected to a website, this data will update regularly. Press the Query tab > Refresh button if you want to refresh the data manually.
That is just one example of how to use Get and Transform in Excel 2016. Learn how to use Get and Transform to merge columns here.
Looking to learn more about Excel? See more free Microsoft tips or browse our range Microsoft Excel courses.