How to Extract Data from Website to Excel Automatically

We want to collect data from webpage of the U.S. Energy Information Administration showing information on petroleum prices.

Step 1 – Using Excel Power Query to Insert a Website Address

Go to the Data tab and select From Web in the Get & Transform Data group.

Insert the web URL in the From Web dialog box.

Press OK.

Step 2 – Extracting the Data Table from the Navigator Window

You will get the Navigator window.
Select the table from the Display Options.
You will see the preview in the Table View tab.

Press on Load.

Step 3 – Applying the Excel Refresh All Command for Data Updates

Go to the Data tab.
Click on Refresh All every time you want an update.

Step 4 – Refreshing Data Automatically Within Fixed Time Limit

Go to the Data tab and select Refresh All.
Choose Connection Properties from the drop-down menu.

You will get the Query Properties dialog box.
You can adjust the time for a periodic update from the website in the Refresh Control section.
Click on OK.

How to Edit an Extracted Data Table in Excel

Double-click on the table in the Queries & Connections panel.

In the new window, select Down from the Transform tab.

This will create an option in the Applied Steps.
Select the Changed Type step.

Select Replace Values in the Transform group from the Home tab.

Agree to Insert in the Insert Step dialogue box.

Insert the Replace With value as null and keep the Value To Find box blank.

Press OK.
Select Filled Down in Applied Steps.

You will see the whole table without any blank cells.

Change the table name from the Query Settings.

Press Close & Load.

Here is the final output.

Things to Remember

The web page must have data in collectible formats like a Table or Pre-data format. Otherwise, you’ll need to convert it to a readable or Excel-table format, which might take a while.

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

Get FREE Advanced Excel Exercises with Solutions!

24 Comments

Anand Mar 4, 2017 at 6:12 AM

Very Good!
MAB Aug 6, 2017 at 6:10 AM

is the data in the web page is not highlighted with the yellow arrow, how do I download the data??
Mat Jul 4, 2018 at 9:38 AM

thank you! just one question, what if we want to keep record of these data for a year?
- Reply
  Kawser Jul 4, 2018 at 12:17 PM
  
  You have to use VBA or you can use Power Query.
  - Reply
    Vikram Chougule Apr 17, 2021 at 4:09 PM
    
    how to use chrome instead of ie to fetch data in web query
    - Kawser Apr 18, 2021 at 1:48 PM
      
      We shall cover this topic soon.
      Thanks.
EstesParkCO Feb 19, 2019 at 3:36 AM

This works for windows Excel but not mac Excel. How do you do for mac Excel 2016
- Reply
  Patricia Godfrey Feb 3, 2021 at 2:23 AM
  
  following for answer
Ignite Feb 20, 2020 at 9:18 PM

Hi, I’ve exact opposite condition. How can I do? Excel to website using PHP.
Vishnu Aug 23, 2020 at 12:29 PM

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!!
- Reply
  Kawser Aug 25, 2020 at 10:58 PM
  
  Hi Vishnu,
  Thanks for your nice words.
  Best regards
popcorntime Oct 28, 2020 at 12:07 AM

Thanks for finally writing about > How to Pull/Extract Data from a Website into Excel Automatically?
< Loved it!
Ron Dec 8, 2020 at 2:02 PM

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 ?
- Reply
  Mrinmoy Sep 4, 2022 at 1:10 PM
  
  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!
david Jan 22, 2021 at 10:54 PM

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

Thanks
anton Apr 18, 2021 at 10:53 PM

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!
- Reply
  Mrinmoy Sep 4, 2022 at 1:19 PM
  
  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.
sunny Apr 19, 2021 at 10:54 AM

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!
Brenda Aug 19, 2021 at 10:12 PM

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!
- Reply
  Mrinmoy Sep 4, 2022 at 1:24 PM
  
  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.
Masab Shabbir Aug 25, 2022 at 6:58 PM

Wonderfull content thanks for sharing
- Reply
  Mrinmoy Sep 4, 2022 at 1:25 PM
  
  Hi Shabbir,
  Thanks for your nice words!
  Best regards.
Hans Engels Feb 21, 2024 at 5:32 AM

what do I need to do when the website requires login credentials (login name and p-word)?
- Reply
  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