Easily Import Data from Web to Excel (2 Practical Examples)
ฝัง
- เผยแพร่เมื่อ 13 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover the ease of importing data from websites directly into Excel. Our tutorial demonstrates how to use Excel's 'Get and Transform' feature (aka Power Query) to seamlessly connect, transform, and load web data for analysis.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/web-query-file
✨ Key Highlights:
▪️ Simple & Dynamic Data Import: Learn how to import various types of data, such as petroleum spot prices and weather forecasts, in a few clicks.
▪️ Data Refresh with a Click: Experience the convenience of refreshing imported data with just a click, ensuring you always have the latest information.
▪️ Practical Examples: Follow two real-world examples to understand how to dynamically link Excel to web data sources for continuous updates.
▪️ Transforming Data in Excel: Discover how to clean and adjust data within Excel using Power Query, including filling down empty cells and adding emojis for enhanced visualization.
▪️ Auto-Refresh Options: Explore how to set your Excel file to automatically refresh data at regular intervals or when the file is opened.
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: www.excel-exercise.com/add-em...
Oz's video - Emojis, Excel, Power Query & Dynamic Arrays: • Emojis, Excel, Power Q...
Check out the Power Query Playlist: • Excel Power Query (Get...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/web-query-file
Thank you, Leila, I love the way you present things in such a simple and clear way!
Best part of the video is when you said; "it is just part of Excel"! That seems to be our main job as Excel Trainers, to convince Excel users that Power Query is the greatest Excel Invention since the PivotTable : )
Very well said Mike! The name power query does scare people away just like pivot tables. If pivot tables were called one-click-reports I think more people would be using it... that‘s probably why Microsoft renamed power query to
„get and transform“ when they put it in the data tab.
I find it's really inflexible like all external lookups, even to other excel files. I had lots and lots of trouble editing an existing query, for example. Ended up creating a whole new query from scratch, instead.
@@borstenpinsel You should probably check out some videos on it. It's very straight forward to use. More so with the intellisense that comes in the latest versions. When just using 2016, I do find I have to google the correct formula syntax due to lack of assistance, but that's probably a good way to learn too. Make sure you read the formula bar each time you create a step to get an understanding of the formula syntax and how you might tweak it for your queries.
@@borstenpinsel Power Query is super flexible. I don't think there's anything that it can't do.
Better than pivot tables
Leila, you are amazing! Thank you for open to us this precious jewel
what i like about Leila is, she has a sense of art beside Excel technics.
She has what I want
... Excel knaaawledge
...and she's easy on the eye.
THANKS FOR ALL YOUR WONDERFUL VIDEOS! yes, I am shouting from the rooftops about your wonderful teaching skills!
Thanks so much for your videos, Leila. They have been so timely and have enabled me to make so much progress with my project.
i have been trying to learn different skills over the past few months. But all where left incomplete half way in the journey. Today started with your videos. Have been practicing them since morning. Very effective. Concise, to the point and more importantly they address real time work challenges. Thank you very much
My pleasure.
THIS IS ONE OF THE BEST VIDEOS I´VE EVER SEEN ABOUT EXCEL POWER QUERY. THANKS A LOT.
Leila is a combination of an artistic and techy person.. also, the best thing about her is she gives credit to whom she got the idea.
Amazing, Thankyou for sharing your knowledge and explaining so well.
Thank you, that was really useful. it will definitely save me a lot of time!
Great video. Quick and clean. Thanks!
More amazing Excel majic described by Leila. What a wonderful world.
Nice. I learned something new. I also wanted to applaud you for giving credit for the work you borrowed from someone else. So many TH-camrs just steal and claim content as their own
Wow. This is a very sophisticated reporting format. Thanks for teaching these kind of useful things...
Amazing....
I was given such task before which I couldn't... You just made my day
Thank you Leila
Wow! I didn’t think this was possible in Excel. Thank you.
Now that was 'Transformational' !! Great tutorial
This is amazing! Exactly what I've been wanting to do with large budget and actual datasets. 🎉
Nice! As always, you're full of brilliance.
Question: Can you share with us some tips on how we can select a specific date such as MTD Report from the examples you've shown us?
I started to get used on your vedios, thanks thanks thanks a lot.
You are really great inspire for all content creators.
Hi Liela,
Thanks for this excellent video, it makes it simple to understand how to get data form a web page.
I'm sure it will save me a lot of time.
Regards
Enrique
PD: Your English is really clear and your pronunciation makes this video understandable for everybody.
Thank you for explaining this so well!
You're very welcome!
Hi Leila. My name is Julio and I live in Brazil. I'm studying a lot about Excel. I need to give my career a boost and I believe that Excel will help me a lot.
I love your channel and the way you teach. Congratulations.
Thank you, Leila, for making things easy.
Great content!Thanks so much!
brilliantly explained will definitely be enrolling for the course thanks
Absolutely amazing. Never knew excel had this feature.
NOPE and i am thrilled to discover this. What a Power Tool at hand.
Amazing, very good. Thanks Leila!
Am always amazed with your videos
Thank you Leila
😀 happy to see this short entertaining tut, well done
This is super. Thanks a lot Leila Gharani. I keep checking your video tutorial.
Thumbs up for the last trick.
It was amazing to use emojis in power query.
Thanks
Glad you like my little umbrella :)
Wow, this is super useful!
Thanks for the help! Super useful 👍
You are amazing Leila! Thank you for these tips!
My pleasure Gustavo.
You are AWESOME! Thank you!
Thank you for this, much appreciated!
Really appreciate your teachings.
Always great practical knowledge for learning.
Excellent video! Thank you!
this is quite helpful! saved me lots of time. thank you so much!
That was very helpful. Always bee looking to start with the power query, I feel your explanation made it look like it's easy so now I will give it a try.
Keep inspiring :)
Mind blowing...! Saving money to buy your black belt course and add value to my life.
Beautifully explained, Excellent
Good info Leila. Keep it coming.
Thank You... Learnt a lot
While carefully reviewing all your videos (after discovering your amazing channel), I realize about this functionality. So,... I only can say to you, Thank You Very Much. Based on it I got a bunch of ideas that will make me progress in my complex reporting.
I often say that if EXCEL would suddenly disappear, the whole world would have a deep impact at all levels.
In the same way, I want to say that your video courses, your teaching skills, and advice make the world get a bit better.
Faithfully Yours.
Thank you! I'm happy the tutorials are helpful.
Lifesaver. Thank you!
wooow it's amazing !!! I've been looking for this for a while
thank you so much Leila
My pleasure. Glad to help :)
Very useful...Thanks a lot.
I am learning not only Excel, but how to organize a video about a concept or concrete theme, and try to explain in a short time. Really impressive Leila, you have a special touch.
I'm glad to hear that Francisco!
@@LeilaGharani This is a dream
Amazing leila thanks a lot, you rock!!
Thank for the video, so interested.
How cool it is to have emoji in Power Query! That's really creative!
I am a regular watcher of technology videos but the you explain things is just awesome thank you so much your initiative
If the external data source requires a password you can add that in "Data Source Settings" - check out this page: 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. )
Was wondering if you could please help me. I have Excel for Mac and there appears to be no DATA>GET DATA >FROM WEB. Mac version instead has GET EXTERNAL DATA and then RUN WEB QUERY. How do I get this done on Mac. Thanks.
Leila, you’re the total package - beautiful, brilliant, and an Excel nerd! :) 😍😍😍 Marry me?
Hi,
Is It possible to refresh based based on a cell change ? For ex if i fill cell E4 the refresh is done automatically? Thanks
I have 1000000 lakhs same data in 4 sheet how can i marge in one pivot
Thank you so much! I will try...
I had no idea this was possible. Thanks!
Excelent video. Thank you.
Amazing video!!
That was amazing, didn't even know about that....
Like information feeding document not information breeding like make new documents? Like copying deleting paste share like that? Do those deleted files return if use special program? Emcase you wanted them back? I'll see you later on until next post? In next months?
@@brandonhowarth2402 for sure I knew that way for easily importing data from web, I wonder for the fact, that I have to give up thumb and subscribe if I like this video. That's Amazing, never knew about this option on TH-cam.
this is Brilliant!! I had no clue that so much power was avail in Excel for web interfacing.
*THANK YOU* *THANK YOU* *(NEW SUB)*
Thanks for the sub!
Absolutely amazing, thankkkkkk you Leila for this tutorial
You're very welcome.
Bravissima!!! Super clear and useful!
Thank you Leila, nice and easy way to explain!!
You're very welcome 😊
Excatly what I needed, thank you.
Great stuff - thanks a lot LG!
Thank you once more.
Very useful ! First class tutorial
Wunderschön, Leila, das wäre genau was ich brauche. Viele liebe Grüße 🌞
Super! Freut mich :)
Excellent tutorial. Many thanks.
Thanks for the awesome video.
Hi Leila. Great tips! Love the umbrella :)) Thanks for sharing. Thumbs up!!
Haha, it's raining a lot here 😁
Predictive Excel so easy with YOU 🌟
Thanks for this video
Extremely useful 👍👍👍
Leila you always amazed us with your versatile brain 🧠. You beauty with the brain.
Thanks a lot Leila ! 🙏
Thanks again love it !!
I love this video! I'm beginning to build an Access Database, and I think a light bulb just turned on for me. Thanks so much!
Great to hear, Bryan!
That was literally phenomenal... Excel is really a power tool for entrepreneurs such as myself.
👍
Nice clear and concise explanation. I real like your videos.
You are realy great. You saved my day and job. Thanks a lot.
Leila, amazing video, super well explained, thank you/muito obrigado!
My pleasure, Paulo!
I really appreciate your efforts
It's my pleasure
Very cool features. Thanks Leila!
agree - it's a really cool feature.
Thank you Leila. Especially like emoji part :)
This is fun and amazing with emoji Laila. Thank you so much.
Wow! That is amazing!!! 👍🏻👍🏻👍🏻
Thank you very much
Power Query is awesome. Thanks Leila..
It sure is! Thank you for your support Matt.
I love the way you talk
Thanks a lot for the video!
YOU are great
Thanks alot
Wonderful!
Maam ur presentation was awesome 👌 really a useful one
an amazing content and yet you can explain to us in a very2 simple way, what an amazing teaher you are, thanks a lot leila
I appreciate that!
you are really genius EXCEL world no.1
☔☂☂ very nice video . thanks Leila