How to Extract Data from Website to Excel Automatically

Get FREE Advanced Excel Exercises with Solutions!

Extracting data (data collection and update) automatically from a web page to your Excel worksheet might be important for some jobs. Excel gives you the opportunity to collect data from a web page. This is one of the most used Excel features for those who use Excel for their data analysis job. If you are working for a financial analyst company, you might need to get or import daily stock prices from a website to your Excel workbook for analysis. In this article, I will show you how to extract data from a website to Excel automatically in easy steps.

We will be using Excel’s From Web command in the Data ribbon to collect data from the web. For example, I want to collect data from this page. It is the webpage of the U.S Energy Information Administration showing information on Petroleum prices.

How to Extract Data from Website to Excel Automatically


Now, let us follow the below 4 steps to extract this data into Excel.

Step 1: Using Excel Power Query Tool to Insert Website Address

In the beginning, we will provide the website information in Excel.

  • First, go to the Data tab and select From Web in the Get & Transform Data group.

How to Extract Data from Website to Excel Automatically

  • Then, insert the web URL in the From Web dialogue box.

  • After that, press OK.

Step 2: Extracting Data Table from Navigator Window

At this stage, we will proceed to the main part of extracting data. Let’s see how it works.

  • First, you will see the Navigator window.
  • Here, select the table from the Display Options.
  • Along with it, you will see the preview in the Table View tab.

How to Extract Data from Website to Excel Automatically

  • Lastly, press on Load.
  • That’s it, you have successfully extracted data from the website automatically.


Step 3: Applying Excel Refresh All Command for Data Update

So now, as we have our data, we will need confirmation that it is linked to the website. Because we want regular updates in our Excel file whenever there is an update on the website. So here is the solution.

  • Simply go to the Data tab.
  • Then click on Refresh All every time you want an update.

How to Extract Data from Website to Excel Automatically


Step 4: Refreshing Data Automatically Within Fixed Time Limit

Data update is more flexible with the Connection Properties tool. Follow the process below:

  • First, go to the Data tab and select Refresh All.
  • Here, choose Connection Properties from the drop-down menu.

How to Extract Data from Website to Excel Automatically

  • Following, you will see the Query Properties dialogue box.
  • Here, you can adjust the time to decide when you want an update from the website in the Refresh Control section.
  • After that, click on OK.


How to Edit Extracted Data Table in Excel

So now, as we have our final extracted data, you can see that there are some blank cells that confuse the readers. Here is a quick editing solution for this extracted data table.

  • In the beginning, double-click on the table showing in the Queries & Connections panel.

How to Edit Extracted Data Table in Excel

  • Then, in the new window, select Down from the Transform tab.

  • As a result, it will create an option in the Applied Steps.
  • Here, select the Changed Type step.

How to Edit Extracted Data Table in Excel

  • Then, select Replace Values in the Transform group from the Home tab.

  • After this, agree to Insert in the Insert Step dialogue box.

  • Next, insert the Replace With value as null and keep the Value To Find box blank.

  • Afterward, press OK.
  • Now, select Filled Down as the Applied Steps.

  • Finally, you will see the whole table without any blank cells.

How to Edit Extracted Data Table in Excel

  • Lastly, change the table name from the Query Settings.

  • Then, press Close & Load.

  • That’s it. Here is the final output.


Things to Remember

The web page must have data in collectible formats like Table or Pre-data format. Otherwise, it would be another battle to convert it to a readable or Excel-table format. You see, the Text to columns feature isn’t always your best friend.


Conclusion

So, this was a complete guideline for you on how to extract data from a website to Excel automatically with easy steps. Let us know your insightful suggestions in the comment box.


Related Articles


<< Go Back to Web Scraping in Excel | Importing Data in Excel | Learn Excel

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.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

24 Comments
  1. Very Good!

  2. is the data in the web page is not highlighted with the yellow arrow, how do I download the data??

  3. thank you! just one question, what if we want to keep record of these data for a year?

  4. This works for windows Excel but not mac Excel. How do you do for mac Excel 2016

  5. Hi, I’ve exact opposite condition. How can I do? Excel to website using PHP.

  6. Very useful, I had this question in mind but never had the patience to search ( never wanted to either, necessity is the mother of all invention),now, when i searched I hit the jackpot in the first link that popped up in google search. What I searched for “how to show values from a website in a excel”. Thanks EXCELDEMY.com!!

  7. Thanks for finally writing about > How to Pull/Extract Data from a Website into Excel Automatically?
    < Loved it!

  8. Hi Kawser,

    I accessed the Google finance site in terms of your first screen print, however I cannot see any yellow squares with a red arrow to select any section of the page. Do have to turn on something to see these, I am using Excel 2019 Professional Plus version, on Windows 10 Professional 64 bit. I checked another web site, and also cannot see the yellow square. What do I have to turn on in Excel to be able see these ?

    • Hi Ron,
      You can see that yellow square with a red arrow in Microsoft Office Professional Plus 2016. In Excel 2019, you won’t find that because there’s no need to use the yellow box. Excel will automatically detect all the tables and make a list of ’em. All you need to do is, simply select any of the tables that you want to import and then load them directly into your Excel worksheet.
      Thanks!

  9. Hi Kawser,
    What would be the best way to do the opposite – that is pull data from an excel worksheet into a website?

    Thanks

  10. Hi, you mentioned how to export web data when the data is already in table-format. How would I do this when the data is not in table-format. Thank you!

    • Hi Anthon,
      If there’s no data table on a web page, Excel will import a default document data table into the worksheet. The document table is basically a null data table.

  11. Hi Kawser, Excel 365 Enterprise is not extracting table from Sharepoint site. My company provides Edge, Chrome or IE browser. Excel message is “You may be trying to access this site from a secured browser on the server. Please enable scripts and reload this page.” in spite Edge JavaScript is enabled. â‘  Does browser type matter? â‘¡ what can be done to Secured Browser ? â‘¢ any alternative other than VBA or Add-In. Thanks Ahead!

  12. How can I pull just the emails into an excel sheet without the name, address, phone etc. if they are not set up in a list. Does a program like that exist? I don’t want to have to copy and past each one! Thank you!

    • Hi Brenda,
      Follow this tutorial to fetch all the data tables from a web page. After selecting a particular data table, click on the Transform Data command to modify your data table in the Power Query Editor. There you can remove all the unnecessary columns and keep your desired data. Then hit the Close & Load button to bring the transformed data table into your worksheet.
      Thanks.

  13. Wonderfull content thanks for sharing

  14. what do I need to do when the website requires login credentials (login name and p-word)?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 22, 2024 at 6:10 PM

      Hello HANS ENGELS,

      Thanks for your query. To extract data from the website to Excel automatically when the website requires login credentials, you must apply web scrapping techniques and automation tools.

      First, you need to understand the web structure of that website and then use web scraping tools to get access to that website and extract data based on your needs.

      Regards
      MD Naimul Hasan
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo