How to Easily Import External Data into Excel & Import Data from the Web

No video

How to Easily Import External Data into Excel & Import Data from the Web

Рет қаралды 509,092

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Discover how to optimize your data handling in Excel using two powerful features: Text to Columns and External Data Import. Learn to efficiently manage complex data sets, overcome common issues like varying decimal and thousand separators, and even automate data refresh from web sources.
This video is a practical guide for anyone dealing with data exports from systems like SAP or Oracle, aiming to streamline their Excel workflow.
If you'd like to learn the newest version of this feature check out Power Query: • Master Excel Web Scrap...
✨ Excel Features Explored:
▪️ Text to Columns: A step-by-step guide to split a single column of data into multiple columns efficiently, ideal for separating names, addresses, or any combined data.
▪️ Importing External Data (CSV/TXT files): Learn how to import structured data files into Excel, controlling decimal and thousand separators, and maintaining data integrity.
▪️ Auto-Refresh and Connections: Understand how to set automatic refresh intervals for your imported data, ensuring your Excel sheets always reflect the most current data from external sources.
▪️ Web Query for Online Data: An introduction to importing data directly from web sources into Excel, with options for setting automatic updates.
00:00 How To Properly Import Data to Excel
01:16 How To Use Text to Columns in Excel
03:27 How To Import Data from CSV / TXT files to Excel
09:52 How To Import Data from The Web to Excel
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩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

Пікірлер: 170

@LeilaGharani 6 ай бұрын

Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter Discover the latest enhancements to import data into Excel here: kzfaq.info/get/bejne/e7WYhrJ1q7LVmZc.html

@nesizotha4007 5 жыл бұрын

I must say I am impressed. I started working in excel two months ago, I thought I was a five on a scale from 1 to 10 but now I think I am a 2. The amount of knowledge that you have is astonishing. And the fantasy too. You have to have an immense knowledge of excel to use formulas as you do. How long have you been using it? Ps. After 13 hours of working it's 2:10 am and I am watching all of your videos. Thanks for sharing your knowledge.

@quasipseudo1 7 жыл бұрын

You have identified an oft-underused aspect of Excel. Good job.

@sirzonsz5022 5 жыл бұрын

Thank you,your teachings are great for New Beginners in Excel

@waygone6657 5 жыл бұрын

This is all great stuff. You can connect to databases half way around the world and pull stuff into your spreadsheet. You can even do some macros that will rearrange the columns as needed and do calculations for you. Really powerful stuff.

@ianhutchinson970 6 жыл бұрын

I have interview on Thursday next week and this vlog has helped me enormously with the assessment I will have to complete. Thank you x.

@LeilaGharani 6 жыл бұрын

You're welcome! I'm happy to hear the content here helps with the assessment. All the best for your interview!

@hemlataponnada 6 жыл бұрын

You are one of the best excel expert. You pick good topics and explain it very well. I'm growing fond of excel more and more, thanks to you!

@LeilaGharani 6 жыл бұрын

Thank you for the kind comment. I'm glad you find the topics useful.

@dontask6239 2 жыл бұрын

I discovered you today and you answered my question straight away. Am about to test the data and My Word amd I glad I found you!

@sachinrv1 6 жыл бұрын

Wonderful. Usually we do only text to column; but the web import and other import is not something we use on recurring basis. This is therefore absolutely important video. Thanks for throwing light on the subject.

@LeilaGharani 6 жыл бұрын

Agree: it's not used that often. Glad you like it Sachin.

@prasadjilagam5740 6 жыл бұрын

Madam, I improved my self in excel because you’re videos. Thank you

@LeilaGharani 6 жыл бұрын

I'm VERY HAPPY to hear that! Many thanks for your comment & support.

@NHKprod 5 жыл бұрын

Note that the "From Web" Import is only available on Windows, not Mac. Great video as always Leila.

@meteora8888 6 жыл бұрын

Thanks Leila. This is excellent and will be very helpful for me! Keep up the good work!

@LeilaGharani 6 жыл бұрын

I'm glad you find this useful :)

@nabrii1924 5 жыл бұрын

Leila, u r such a darling! You just landed me to my exam room with a confident smile. Excellent job!!!

