How to import data from the web into Microsoft Excel

Microsoft Excel is a very important tool for many to create spreadsheets. You can add data to examine, calculate, and analyze further. In addition to adding it manually, Microsoft Excel allows you to import data directly from the web.



How to import data from the web feature image

Importing data from the Web into Microsoft Excel eliminates the need to manually populate the data or copy and paste it. After you connect Excel to the web data source, it imports the information you need and updates itself if there are any changes. Here’s everything you need to know about importing data directly from the web into Excel.

Important points to note when importing data from the web into Microsoft Excel

You must meet two conditions 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 in an HTML table. If the data is not in a table, Power Query will not identify it.

Also on Guiding Tech

How to import web data into Microsoft Excel

Using the Power Query feature of Microsoft Excel, you can import data presented in tabular form on a website into a spreadsheet. This is how it works.

Step 1: Launch Microsoft Excel on your computer.



How to import data from the web into Excel step 1

2nd step: In the Ribbon interface at the top, click Data.



How to import data from the web into Excel step 2

Step 3: In the group labeled Get and Transform Data, select From the web.



How to import data from the web into Excel step 3

Step 4: In the pop-up window with the label From Web, enter the URL of the web page in the Address text box.



How to import data from the web into Excel step 4

Step 5: After pasting the link, click OK to load the web page in the browser.



How to import data from the web into Excel step 5

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



How to import data from the web into Excel step 6

Step 7: Once Microsoft Excel has finished loading the data, you should see a Navigator window. Select one of the tables listed under the Navigator to check if your data has been imported.



How to import data from the web into Excel step 7

Step 8: Select Load to have it appear on your spreadsheet if you are satisfied with the way the data was imported from the web.



How to import data from the web into Excel step 8

That’s all there is to importing web data into Microsoft Excel using the web. If you want the data to be constantly updated based on changes to the website, read the web data refresh below.

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 want the refresh to be performed. Here is what you need to do to refresh web data in Excel.

Step 1: Launch the worksheet that is connected to the web page.

2nd step: Click on the table.



How to import data from the web into Excel step 9

Step 3: On the ribbon, click Table Design.



How to Import Data from the Web into Excel Step 10

Step 4: In the External Table Data group, click the Refresh drop-down list.



How to import data from the web into Excel step 11

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



How to Import Data from the Web into Excel Step 12

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

  • Enable background refresh: This option is checked by default and ensures that your content is constantly updated.
  • Refresh all: This option ensures that Excel only refreshes data for specific periods of time. When selecting, you will need to enter the minute intervals you want this to happen.
  • Refresh data when opening file: The data in the Excel file is not refreshed until you launch it.


How to import data from the web into Excel step 13

Step 7: Select OK to save your refresh preference.



How to import data from the web into Excel step 14

Last updated Sep 24, 2021


The above article may contain affiliate links that help support Guiding Tech. However, this does not affect our editorial integrity. The content remains impartial and authentic.

Source link

About Dwayne Wakefield

Check Also

Importers have a 24 hour window to clear goods before benchmarks are removed

Economic news for Wednesday 5 January 2022 Source: www.ghanaweb.com 2022-01-05 President of the Association of …

Leave a Reply

Your email address will not be published. Required fields are marked *