Microsoft Excel is a very important tool for many people to create spreadsheets. You can add data for further review, calculation, and analysis. In addition to adding manually, Microsoft Excel allows you to import data directly from the web.
Importing data from the web into Microsoft Excel eliminates the need to manually enter data or copy data. After you connect Excel to a web data source, Excel imports the information you need and updates itself when changes are made. Here’s everything you need to know about importing data directly from the web into Excel.
Important things to keep in mind 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 data import. 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 in an HTML table. If the data isn’t in the table, 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 web page into a worksheet. Here is how it works.
Step 1: Launch Microsoft Excel on your computer.
Step 2: On the Ribbon interface at the top, click Data.
Step 3: In the group titled Get & Transform Data, select From Web.
Step 4: On the pop-up window labeled From Web, enter the URL of the website in the Address text box.
Step 5: After pasting the link, click Agree to load the web page on the browser.
Step 6: Microsoft Excel will ask you to confirm the data you want to download from the website, click Connect to confirm.
Step 7: After Microsoft Excel finishes loading the data, you will see the Navigator window. Select any of the tables listed in the Navigator to check if your data has been imported.
Step 8: Select Load so it shows up on your sheet if you’re happy with how the data is imported from the web.
That’s all it takes to import web data into Microsoft Excel using the web. If you want your data to be constantly updated in line with changes to your website, read below about how to refresh web data.
How to refresh web data on Microsoft Excel
As we mentioned above, when you import data from a web page into Microsoft Excel, it refreshes automatically. You can also set your preferences on how you want the refresh to be done. Here’s what you need to do to refresh web data in Excel.
Step 1: Launch the sheet connected to the website.
Step 2: Click Table.
Step 3: From the Ribbon, click Table Design.
Step 4: In the External Table Data group, click the Refresh drop-down menu.
Step 5: From the Refresh drop-down menu, you have three options, Refresh, Refresh All and Connection Properties. To set your preferences, click Connection Properties.
Step 6: In the dialog box named Query Properties, click the tab named Use to set your refresh options including:
- Enable background refresh: This option is ticked by default and ensures your content is constantly refreshed.
- Refresh every: This option ensures Excel only refreshes data for a specific time period. When you select this option, you’ll have to enter the minute interval that you want this to happen.
- Refresh data on file opening: The data in an Excel file is refreshed only when you launch the file.
Step 7: Select OK to save your refresh preferences.
Import Windows file and folder details into Excel
As long as your web data is in tabular form and you have the relevant version of Microsoft Excel, importing your web data into Excel should be simple. YOu can also import windows folder or file details into Excel.
Categories: How to
Source: thpttranhungdao.edu.vn/en/