@LeilaGharani 5 жыл бұрын

I'm very happy to hear that! Good luck on your exam.

@sherrieffbey235 2 жыл бұрын

Thanks this helped a lot...

@christopheraaron1255 Жыл бұрын

What a difference a few years makes...

@manashmalakar 7 жыл бұрын

Your slowly step by step briefing method helps me lot. Fast showing is just shows your skills but not understand many people. thanks for helping us...keep going with the slow method.

@LeilaGharani 7 жыл бұрын

Thank Manash.

@manashmalakar 7 жыл бұрын

Can u pls send me your mail address... if any problem arise i send you. My mail id is smartmanash@gmail.com

@laissezfairez 7 жыл бұрын

You are such a great tutor, Thanks !!

@LeilaGharani 7 жыл бұрын

You're welcome Ester.

@BlackOcelot0095 4 жыл бұрын

Can you do a video on importing data from a website you have to sign in before you can view the data?

@HarpreetSingh1991 2 жыл бұрын

Hey, I find this very helpful & you were very calm throughout the video and yeah, subscribed to your channel. Love

@LeilaGharani 2 жыл бұрын

Thanks for subbing!

@prestige_247 Жыл бұрын

Thank you for this, really helpful

@ronspi 5 жыл бұрын

1:40 should be "highlight the cells" not 'highlight the column'. Great video.

@shironmania 5 жыл бұрын

Quite useful, Text to column does have many features as you have shown here and in other posts. Importing to excel was also handy but making changes in excel and re-export to system is often unexplored. Good one again

@LeilaGharani 5 жыл бұрын

Glad you like it.

@zouheirhajli6295 5 жыл бұрын

MANY THANKS FOR ALL THE THINGS I'VE LEARNED FROM YOU

@hattemghanoom9557 6 жыл бұрын

Very useful data import tips for Excel

@vimalprakash4115 2 жыл бұрын

Thanks a lot

@Waseq24 Жыл бұрын

سلام خواهر عزیزم لیلا جان شما واقعا افتخار ما هستید .

@GeekyAfros 2 жыл бұрын

Thank you ma’am great content

@naveensingh3267 3 жыл бұрын

You are best

@agent7807 6 жыл бұрын

saved me few hours. Kudos !

@LeilaGharani 6 жыл бұрын

That's great! Thanks for sharing.

@pcoladawg 7 жыл бұрын

Good job. Not exactly what I was looking for but you were very clear and I like the hot tip. I will watch for other videos as I continue to develop my process system.

@LeilaGharani 7 жыл бұрын

Thanks - I hope you also find what you were looking for.

@srikanthkasala7850 6 жыл бұрын

Respected Madam, Am fan of your videos and new idea thoughts… Am impressed valuable lessons… Really I tired lot and output was no result.. First off all am beginner of learner in excel... I need your help/idea in excel…. In work sheet1 I want two (2) things 1. Search box and display the output there from work sheet2/3/4 are etc. 2. Automatically transfer data and save the data from work sheet1 into work sheet2/3/4 are etc.. Whether it is possible to do like this I don’t know!! Am regularly watch your videos. I hope you and needful to me and who are following your's students of richest ideas and thoughts from your videos...

@simoiyahector-morales9521 5 жыл бұрын

excellent video, thanks alot for these easy to understand videos.

@LeilaGharani 5 жыл бұрын

I'm happy to hear this Simoiya! Many thanks for your support.

@hosseinhosseinpoor4845 3 жыл бұрын

Thanks leila.

@ModernPindawale 6 жыл бұрын

Hello ma'am can you show video web scraping and data scraping full video on KZfaq and use in which software easy to use

@lisamccray6481 4 жыл бұрын

Thanks for the hot chili pepper. Cool!. (dichotomy 😎) Nice visual aid.

@LeilaGharani 4 жыл бұрын

😊

@thisisshe2685 2 жыл бұрын

you save my ass for tomorrow meeting! thanks babe

@brandoncradeur5746 4 жыл бұрын

