Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
When you work with colleagues in other departments or other companies, they might not have access to Microsoft Excel. If that's the case, they might save their data to a text file for you to read into Excel. You can bring that data in using the Text Import Wizard. So I have opened just a blank new workbook. I haven't saved it. That's why it's called Workbook 1, and what I'm going to do is bring in the data from the outside file. To do that, you click the Data tab on the Ribbon, and then in the External Data Sources group, you click Text.
This is a CSV file, which means comma- separated values, and that is a form of a text file. So I click Text. When I do, Excel enables me to select the file I want to bring in, and in this case that file is called ImportCSV, and you'll see that it's noted that it's a comma-separated values file. And I'll click Get Data to start the Text Import Wizard. And I'll just bring it down a little bit. The Text Import Wizard allows you to bring in data of several kinds. The first kind is what's called delimited data, and that's where your data values are separated by a known character, such as a comma or a tab.
And if you look down here at the data in the Preview Pane, you'll see that indeed I do have commas between Year, Quarter, Month, and so on. A comma indicates the end of a value, which is equivalent to the end of a cell, and a carriage return, or line break here at the end indicates that's the end of a row. So this would be row 1, cell 1, cell 2, cell 3, and then row 2, cell 1, cell 2, cell 3, and so on. If you're working with an older program, and this is true of many accounting programs that were written in the 1960s and 70s but are still used, and that is where the data for each field has a fixed width.
In other words, the Year field, which is here, might have exactly 4 characters in it, because we are really short of the Year 10,000, even though we had trouble with the Year 2,000 with our programming. So the Year would always be four characters long. For Month, you would just take the longest month and make sure that the field was that width. So, for example, if it were 10 characters and you had May, which is a three letter month, then you would have M-a-y, followed by seven spaces, and that's just an idea of what a fixed width field looks like.
In this case, our data is delimited, or separated by commas, so we can keep the delimited selection. And we do want to start importing with row 1, because those will be my headers once I get them in. They're recognized as values or text right now, but they'll be headers once I get them into Excel. Everything looks good. I can click Next. And here I get to tell Excel which character delimits my data. So right now it has Tab selected, but that's not the case, so I will clear the Tab check box, and instead I will check Comma.
Now you can see that my data looks pretty good in the Preview window. Where before everything was just sort of mixed together, now we have Year, clearly in one column Quarter, clearly in its own Column, and the same for Month, Company, and Revenue. That all looks good. I can click Next. And I didn't technically have to go to the screen. I could have clicked Finish, based on the example that I've created for this exercise, but I just wanted to show you what you can do if you want to. In this case, on this screen you can assign different data formats to columns.
For example, if I wanted to indicate that this column here contained text values, I could do so, but I can do that once I get the data into Excel proper, so I won't do it here. And once again, everything looks good, so I can click Finish, and Excel displays the Import Data dialog, asking me where I want to put the data. In this case, I'll just put it in an existing sheet, which is the current sheet, and I'll put it in cell A1, which is here, and it is currently highlighted in blue, and it has a marquee around it.
If I wanted to, I could put the data onto a new sheet, so just letting you know that option is there. And I don't need to worry about changing any properties, and it looks like everything looks good, so I can click OK, and Excel brings the data into the program. Text files are the common language of the data world. If you need to import data from another program, a text file might be your best bet.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.