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 colleagues who don't have access to Microsoft Excel, then you can have them export their data to a text file or a comma-separated value file. In this movie, I will show you how to bring in comma-separated value data and also how to import other data into Excel. I'm starting out in the chapter four folder of the exercise files collection, and I want to do two things in this movie. The first is to bring in data from the DataSource comma-separated value file, and then also from the DataSource text text 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 exchange among otherwise incompatible programs. You lose all of your formatting, but the data comes through just fine. And the great thing about Excel 2016 is that you can just double-click a comma-separated value, or CSV, file to open it. So if I double-click the DataSource comma-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 folder of the exercise files collection. From here, let's say that I want to bring in text from the DataSource text document in 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 side of the Data tab of the ribbon and click the Get External Data button and 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 file that 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 is whether the data is delimited. In other words, is there a particular type of character that is used to separate the fields and is 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 Wizard asking what the delimiting character is. In this case, it correctly guessed that we have Tab, and we'll say I don't need to treat consecutive 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 able to apply the proper format for you. In this case, General works for everything, so I will click Finish, and I will import the data to 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.
- Using operators
- Use cell references in formulas
- Formulas: SUM, AVERAGE, MIN, MAX, MEDIAN, and MODE
- Counting cells
- Summarizing cells conditionally
- Rounding cell values up and down
- Working with list data
- Finding data using VLOOKUP formulas
- Connecting to an external data source
- Cleaning up imported data
- Validating data using rules
- Tracing precedents and dependents
- Identifying errors
- Finding target values using Goal Seek