I thoroughly enjoy your excel training videos and appreciate that you began adding the excel file and step by step written instructions to refer to as a guide. I have a request. Many companies use quick books; however, the reporting is not as visually appealing. Is there a way for excel to connect directly to QB desktop (.QBB or .QBW source files) or online files in orders to make financial reporting and visualization to dynamic to easily prepare monthly financial reporting package? I watched your videos similar to this one, ability to connect to websites, and other excel files. But haven’t found anything that directly addresses my need. Any insight, instructional video or training would be greatly appreciated.

@Ms.MM084 5 жыл бұрын

Thank you thank you!! You are making me smart!

@LeilaGharani 5 жыл бұрын

You're very welcome. I'm glad to hear that :)

@majakbalang4407 4 жыл бұрын

I like the tutorial!!

@johng5295 4 жыл бұрын

Thanks in a million!

@SJ01Bharat 4 жыл бұрын

Thanks Leila

@mohammedsalem3657 Жыл бұрын

Wonderful, great job ضخمة بيم محد كماش لا

@mohmmadabdo5899 6 жыл бұрын

This what I need. .. Thanks

@mahmud4850 7 жыл бұрын

Great job. thanks.

@LeilaGharani 7 жыл бұрын

You're welcome Mahmud.

@narthseneviratne3585 7 жыл бұрын

I understood it very well & thank you!!!!!!!

@LeilaGharani 7 жыл бұрын

You're welcome Narth.

@RB-ez8vx 4 жыл бұрын

Thanks for the video. I need help on how to record the pulled data, that is periodically refreshed, on another sheet. For example, if I need to track a record of stock price movement automatically, how would I do that? If you already have a video on this, then please provide the link.

@eversut1 5 жыл бұрын

Thank you for the video.

@LeilaGharani 5 жыл бұрын

My pleasure.

@esantorcad5591 6 жыл бұрын

Excellent Video, Thank you!

@LeilaGharani 6 жыл бұрын

You're very welcome Esan.

@alndr4u 3 жыл бұрын

In Excel, sheet1 range B1:B200 contains many website links of zip files, download the zip file of all those links together and keep them in the same folder using vba, make a video for that.... Thanks

@desmondbong7470 5 жыл бұрын

Do you have a video to show how to insert number of column/columns according to the specified number. e.g input data = 5, insert 5 columns automatically. If not, will you do one please

@VikashPatel-ub8ke 5 жыл бұрын

Very useful

@poyahashimi 4 жыл бұрын

Thanks.

@ainewsupdates 5 жыл бұрын

While importing data from excel it is using internet explorer as a default browser. Actually, I want to change it to chrome . is it possible.

@ikemsinachidavid7015 6 ай бұрын

Hello. I enjoyed your lesson. I have a little problem. After importing the file and saving as csv file on my excel. The numbers I changed didn’t reflect on my work pad

@joerosson 3 жыл бұрын

Don't really care about what NOT to do or why. Care only about How to do something step by step. Skip to 1:15

@gevorg827 5 жыл бұрын

usefull video thank u

@LeilaGharani 5 жыл бұрын

Glad you like it George.

@harishkvideos4048 4 жыл бұрын

Hey.. i am learning from you via UDEMY also.

@akshayshah3730 4 жыл бұрын

In Excel any function to see data import source see and all activities done in Excel to see

@irontruck 5 жыл бұрын

Would be good to see other sources like sql and nosql

@r0ckdaddy 2 жыл бұрын

Is there a video on importing data from text throug office 365? Because that's just totaly different and i cannot figure out how to get my data straight.

@Deependra1991 5 жыл бұрын

How to import data from pdf file, plz make a video on that

@friendshipmedia7852 7 жыл бұрын

very helpfully this video

@LeilaGharani 7 жыл бұрын

Glad to hear that :)

@baglodepackaging367 4 жыл бұрын

export the paper packaging, paper bag, paper box, etc.

@bpolkusm 3 жыл бұрын

Do you have a video on how to import SharePoint list columns into a word doc stored in OneDrive for business

@michaelkulbe9930 4 жыл бұрын

Vielen Dank😍. Can you Show this with REST API. PLEASE. 🥺😍🥰

@ndelpurg 6 жыл бұрын

excellent video perfect and thank u

@LeilaGharani 6 жыл бұрын

You're very welcome.

