Excel's extracting data capability is not a new thing. However, it has become more flexible with Power Query. In this guide, we're going to show you how to extract data from a website in Excel.

Download Workbook

Extracting data from a website in Excel by using Power Query

1. Click From Web command under the Data tab, Get & Transform Data Section name might be different on your Excel because Microsoft has changed the name a few times.
   
2. In the From Web dialog, you can stay on the Basic option to extract data from a public website. The advanced option is for websites that are using parameters to generate data. If the URL of your website is like this website.com?country=US?state=NY, you can update "country" and "state" parameters dynamically by using Advanced mode.
   In our example, we stayed in Basic and use Wikipedia's FIFA World Cup 2022 address: https://en.wikipedia.org/wiki/2022_FIFA_World_Cup
   
3. Clicking OK opens the Navigator window where you can see the preview of existing data in tables and select which ones to extract.
   Our selection Group G.
   
4. You have two options:
   1. Load: Loads the data you see in the Navigator
   2. Transform Data: Opens the Power Query window where you can alter the data before populating in the worksheet.
      Because the first row of the data includes the titles, we are continuing with Transform Data button to remove that row.
5. Power Query has a built-in command that can convert the first row to the header. Clicking Use First Row as Headers button promotes the rows to headers.
   
6. You can see that there are no more duplicate headers. Also, Power Query automatically changed data types of columns like Pld, W and D from text to number because text values (header duplicates) are removed.
   Use the Close & Load button to populate the table in the worksheet.
   
7. Once imported you can use the values in your calculations as regularly. Do not make changes directly on the table, because Excel overwrites the table with up-to-date values every time the table (or data connections) is refreshed.
   
8. You have a couple of options to refresh the data:
   

    

   1. Shortcuts
      1. Ctrl + F5: refresh worksheet
      2. Ctrl + Alt + F5: refresh workbook
   2. Right-click menu
   3. Ribbon
      1. Data tab
      2. Query tab (Visible only if a cell in the table is selected)
   4. Of course, VBA
      1. ActiveWorkbook.Connections("test").Refresh 'Refresh specific connection named "test"
      2. ActiveWorkbook.RefreshAll 'Refresh all connections
9. See that the how the table is updated after Cameroon - Serbia (3-3) match.