How to Import Data From the Web Into Microsoft Excel

Quick Tips

• Power Query supports importing data in Excel.
• You can only load data if it is available in HTML table format.
• It is possible to capture live data from the website and convert it into an Excel table.

Using Microsoft Excel’s Power Query feature, you can import data presented in tabular form on a website into a worksheet. Power Query is only available on Excel for Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.

Step 1: Open Excel and click on the Data tab on the Ribbon menu.

Step 2: Click the From Web menu.

Step 3: In the popup window labeled From Web, input the webpage address into the URL textbox. After pasting the link, click OK to load the webpage into the browser.

Step 4: Once Microsoft Excel has loaded the data, you should see a Navigator window. Select the required table to check if you want to import that.

Step 5: if the preview looks good, click the Load button to import it on the sheet.

How to Refresh Web Data on Microsoft Excel

It is interesting to note that you can refresh the data loaded from a website in Excel. This is handy if you constantly use a website’s value in your calculations. You can also choose how often you want to refresh the content.

Step 1: Select the Table connected with the website in your Excel file. Then, click on the Table Design menu.

Tip: If you want to remove the theme of the table design, follow the guide.

Step 2: Click the Refresh drop-down within the External Table Data group.

Step 3: From the Refresh drop-down, you have three options: Refresh, Refresh All, and Connection Properties. To set your preferences, click on Connection Properties.

Step 4: In the Query Properties dialogue box, click on the Usage tab to set your refresh preferences, which include:

• Enable background refresh: This option is ticked by default and ensures your content is constantly refreshed.
• Refresh every: This option ensures Excel only refreshes the data within specific periods. When selecting this, you must input the minute intervals where you want this to occur.
• Refresh data when opening the file: The data in the Excel file is only refreshed when you launch it.

Step 5: Select OK to save your refresh preference.