Join David Rivers for an in-depth discussion in this video Importing workbooks, part of Excel 2008 for Mac Essential Training.
Let's explore a very common scenario when it comes to upgrading to any software application, including Microsoft Excel. Here we are using Excel 2008 on the Mac. If we have upgraded, chances are that we have got older versions of Excel Workbooks or Spreadsheets that we have created in older versions of Excel, how do we open those up? Or, what if we are handed an Excel file that was created in an older version of Excel from somebody else and we want to open it up here in version 2008? Another scenario is, we receive a file and it was created in some other application, other than Excel, not even an older format, maybe some old version of Lotus 123 or Numbers on the Mac? Well, we are going to explore both of those scenarios right now; starting with opening up older formats, because this is very typical, if you have upgraded to Excel 2008, odds are pretty good you have got some older files that you created in a previous version.
So if you have got the exercise files, you can follow along it, if not, you can just watch and learn, no problem. We are going to go up to the File menu and just like opening up a new file here that we have created in 2008, we go to Open or command O on the keyboard. We are going to navigate to the Lesson2 folder, and you will notice that we have got one here that's a little bit different from the rest. We have got four files that we can choose from right now that are Excel files. Now, this top one has a different looking icon, and if you will look at the extension, it's the only one with xls on the end, the rest have this new extension that includes an extra x on the end, representing that XML technology that's built in.
So to open up an older format, what do we do? Well, not much, we just click on it and click the Open button. Any converting that needs to happen, happens on the way in. There is our file, looks great, and we can continue working on it. Here is the best part though. If this was handed to us by someone who is using an older version of Excel, we can make changes to this file, and when we save it, it remains an xls file, which means we can hand it back to them and they can open it up in their older version of Excel. That's great. Now, if it's just one of your own old files, when you go to save this, you might want to save it under the new extension xlsx, and it will give you more options when it comes to working with the file itself.
In the next lesson we will be talking about saving in detail. Now that other scenario is, we receive a file, maybe that was created in some other application. The thing is, when you want to share files with other people and you don't know what they are using, or even if they have got Excel for that matter, then you might want to save it to a format that anyone can open. So we are going to look at a scenario where we have received a file that was exported to a format called CSV, Comma Separated Values, but we will also look at the other files that can be imported here in Excel.
The keyword is imported. In these cases we go up to the File menu and we go down to Import. Now we are going to be asked a couple of questions before a wizard kicks in to help us import the file, but here are the four types that we can import. There is that CSV file I was talking about, its very common, stands for Comma Separated Values, meaning that the columns that we are used to seeing in a Spreadsheet are actually data values that are separated by commas now. So it's a text file that uses a Delimiter called the comma.
Now, down below we can also open up FileMaker Pro database files, HTML files, if someone has handed you that, even other Text files. So a text file that uses something like tabs or spaces to keep the column separated can also be opened. So we are going to leave it at CSV, and we are going to click the Import button. So now we got to go find it. We need to choose the file. Again, in our Lesson2 folder of the exercise files, notice there is one CSV file there, ECP_Expenses1. We give it a click, and now we click on Get data.
Notice, we are not opening it yet. When we choose Get Data, we are actually going to start our Import Wizard. So you can see the Text Import Wizard takes in, where it's Step 1 of 3, and we have got some choices to make down below. Well, at the very bottom of this window we get a Data preview, which is cool, because we get to see the actual data that's going to be brought in, and how it might look. So with Fixed width selected to describe our data, you can see the effect is kind of this mishmash of text and numbers and stuff. If we know it's Delimited, separated with those commas, and we do, we can choose Delimited, and then we will be able to choose the character, and if I look down below, you can see there are commas separating each of these values.
Notice that we have got one field here, I am going to call that a field where we have got the City and the State, and we have got a comma involved there, so we have to be careful that, that doesn't get included as a Delimiter. You will notice that this group of text in each of these rows is actually kept together between these double quotes, so keep that in mind. Now, the file origin, it can be handy to know that; did it come from a Mac, did it come from a PC? I am going to choose a Windows PC here. So if you know that information, it's best that you can select it.
It will just help make the import and the transition that much smoother. So with those selected, I am going to go on to the next step, Step 2, and this is what I like. I have got a Data preview down below of what the end result is going to be with certain selections made. Now, I know that Tabs are not being used, so I can deselect in the Delimiter section here, Tabs. It's a Comma that's being used to separate the values. Notice over here, Ventura, CA has a comma, but they are staying together, that's because of a Text qualifier. You can see it has been selected for me already here as the double quote.
Now, the double quote was probably inserted as this file was exported from whatever program it came from. So on the Export, you can choose these Text qualifiers and Delimiters, and when you create a CSV file, this is the character used by default as our Text qualifier. Notice that if I choose none, what happens down below in my preview; Ventura is by itself and CA is by itself, and we have got the double quotes at the beginning and the end of each of these. That throws off our column headings, so we definitely want to keep that qualifier selected as our double quote.
So this looks good. Now, Treat consecutive delimiters as one is another option, if there are any blanks in there, sometimes you will see a couple of Commas together with nothing in between. Well, that's okay, usually that means there is something missing or no data was entered in that particular cell. So we want keep this deselected unless you know for sure that whenever we see double commas that they should be treated as one. But I am going to leave it unselected, and move on to the next step. Here is where I get to choose the actual data format for each of these columns.
For example, this first column is a Date column. Notice the General appears by default across the top of all of them using the General Format, but this is the Date. You can see that it's actually day, then the month, then the year. The days change, but the month stays the same, and the year stays the same. So I am going to come down to Date. I am going to choose that, and I am going to change it to Day, Month, Year; look at all the formats to choose from. So it is Day, Month, Year, there we go, and it shows up now at the top of this column. Now, the rest of these are General. Notice there is no numeric format, numbers like we see here in the cost column fall under the General format.
So if I click on the Expense type column, and I hold down my Shift key and click on the last column, I can change them all here to something other than General if I wanted to, or make sure that they are all selected as General, and this is perfect. Now, there is an advanced button here as well. So I am going to go over to my Cost column here. Click the Advanced button. Notice that we can recognize numeric data by Decimals, Decimal Separator; you can see as a Decimal, Thousands is the comma. If I move this out of the way, I don't have any values in the thousands, so this will work, I can click OK, and those will be recognized as numbers when I bring it into Excel.
So I am actually done, I can click Finish now, and let's see what happens. Aha, Where do you want to put the data, in the existing sheet that we are working on right here, which is the one we just opened, the old format, or do we want to create a New sheet? I do want to create a New sheet. I am going to click OK, and you can see I have got all of my data showing up in here. Look at my Dates, they show up properly, and they are in this case Month, Day, Year. All I need to really do now is make it look good with some formatting.
Later on in this title we will be doing a lot of formatting with our sheets in a Workbook. Notice up at the top, the name of this file is now ECP_Expenses1.xls. So when we Import it, we actually import it as an xls file. So this is an older format. If I want to save it under some other format, like our newest format xlsx, then I would have to change that as I save it. So keep that in mind, when you Import or even Open older versions, you have the ability to save to those formats as well, and in fact, if you were going to be handing this back to somebody and you didn't know if they were using Excel or not, you might want to export it back to CSV.
So in the next lesson we will be looking at all of these save options.
- Customizing the user interface Using workbooks Adding and removing sheets Restricting input with validation rules Formatting workbooks Using formulas and functions Working with charts Adding, removing, and editing text Aligning and layering objects Creating PivotTable reports Sharing spreadsheets Creating custom templates
Skill Level Beginner
Q: How does one generate an average using cells in columns that are not consecutive i.e. a7,c7,e7...?
A: To get an average of non-contiguous cells, you can either select them individually, or type them in manually. Here are the steps involved:
1. Click in the cell where you want the average to appear
2. Start the function by typing: =average(
3. Now, either type the cells addresses (ie A7,C7,E7) or select each cell by clicking them while holding the Command key.
4. Close off the function with closing round bracket: ) and press Return key
You should see the answer in the cell where you entered the function. Checking the formula bar, your finished "formula" will look something like this: