Import data into Excel

Keyboard Shortcuts

If you work with colleagues who don’t have access to Microsoft Excel, you can have them export their data to a text file or a comma separated value file. In this video, the instructor shows how to import data into Excel.

- [Instructor] If you work with colleagueswho don't have access to Microsoft Excel,then you can have them export their datato a text file or a comma-separated value file.In this movie, I will show you how to bring incomma-separated value data and also how toimport other data into Excel.I'm starting out in the chapter four folderof the exercise files collection,and I want to do two things in this movie.The first is to bring in datafrom the DataSource comma-separated value file,and then also from the DataSource texttext document.

A comma-separated value file, as the name implies,has individual values that are separated by commas.This is the most common file that is used for data exchangeamong otherwise incompatible programs.You lose all of your formatting,but the data comes through just fine.And the great thing about Excel 2016is that you can just double-click a comma-separated value,or CSV, file to open it.So if I double-click the DataSourcecomma-separated value file, it just opens.

So that's a great way to work.I have my data, and it's ready to go.Now I'll press Control W to close that,and I'm back in another file,which is the import workbook.And this workbook is also in the chapter four folderof the exercise files collection.From here, let's say that I want tobring in text from the DataSource text documentin the chapter four folder.To do that, I will go to the Data tab of the ribbon,and from there I will go to the far left sideof the Data tab of the ribbonand click the Get External Data buttonand from there I will click From Text.

In the chapter four folder of the exercise files,which appears in my Import Text File dialog box,I see two text files.The first is DataSource, that's the CSV filethat I looked at earlier.And then also DataSource text.In this text document,I have the same values as in DataSource,but instead of them being separated by commas,they're separated by tabs.This type of file isn't that common any more,but if you work with older data sets,then the techniques that I show you will be very useful.

So I'll click DataSource text, and click Import.Doing so starts the Text Import Wizard.The first thing I need to know iswhether the data is delimited.In other words, is there a particular type of characterthat is used to separate the fields andis not used at any other place in the data set.In this case, we're using tabs to separate our values,so selecting Delimited is correct.I do want to start the import at row one,and if I look at my preview, I see that I do have headers,Year, Quarter, Month, City, and Revenue.

So I will check the My Data Has Headers checkbox.And I'll click Next.And here I see the Text Import Wizardasking what the delimiting character is.In this case, it correctly guessed that we have Tab,and we'll say I don't need to treatconsecutive delimiters as one.In other words, if a cell would be blank,then you would have a tab followed by a tab.If there were any blank cells,they would be delimited properly.And under Data Preview, everything looks good.

I have my year, my quarter, my month,my city, and my revenue all separated out correctly.So I'll click Next.On this step of the Text Import Wizard,you can apply individual formats to the values in each cell.In this case, they're all set to General,and when you bring the data into Excel,if it were years or dates or currency values,something like that, then Excel would be ableto apply the proper format for you.In this case, General works for everything,so I will click Finish, and I will import the datato my existing worksheet in cell A1 and click OK.

And there's my data.I can now use it in my calculations,I could create an Excel table based on this data,and I can go on and do my work.

Resume Transcript Auto-Scroll

Author

Released

4/26/2017

Starting to build a spreadsheet from a blank worksheet is less intimidating if you are familiar with functions and how to create formulas in Excel. This course covers creating formulas, managing named ranges and tables, importing, exporting, validating data, and more. Learn how to maintain the integrity of your original data, audit your formulas for correctness, and adjust values to be able to calculate how to reach outcome goals.