How to Perform Web Queries to Import External Data in Excel 2013
To make a web page query in Excel 2013, you click the From web command button on the Data tab of the Ribbon or press Alt+AFW. Excel then opens the New web Query dialog box containing the Home page for your computer’s default web browser (Internet Explorer 10 in most cases). To select the web page containing the data you want to import into Excel, you can:
Type the URL web address in the Address text box at the top of the Home page in the New web Query dialog box.
Use the Search feature offered on the Home page or its links to find the web page containing the data you wish to import.
When you have the web page containing the data you want to import displayed in the New web Query dialog box, Excel indicates which tables of information you can import from the web page into the worksheet by adding a yellow box with an arrowhead pointing right. To import these tables, you simply click this box to add a check mark to it.
After you finish checking all the tables you want to import on the page, click the Import button to close the New web Query dialog box. Excel then opens a version of the Import Data dialog box with only the Table option available where you can indicate where the table data is to be imported by selecting one of these options:
Existing Worksheet (default) to have the data in the Access data table imported into the current worksheet starting at the current cell address listed in the text box
New Worksheet to have the data in the Access data table imported into a new sheet that’s added to the beginning of the workbook
After you click OK in the Import Data dialog box, Excel closes the dialog box and then imports all the tables of data you selected on the web page into a new worksheet starting at cell A1 or in the existing worksheet starting at the cell whose address was entered in the text box in the Import Data dialog box.
Excel brings this data from the Volume Leaders table on the Yahoo! Finance web page into the worksheet as cell ranges rather than as an Excel Table. If you want to be able to sort or filter this imported financial data, you need to select one of its cells and then select the Format as Table button on the Home tab to format them and add the requisite AutoFilter buttons.
When formatting data as a table, you remove all external connections to the data on the website.
You can make web queries only when your computer has Internet access. Therefore, if you’re using Excel 2013 on a portable device that can’t currently connect to the web, you won’t be able to perform a new web query until you’re in a place where you can connect.