Microsoft Excel is a very important tool for many people to create worksheets. You can add data to review, calculate, and further analyze. Besides adding manually, Microsoft Excel lets you import data directly from the web.
Exporting data from the web to Microsoft Excel saves you from manually feeding or copying and pasting data. When you connect Excel to the web data source, it imports the information you need and updates itself when changes occur. Here’s everything you need to know about exporting data directly from the web to Excel.
Important Points to Consider When Exporting Data from the Web to Microsoft Excel
To export data from the web to Microsoft Excel, you must meet two requirements:
- 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 in Excel for Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, and Excel 2010.
- Data format: When exporting data from the web to Microsoft Excel, the data must be in tabular form or embedded in an HTML table. If the data is not in a table, Power Query does not identify it.
Exporting Web Data to Microsoft Excel
Using the Power Query feature in Microsoft Excel, you can export data presented in tabular form on a website into a worksheet. Here’s how it works.
Stage 1: Start Microsoft Excel on your computer.
Step 2: Click Data on the top Ribbon interface.
Stage 3: In the group called Get and Transform Data, select From the web.
Step 4: In the popup labeled From the web, enter the URL of the Web page in the Address text box.
Step 5: After pasting the link, click OK to load the web page in the browser.
Step 6: Microsoft Excel will ask you to reconfirm the data you want to upload from the website, click Connect to confirm.
Step 7: When Microsoft Excel has finished loading the data, you should see an Explorer window. Select one of the tables listed under Explorer to check if your data has been imported.
Step 8: If you’re having trouble with how data is imported from the web, select Upload to have it appear on your worksheet.
That’s all it takes to export web data to Microsoft Excel using the web. If you want the data to be constantly updated according to changes made to the website, read the topic refresh web data below.
How to Refresh Web Data in Microsoft Excel
As we mentioned above, when you export data from a web page to Microsoft Excel, it is automatically refreshed. You can also set your preferences for how you want the renewal to be done. Here’s what you need to do to refresh web data in Excel.
Stage 1: Start the worksheet linked to the webpage.
Step 2: Click on the table.
Stage 3: Click Table Design from the Ribbon.
Step 4: Click the Refresh drop-down menu within the External Table Data group.
Step 5: From the Refresh drop-down menu, you have three options: Refresh, Refresh All, and Connection Properties. Click Connection Properties to set your preferences.
Step 6: In the dialog named Query Properties, click the tab named usage to set your refresh preferences, including:
- Enable background refresh: This option is checked by default and ensures that your content is constantly refreshed.
- Every refresh: This option causes Excel to refresh the data only periodically. When choosing it, you need to enter the minute intervals you want this to happen.
- Refresh the data when opening the file: The data in the Excel file is refreshed only when you start it.
Step 7: Select OK to save your renewal preference.
Export Windows Directory and File Details to Excel
As long as your web data is in tabular format and you have the corresponding version of Microsoft Excel, exporting web data to your Excel should be easy. You can also export your Windows directory or file details to an Excel.