@sukhmaidickoff 5 жыл бұрын

Thanks for the upload - it´s a nice tutorial. I had some challenges when importing stock prices to Excel via an external data source (an American website) and the price was quoted with a comma as a thousand separator and a dot as the decimal and my Excel had a European setup. Like you described in the video I just used used "Text to columns" and stated under "advanced" that I wanted the dot as a thousand separator and the comma as the decimal. Problem solved 😉

@LeilaGharani 5 жыл бұрын

I'm glad the tutorial was helpful Bryan. I hope the stock market is treating you well :)

@surendrasharma2042 3 жыл бұрын

@@LeilaGharani is there any excel tool to get automate specific data from gmail to spreadsheet . Like part from the mail........ order number, item number, price , feedback etc. Whenever get mail, automatic data pull in spreadsheet.

@jayevolutionx7169 2 жыл бұрын

Hello Thank you for the awesome video. could i use this to copy stock data exported from a point of sale from one csv to another template for import to a new csv? Thanx

@sajmosajmon5774 2 жыл бұрын

How to import data by function for example importxml and SAVE data automatically?

@aye.myatmon4485 Жыл бұрын

Can you show me how to make csv file

@InSync3912 8 ай бұрын

Why by importing csv file into ERP software - all my data end up in one cell? I do choose the right delimitor

@LEANAMH 2 жыл бұрын

Hi there, is there any way I can import Excel spreadsheet into Xplan document vault?

@dominic2446 3 жыл бұрын

0:42 0:52 technically speaking, "decimal separator" should have been used instead of "decimal" since decimal is a number whereas decimal separator is a symbol. however, i think "decimal separator" is a misnomer, since "thousands separator" is also a separator in a decimal. so, "decimal separator" should be renamed to "integer-fractional separator", as that term indicates where the separator is in the number.

@syedmohsin6349 2 жыл бұрын

We can use flash fill right??

@tpnphoenix3367 2 жыл бұрын

I want copy two different documents I want to copy the sheets of one excel project into the other with messing it up

@JJ_TheGreat 2 жыл бұрын

4:22 How would we import a .txt file into Excel which has NO structure at all - no coma separators, just a bunch of spaces, but no logic to it. There could be columns in the data, but the import file doesn't separate the text that way - and also has a bunch of repeating headers at the top of each page? I am just curious. Thanks.

@shubhrajit2117 3 жыл бұрын

Please consider adding timestamps to your videos

@LeilaGharani 3 жыл бұрын

Thanks for the feedback! I do have timestamps for my newer videos. I just added it also for this one.

@WaltKlonizchii 4 жыл бұрын

How do you trigger this process with a macro? I download a csv file daily from the web. The filename changes daily and has no extension. I perform the import manually by downloading the file to a local drive, then Data >> Import External Data >> Import Data >> browse to Downloads folder >> FileFilter = All Files (*.*) >> select file and click Open. I cannot get a macro to open the Select Data Source dialog box and default to the Downloads directory with FileFilter = (*.*) then allow the user to select the file to import. External Data Range Properties will also need to be specified as a new default. Maybe you've already posted a video on this? I have Excel 2003 with Visual Basic 6.5.

@kkt2921 4 жыл бұрын

Can I ask if you can use the text to column or even format data extracted from a website without risking the data running awry whenever you refresh the extracted dataset?

@vivekjoshi3769 2 жыл бұрын

Hello, I have a question. If we use data from web, & checkmark the box that says refresh data. Considering the example in the video will the stock market values in +/- get updated automatically ?

@anishbanerjee1642 3 ай бұрын

while i copy paste data from our company's ERP to Excel spreadsheet it does not work. Neither Vlookup, nor SUM formula are working. can you help me

@learningwithjoy9028 8 ай бұрын

These options r not in my excel,which excel is used to do this???

@maheshvinchhi3785 4 жыл бұрын

Hello Ma'am.. Is it possible to download excel file from web automatically and save it in a specific folder via macro or power query or with any other features?????

@shamshadjalali8445 4 жыл бұрын

hi, would be possible to do the same with google sheets, I have data in my laptop every time I add more data to the workbook, I would like the google sheet get updated as well, vice versa.???

