Excel Tip #1: Import Data From a Website
Sometimes you’ll find some great information online that you want to capture in a spreadsheet. The data might be too tedious to type in manually, so simply import the data from the website into your spreadsheet.
There are a couple ways of doing this.
Open Webpage in Excel
- Open Excel.
- Click File > Open.
- Next to the File name box, select All Web Pages from the drop down menu.
- Enter the URL from the website that has the data you want to import into your spreadsheet in the File name box.
- Click Open.
Excel will take a bit of time to open up the web page, but it will copy everything it can into your spreadsheet. Chances are you will have to play around with the formatting, and possibly delete some of the extra stuff it brings in like navigation menus, advertisements, etc. but the data you are looking for should also get imported as well.
Get External Data in Excel
- Click on the Data tab, and then click the From Web icon in the Get External Data group.
- In the Address box enter the URL of the website you want to import data from.
- Click the Go button.
- Click the yellow box with the black arrow next to the tables you want to import into your spreadsheet.
- Sometimes Excel won’t be able to identify data that appears to be laid out in a table as a table. Modern website formatting can give the appearance of columns and rows without actually using a table. If this is the case with the website you are trying to import, you can usually click the arrow at the top left corner of the page and import everything.
- Click Import.
- Excel will prompt you for which cell you would like the imported data to begin in. Enter the cell, and click OK.
Excel will take a little while to process the data and then import it into your spreadsheet.
The nice part about the second method is that you can click the Refresh All button in the Connections group on the Data tab to update your spreadsheet with whatever is on the website at any time. This is a nice way to update stock prices, sports statistics, or frequently updated data like that.