Easily Import Data from Web to Excel (2 Practical Examples)

Easily Import Data from Web to Excel (2 Practical Examples)

Complete Excel Power Query course: https://courses.xelplus.com/p/excel-power-query
*** Note: On XelPlus you will get additional bonuses that are not available on other platforms. Check the course landing page for more info.

You’d like to bring data from a web page into Excel? Instead of copying and pasting, use “Get and Transform” (Power Query) from the “Data” tab. This way you can import website data properly into Excel. Once you create a web query you can automatically get the latest data in Excel. You just have to refresh your query and update the query settings so it runs a refresh at specific intervals defined by you.

Getting data from a web page has never been easier. Just Open Excel, go to the “Data” tab and click on “From Web”. Enter the web address of the external page you’d like to import. Power Query opens and tries to find the html tables on that web page. Select the correct table and transform your data. This step gives you the ability to customize your output. You can for example, fill in the blanks by automatically copying down the top values. You can remove the columns you don’t need and clean up the data. Once done, you can view the data in an Excel table, as an Excel Pivot Table or create a connection to the query.

Note: Importing web data with Power Query this way requires the tables on the web pages to be in HTML format – otherwise they’re not recognized in the Navigator view.

00:00 How to Import Data from Websites to Excel
01:15 Importing Crude Oil Spot Prices to Excel
04:59 Getting the Latest Weather Data into Excel
07:25 Bonus – Using Emojis in Power Query

______ Check out _______

Frédéric Le Guen’s blog post on adding emojis to your reports: https://www.excel-exercise.com/add-emojis-to-your-charts-formulas/

Oz’s video – Emojis, Excel, Power Query & Dynamic Arrays: https://youtu.be/c-tnA1zfrzs

⯆ DOWNLOAD the workbook (scroll to the bottom of the blog post): https://www.xelplus.com/import-data-from-web-to-excel/

Check out the Power Query Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-

★ My Online Excel Courses ► https://www.xelplus.com/courses/

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

