Computer

Import data from any website with this Excel function

Excel offers the ability to aggregate data for in-depth review, calculation, and analysis. Perhaps the manual way is to add it manually, but this is very impractical. Therefore, we can import data directly from a website by using the Power Query tool.

What is Power Query in Excel

We are talking about a tool that is included in Microsoft Excel and that is responsible for helping us to simplify the process of data import from different sources to a spreadsheet in the most convenient format and then cleanse, transform and reshape this data as needed. Since it belongs to the Office office suite, it is a free add-on that is updated periodically.

Its main advantage and what makes it an extremely useful tool is that it fulfills the function of an ETL, that is, extracting, transforming and loading data. Its user interface is intuitive and well designed making it easy to learn. For this we will not need to know or use any code, so its use is much simpler compared to other tools such as formulas or VBA.

Among the main features of this tool we can mention:

  • The possibility of connecting to different data sources such as text files, Excel files, websites, databases, among others.
  • Transforms the obtained data based on the prerequisites of the report.
  • It allows you to save the data in an Excel table or simply connect to the data to load it later.

Also, if the source data changes over time, it is possible to update the results obtained and found in our spreadsheet with a simple click, so it is very useful if we work with data that can change frequently.

Aspects to take into account

If we want to import the data from a website to our spreadsheet, it is important that we take into account two important aspects so that it can be carried out satisfactorily.

  • Version we use of Microsoft Excel: In order to carry out the importance of data to our spreadsheet, we must use the Power Query function. In this way we can make the connection, transform, combine and load data. For this, it is necessary to use a compatible version of Excel such as Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 and Excel 2010.
  • Data format: Since we are going to import data from a website to Excel, for this to be done satisfactorily, it is necessary that they are embedded in an HTML table. Otherwise, Power Query will not identify them, so we will not be able to import them.

Once we are sure that we are going to be able to meet both requirements, we can start the import process.

Steps to import data to Excel

The first thing we must do is open Microsoft Excel and select an existing or new sheet where we are going to import the data. Later on your ribbon, click on the “Data” tab. Next, we go to the “Obtain and transform” data section that is located on the left. From here, we click on the section of “Get information”which will display a menu, where we select “From other sources” and “From the web”.

Excel get data from the web

This will open a new pop-up window called «From web», where we must paste the URL of the web page that we want to import into the “URL Address” text box. Once we click on “Accept”. In some cases, Excel will ask us to reconfirm the data that we want to upload to the website, so we must click on “Connect” to confirm.

excel url

Once Excel has finished loading the data, a new window called “Browser” which will show us the components of the web page in the left panel. Now we must select any of the numbered tables in the «Navigator» to verify if the data has been imported correctly.

Excel Browser table view

If we want to make sure we’re on the right page, we can click the tab “web view” to preview the page in traditional HTML format.

Excel Browser web view

In general, the listed components are not represented with obvious names that allow us to identify which element or component of the web page it refers to, so we will have to click on each one, so that we will obtain a preview of each element in the right panel, thus being able to find out which one belongs to the desired table. If we agree with the way in which the data from the web will be imported, we can click on “Load to” to send the data directly to Excel.

Excel Browser Load In

Later, a window called “Import data”, which will offer us to select how we want to see the data in the workbook, either in Table format, PivotTable Report, PivotChart or Create only the connection. We can also indicate where we want to place the data, whether in an existing spreadsheet or in a new one. Once selected, click on “OK”.

Excel import data

As a result we will see the table in our spreadsheet. It is connected to the panel «Consultations and connections» which is on the right. It lists all existing queries in this file. Hovering over the query brings up an information window. In it we will see a preview of the data, the number of columns imported, the date and time of update, how the data was loaded and the location of its origin.

Excel Queries and Connections

Update web data

As we already know, when we import data from a web page to Excel, it is updated automatically. In the same way, it is possible to edit our preferences indicating how we want them to be carried out.

To do this, from the worksheet that is connected to the web, we click on the table and on the ribbon we click on the “Table Design” tab. Here, within the “External table data” section, we click on the “Update” drop-down menu. Here, we will find three options: Update, Update All and “Connection properties”. We will click on the latter to set our preferences.

Excel Connection Properties

This will open a new window called “Query Properties”. In which we will click on the Usage tab to set our update preferences.

Excel Query Properties

Here we will find the Enable background update box checked, which guarantees that the content is constantly updated. We can also check the “Update every” box so that Excel only updates the data within a certain period. Finally, the “Refresh when opening the file” option allows the data to only be updated when we start the application.

Once finished, click on Accept so that our update preferences are saved.

Related Articles

Leave a Reply

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