How to Import Data From the Web Into Microsoft Excel

Microsoft Excel is a very important tool for many to create worksheets. You can add data to review, compute, and analyze it further. Apart from adding it manually, Microsoft Excel allows you to import data directly from the web.

Importing data from the web into Microsoft Excel saves you from manually feeding the data or copy-pasting it. Once you connect Excel to the web data source, it imports the information you need and updates itself when there are changes. Here’s all you need to know about importing data directly from the web into Excel.

Important Things to Note When Importing Data From the Web Into Microsoft Excel

You need to meet two requirements to import data from the web into Microsoft Excel:

• Microsoft Excel version: Power Query supports importing data. With Power Query, Excel users can connect, transform, combine and load data. However, Power Query is only available on Excel for Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.

• Data Format: when importing data from the web into Microsoft Excel, the data must be in tabular form or embedded within an HTML table. If the data is not in a table, then Power Query won’t identify it.

How to Import Web Data into Microsoft Excel

Using the Power Query feature in Microsoft Excel, you can import data presented in tabular form on a website into a worksheet. Here’s how it works.

Step 1: Launch Microsoft Excel on your computer.

Step 2: On the Ribbon interface at the top, click on Data.

Step 3: In the group titled Get & Transform Data, select From Web.

Step 4: On the popup window with the label From Web, input the URL of the webpage into the Address textbox.

Step 5: After pasting the link, click on Ok to load the webpage in the browser.

Step 6: Microsoft Excel will ask you to reconfirm the data you want to load from the website, click on Connect to confirm.

Step 7: Once Microsoft Excel is done loading the data, you should see a Navigator window. Select any of the tables listed under the Navigator to check if your data was imported.

Step 8: Select Load to have it show up on your worksheet if you’re fine with how the data was imported from the web.

That’s all there is to importing web data into Microsoft Excel using the web. If you’d like the data to be constantly updated in line with changes to the website, read below on refreshing web data.

How to Refresh Web Data on Microsoft Excel

As we mentioned above, when you import data from a webpage to Microsoft Excel, it refreshes automatically. You can also set your preferences on how you would like the refresh to be done. Here’s what you need to do to refresh web data in Excel.

Step 1: Launch the worksheet that is connected to the webpage.

Step 2: Click on the Table.

Step 3: From the Ribbon, click on Table Design.

Step 4: Within the External Table Data group, click on the Refresh drop-down.

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

Step 6: In the dialog box called Query Properties, click on the tab called usage to set your refresh preferences which include:

• Enable background refresh: This option is ticked by default and ensures your content is constantly being refreshed.
• Refresh every: This option ensures Excel only refreshes the data within specific periods. When selecting this, you would have to 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 7: Select OK to save your refresh preference.

Importing Windows Directory and File Details into Excel

As long as your web data is in tabular form, and you have the relevant Microsoft Excel version, importing web data into your Excel should be straightforward. YOu can also import your windows directory or file details into an Excel.