Free Ebook

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593

Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!



  1. Robert Schwenk on December 8, 2021 at 1:10 pm


  2. Jagadeesh Kumar on December 8, 2021 at 1:13 pm


  3. Robert Cameron on December 8, 2021 at 1:13 pm

    Hey I have a question If I data scrape from a site that is a paid subscription when the subscription ended does the data still save in your excel or will it say #ref

  4. winter is coming on December 8, 2021 at 1:13 pm

    Thank you Leila!

  5. Abdulrahman Almukhaizeem on December 8, 2021 at 1:14 pm

    Thanks for the tip, Leila. It’s quite helpful indeed. Quick question, I tried copying data from a website and pasting them in excel but it didn’t paste in the right order (i.e, the data on the website were organized in a 4-column table but when I copied and pasted in excel, it pasted everything in one column) how can I organize the data and sort them in the right format/ columns given the large set of data? Would appreciate your input.

  6. Salim Ahmed on December 8, 2021 at 1:14 pm

    How can I export data from website by using MS excel 2007?

  7. SELF213 on December 8, 2021 at 1:15 pm

    I’ve tried this but some websites doesn’t show the table on excel. How can I import a table from those websites that doesn’t show the table in excel?

  8. Tue Pham on December 8, 2021 at 1:16 pm


  9. Integral on December 8, 2021 at 1:16 pm

    Mind = blown

  10. Samir Abaza on December 8, 2021 at 1:16 pm

    I used to use the currently known as legacy and it is not loading the page correctly also the now query table is not loading the table correctly. the only option left is TEXT or CSV view for HTML page and it is nightmare to extract the data from

  11. SixtyFive Roses on December 8, 2021 at 1:17 pm

    Excatly what I needed, thank you.

  12. Adarsh S on December 8, 2021 at 1:18 pm

    Maam ur presentation was awesome 👌 really a useful one

  13. Advance Point on December 8, 2021 at 1:18 pm

    hi i am facing issue data fetch in yahoo finance page to get data con you guide me

  14. Ravi Malpani on December 8, 2021 at 1:20 pm

    Hi Leila Thanks for this amazing video its really very easy to learn things from you, your explanations are simple to learn , can you help with the query, I want not to replace the previous data but new data to be added below the previous data, as I want to create a repository of daily price movement which further I want to connect to Power bi.

  15. Advance Point on December 8, 2021 at 1:21 pm

    Hi Leila I am trying to get data from Yahoo finance but unable to get table

  16. Md. Shihab Uddin Sabuj on December 8, 2021 at 1:22 pm

    When i want to import popup menu doesn’t’ show. What should i do. Direct import. Please help

  17. Ergin Kaya on December 8, 2021 at 1:24 pm

    You are realy great. You saved my day and job. Thanks a lot.

  18. Darshan Shah on December 8, 2021 at 1:27 pm

    Hi Leila, You videos are quite useful. I wanted to know is there any way of scapping data from websites which have image verification process.

  19. bigfriki on December 8, 2021 at 1:34 pm

    But what if the data or value I need from a website isn’t in a table?? 😕
    I’m trying to important the stock price of a stock in yahoo finance, but since it’s only shown at the top outside of a table, excel doesn’t recognize it. That’s really disappointing to be honest…

  20. Wale Adeniji on December 8, 2021 at 1:34 pm

    I was given such task before which I couldn’t… You just made my day
    Thank you Leila

  21. IT-Time on December 8, 2021 at 1:35 pm

    Can we change url dynamically like for 2021 and 2022 there are tow diff links. When i open table it replace with latest year data not old year data. What is the tips.

  22. Vaithiyanathan Pugalenthi on December 8, 2021 at 1:37 pm

    Good feature to know. Nice video. When I tried to use am getting some Javascipt error. Is there any pre-requite to take care before using this feature to extract data from web.

  23. Uday Radhe on December 8, 2021 at 1:38 pm

    awesome! love it!

  24. Erick Gutiérrez on December 8, 2021 at 1:38 pm

    I really like this solution, what happen if the web need credentials to access it?

  25. Heppu on December 8, 2021 at 1:40 pm

    The problem is sites nowadays have those cookies popups and I just get that. how to bypass it

  26. Rajiv K. Sharda on December 8, 2021 at 1:40 pm

    Excellent 👍

  27. M M on December 8, 2021 at 1:41 pm

    Can you do this on iPhone?

  28. Puspa Jha on December 8, 2021 at 1:46 pm

    This really helpful 👏

  29. 50bravo on December 8, 2021 at 1:49 pm

    ‘Or use Bing’

  30. its0KagemanxD on December 8, 2021 at 1:49 pm

    Hi Leila thank you for your videos, it has become a go to ressource for me. I work in an organisation where most are bilangual. One of theese languages are kaalalisut (greenlandic) spoken by very few people (worldwide). There is no google translate (for obviois reasons) but there are online ressources. Would it be possible to "livetranslating" a spreadsheet, by scraping the dictionary, but as the search result?

  31. Valdanno Fernandes on December 8, 2021 at 1:50 pm


  32. ATULYA SAHOO on December 8, 2021 at 1:51 pm

    Very useful…Thanks a lot.

  33. Btnmbah Uno on December 8, 2021 at 1:51 pm

    Good day Leila. Thanks for this wonderful video. Can I ask if it’s possible to import single data thru web query to Excel? and How?

  34. Ather ulhassan on December 8, 2021 at 1:53 pm


  35. Sherlock Holmes on December 8, 2021 at 1:54 pm

    Hai Leila can you make tutorial Excel with database MySQL so user can take data by Primary key?, thanks

  36. Bernardo Reyes on December 8, 2021 at 1:54 pm

    How wold you create an order processing status spreadsheet ?

  37. Paulo Oliveira on December 8, 2021 at 1:55 pm

    Great! But can I do something similar on sites that need to log in and navigate to the desired location, besides inserting parameters such as date?

  38. cyrus on December 8, 2021 at 1:55 pm

    Thanks Leila, How can import the entire data from database table with web URL link. The web page usually load few rows at a time and with your way, it only import the 1st page.

  39. Shawn Smith on December 8, 2021 at 1:56 pm

    this is Brilliant!! I had no clue that so much power was avail in Excel for web interfacing.

  40. TN NATARAJ on December 8, 2021 at 1:56 pm

    In Excel – Page Layout, – Page Setup – Sheet -Print Titel – Rows to repeat at top – I Want Row No. 1 & 7 & 8 witll Page header too alll Pages How to Set up. ???

  41. hrshbht on December 8, 2021 at 1:56 pm

    Hello Leila… I am working to get webpage data in excel; however, do not see the option "Get Data from Web" in my Excel (working on macOS).
    Request your help running same thing on macOS please.

  42. Knowledge Voyage on December 8, 2021 at 1:59 pm

    Very enjoyable and user friendly

  43. Saiful Rehmani on December 8, 2021 at 2:00 pm

    Hi I needed to give an array which is dependent on another Sheet- lets say Sheet1 B10 & C10 Value. Example- Instead using =Sum(A1:A300) formulae I want to use =Sum(A&’Sheet1!’B10:A&’Sheet1!’C10) kind of formulae but it’s not working!! any help regarding the same will be appreciated

  44. Rama Perumal on December 8, 2021 at 2:00 pm

    Hi Leila, thank you for the video. I am wondering if you have any video on hot to connect a webpage with login and then pass parameter to get the data.

  45. Umar Hayat Khan Lodhi on December 8, 2021 at 2:02 pm

    Asalam’alaykum! as always helpful

  46. Leila Gharani on December 8, 2021 at 2:04 pm

    If the external data source requires a password you can add that in "Data Source Settings" – check out this page: https://support.office.com/en-us/article/data-source-settings-power-query-9f24a631-f7eb-4729-88dd-6a4921380ca9 (If you don’t see the option in the Ribbon, go to Power Query, File, Options and Settings, Data Source Settings – Edit Permissions – Edit – Basic – The input username and password. )

  47. Marc Z on December 8, 2021 at 2:06 pm

    Hello Leila,
    Thank you for your video, but i have a small question. I did all of the above and everything was working perfectly fine for a few weeks up until now.
    The data is not getting updated anymore and i’m receiving this message: "The internet site reports that a connection was established but the data is not available".
    Could this problem be from the website ? I did a lot of research on this issue but i couldn’t find a solution. Please help!! 🙂

  48. Andrea B on December 8, 2021 at 2:07 pm

    Leila, I admire you! You are an outstanding & beautiful person. Very impressive!

  49. Abdullah Adawi on December 8, 2021 at 2:08 pm

    Can we do the same with twitter?
    I need to import the stats of some accounts, how can I do that?

  50. Chaplain Stephen Strickland on December 8, 2021 at 2:08 pm

    QUESTION: You like a Power Query but are asked for an additional item by boss….. Can we go from current form to adding fields?