Excel is incredibly powerful software that can do just about any mathematical or logical calculation you might need. Excel gurus can create spreadsheets that automate the complex processes that many businesses need to be successful.
The only problem is that data entry can be tedious and time-consuming. Especially if your calculations depend on regularly updated information. Do you really need to manually check and enter changing information manually each time?
It turns out the folks at Microsoft have thought about this, and Excel has the ability to get information from websites directly. This means that if there is a site that tracks a specific value in some kind of web spreadsheet, you can connect it to your spreadsheet so that your calculations are up to date whenever that Excel file is opened on a computer with an Internet connection.
What’s more, you can create your own website spreadsheet, perhaps with a free website builder like Google Sites, which provides an easy way to automatically update the Excel sheets you share. Sounds good, right? So let’s take a look at how it works.
Pull data from a web site in Excel
For this guide, we are going to use the latest version of Excel for Office 365 as of March 2019
First, open Excel and create a blank sheet.
Now switch to the Data toolbar.
On this toolbar, under Get and Transform Data, click From Internet. 3
You can insert the site address for the desired web table here. In this case, we are going to use ferates.com, which lists the current Mastercard exchange rate tables.
Specifically, we will use the URL https://ferates.com/mastercard/usd, which displays this table of US dollar exchange rates. The table from the site looks like this:
Paste the URL into the appropriate field and click OK.
This will open the Navigator, a tool showing the various components of your chosen website.
The tool has identified a table on the page, select it to make sure this is what we want.
Yes! This is exactly what we wanted, so now all you have to do is click Download.
The table is now in your real-time data spreadsheet. You can use its contents just like any other table, with the difference that the numbers will always be relevant.