@ATPRO92 7 жыл бұрын

I love that

@LeilaGharani 7 жыл бұрын

Glad you liked it.

@manashmalakar 7 жыл бұрын

and also pls upload the excel files with direct link where i/we download directly the video tutorial file without any hassle...

@LeilaGharani 7 жыл бұрын

The file for this lecture is included in my online course on Udemy. If the files are on the site, I will put the direct link in the descriptions.

@alirezamogharabi8733 7 жыл бұрын

سلام. ممنون از آموزش خوبتون شما ایرانی هستین؟

@afjalkhanpathan2696 5 жыл бұрын

i love you

@yogendramehta3245 4 жыл бұрын

Will this work to import all Utube video url data from monthly filtered history to an EXCEL file ?? Appreciate yr confirmation Thx

@santanuchanda3674 5 жыл бұрын

I wanted to import "column to comma" link into excel. Tell me if it is possible to edit that imported sheet and put my column wise data to convert into comma data?

@JMW376 4 жыл бұрын

Hi Leila I’m using Office 365. But when I tried to import the currencies web data (shown at the 10mins 33 mark in this video) the ‘navigator’ window in Excel wouldn’t let me highlight or copy the data. Do you know why that may be? Thanks in advance.

@RossCanpolat 3 жыл бұрын

Do I need to load an external data source in excel in order to use it in a data validation drop down list? Currently my external data is set to "Connection only" however when I try set the source of a data validation list to "=dbLIST", (the name I gave to the data source) it doesn't load the list for me & when I try define a name first in excel and refer that name to the data source and then use that name as the source for the data validation list it throws an error and the list won't load.

@amrahmed2157 6 жыл бұрын

Thanks for your efforts, Laila. Your way of how you introduce information is really interesting.. Do you have any videos or courses on into to Excel VBA or VBA for beginners?? Thanks for the help :) Amr

@LeilaGharani 6 жыл бұрын

Hi Amr, Thanks for your comment. Glad you like the videos. I'm currently working on a comprehensive VBA online course. It will take a few more months to be ready. I'm also planning to post some VBA content here. Is there anything specific you're looking to learn? You can find a good introduction to Excel VBA here: kzfaq.info

@bharatbhanushali 4 жыл бұрын

I have imported custom ribbons on my Excel having Macros. I would like to change or modify the references to the ribbon button. Could you advise how can I change?

@AmitSharma-po1zb 5 жыл бұрын

Hi ..hope u doing well..if I want to import data from a file whose extension is .pc how will I do it.. cos excel is not giving any such option to import data from these kind of files..

@huanglyful 3 жыл бұрын

What if the data is a formula, how do I import them as formulas? I am having the same issue with workbook connections where everything is raw data? Anyone can help with this please?

@kvakhranev 5 жыл бұрын

Hello, I have a VLOOKUP function that sources a separate file (Workbook), which gets rolled/saved daily with a NEW Date. I am using IDIRECT to change the Daily Roll date forward, in my VLOOKUP, daily. The limitation of this technique, requires to have both Workbooks open, for VLOOKUP to function. Is there a way to use IMPORT DATA functionality, for FileNames that change Daily? Effectively, I am attempting to eliminate the need to open multiple workbooks, to perform the VLOOKUP. I want to perform the VLOOKUP from tables, that update, when the data changes. Is there an effective way to direct IMPORT DATA functionality to change the source, on a Daily Basis?

@ajayv304 5 жыл бұрын

We Indians follow British system and use "," as a thousand separator and "." as decimal seperator

@princetamunonye4739 5 жыл бұрын

Nice one

@LeilaGharani 5 жыл бұрын

Glad you like it.

@supermelodia 4 жыл бұрын

How do I separate 1 column address into Street column, City Column, ZIP, STATE. One time I received a lot data more than 200 rows and the whole address was put in 1 column, I could leave like that in a emergency, but I need to split the address column mentioned above. I spend 2 hours to figure out or clean the address, what a mess. Example: 201 17th Street, apt 15, Anaheim, CA. i had to multiply the colums and search and replace. When it's between commas its easier to split, when street and city has no commas, difficult to clean by searching and replacing.