How to Import Table from Website to Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is indeed an extremely effective software. Using Excel’s resources and features, we may perform countless operations on a given dataset. We frequently need to import tables from the Website to Excel. When we copy table data from websites to Excel, information from websites needs to be updated in the Excel file, making the procedure very time-consuming. This article will discuss two easy ways to insert tables from websites to Excel. Therefore, you should go through these 2 Easy Ways to Import a Table from a Website to Excel.


Import Table from Website to Excel: 2 Easy Ways

As an illustration, we will explore a sample website from where we will fetch a Table. In this case, we will visit microsoft.fandom.com. The following website, for example, has a table labeled Windows Servers containing two columns Name and Release date. Using these two approaches, we will import this table from this website to Excel. In addition, I have yet to mention that I have been using the Microsoft Excel 365 version for this article. You can choose any other edition that you find most convenient except the Earlier Version before 2016.

import table from website to excel


1. Utilize Power Query to Import Table from Website to Excel

Excel’s Power Query is a data analysis tool that enables users to input data from various sources and then clean, convert, and reformat it as necessary. It allows you to build up a query one time and then reapply it after a simple refresh. The greatest thing about Power Query is that you do not need to know or use any code to perform any of it. In this context, we will fetch a table from the website mentioned earlier. Moreover, we will import specific data columns from the table. To complete the task, please follow the instructions below.

STEPS:

  • First of all, in the active sheet, choose cell B4.
  • Next, go to the Data tab, followed by From Web.

Utilize Power Query to Import Table from Website to Excel

  • Due to this, the From Web window will pop up.
  • Then, In the URL Box, input the URL of the Website you want to import tables.
  • Next, hit OK.

  • Subsequently, the Navigator window will pop out. The left section of this window contains all the tables. In contrast, the right section allows us to view the table.
  • For this context, choose Windows Servers. In this case, we will find an unnamed column. We will import all columns except for this column.
  • To achieve this, go to Transform Data.

  • Afterward, the Power Query window will open.
  • Then, right-click on the top of the column.
  • Now, select Remove.

Utilize Power Query to Import Table from Website to Excel

  • Next, go to Close & Load, followed by Close & Load To.

  • Latterly, the Import Data window opens.
  • At this time, check the Existing worksheet, then hit OK.

  • As a result of it, the following outcome will occur.

Output of Utilizing Power Query to Import Table from Website to Excel

Read More: How to Scrape Data from a Website into Excel


2. Import Table from Website Through Excel VBA

VBA is an abbreviation for Visual Basic for Application. VBA is a programming language established by Microsoft. Users can utilize the VBA programming language to access Excel-incompatible functions. We will use VBA in this part to import a table from a Website into Excel. It is essential to note that the active sheet must be opened as Sheet1. In other words, the sheet where we want the table to appear must be the first sheet among all other sheets in the workbook. Please follow the directions below to carry out the task correctly.

STEPS:

  • To begin, choose the active sheet of the workbook as Sheet1.
  • Second, navigate to Developer.
  • Then, click Visual Basic.

Import Table from Website Through Excel VBA

  • After that, select Insert, followed by Module.

  • Next, enter the following code into the Module Box.
Sub SOFTEKOimporTablefromWeb()
Dim Data As QueryTable
Dim URL As String
URL = "https://microsoft.fandom.com/wiki/List_of_Microsoft_products"
Set Data = Sheet1.QueryTables.Add( _
                Connection:="URL;" & URL, _
                Destination:=Sheet1.Range("B4"))
With Data
.RefreshStyle = xlOverwriteCells
.WebFormatting = xlWebFormattingRTF
.WebSelectionType = xlSpecifiedTables
.WebTables = "2"
.Refresh
End With
End Sub
  • Make sure to change both the Range and the URL you want to visit.
  • After that, press F5 or click the Run button to finish.

  • As a consequence of it, the following will be the outcome.

Output of Importing Table from Website Through Excel VBA


Download Practice Workbook

The example workbook used during the session is available for anyone to download at no cost.


Conclusion

You are now able to Import a Table from a Website to Excel by making use of the ways that we have just covered. If you think of alternative methods to get the work done, please share those thoughts and suggestions with us. If you have any questions, comments, or recommendations, please leave them in the space provided below.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

2 Comments
  1. Thanks for free code and it works for your website.
    However it did not work for Concur Expense;
    Error message as follows:
    “This Web query returned no data. To change the query, click OK, click the arrow on the name box in the
    formula bar, click the name of the eRernaI data range for the Web query, right-click the selection, and then
    click Edit Query. ”

    I suspect User login is a problem.
    Would you help me modify code for above website?
    Thanks and Kind Regards,

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Nov 30, 2023 at 7:16 PM

      Hello GFIN SUNNY

      Thanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your problem.

      Unfortunately, using VBA to automate web interactions with sites that require authentication is not easy when it requires login. You may need a more advanced approach to handle authentication, such as sending HTTP requests with the necessary credentials.

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo