Intermediate Microsoft Excel 2007 Now that you have mastered the Introduction to Excel class, you are ready to tackle the intermediate class. Today we will explore importing and exporting data, simple formulas, and joining and splitting information in cells.
Importing and Exporting Data We will start with importing and exporting data. What is importing data? Data can be any file of information. Importing is how we will bring that data into Excel. For example, your faculty gives you a file that looks like a bunch of nothing and it is suppose to be a small address book.
How are we suppose use this data? It is simple. We will just need to import the data into a spreadsheet. This file is delimited or separated by commas. We can see a pattern. After every item, we can see a comma. So if there was a comma in the address, it would mess up our data import.
Exercise 1: How to import this file 1. Press the Office Button and Open then change the "All Excel Files” to All Files. 2. Go to the A: drive and select the file that has the data. In our example, it is called Intermediate Excel import file.txt that’s on the diskette. Select the Open button. 3. Select Delimited. The import wizard starts up immediately. There will be 3 steps. The first step is to tell Excel how the file is set up. You have two choices – delimited or fixed width. Delimited means that there is a character that separates the values (or fields) in the file. There are many delimiters some examples are commas, tabs, and semicolons. NOTE: a good delimiter is a character that is not within the data. If commas are used in the address, a tab or semi-colon would be a more suitable delimiter. Fixed width means that the information has set widths for each field. For example, the first name field will only have 20 characters or the last name field will only have 30 characters. If you use fixed width, Excel will chop off data that does not fit into the fixed width setting but the nice thing is that you can use any character in the data because there is no delimiter.
Intermediate Excel
- 10/6/2008
Page 1 of 14
4. You can choose to start the import from a different record or row. The default is row 1. 5. Select the Next button
6. The second step is to select the Delimiter(s). By default, Excel uses tabs. You will need to uncheck the Tab and put a checkmark next to the comma. Notice the change in the Data preview as you select the comma delimiter. If you have multiple delimiters such as comma and spaces, you may check off what you need. Please notice the difference in the preview window when you check off spaces as the delimiter. 7. Select the Next button
Intermediate Excel
- 10/6/2008
Page 2 of 14
8. The last step is to set the format for the column or field. The default is General and this is acceptable for now because you can always change the formatting of the columns after you do the import. The format selections are listed in the upper right corner of the wizard window. 9. Select the Finish button
10. You have just imported a comma delimited text file.
Now that we have the data inside of Excel, we can use it for mail merging or just to keep an address book. You may add or remove records from the spreadsheet. You can also edit the data for each record like a change in address for Therese.
Intermediate Excel
- 10/6/2008
Page 3 of 14
Exercise 2: Add more records 1. Select cell A5 2. Enter your first name 3. Hit the Tab key and enter your last name 4. Hit the Tab key and enter your address 5. Hit the Tab key and enter your city 6. Hit the Tab key and enter your state 7. Hit the Tab key and enter your zip The exercise was just to put more information into the spreadsheet. Please repeat Exercise 2 two more times. Do not worry about the data being in the incorrect field. We will work on that in the next section. Now, we have maintained this address book for months. A colleague needs to send letters to everyone in your spreadsheet. Your colleague does not know how to use Excel but they can use Word and they ask you to send them a comma delimited file. You tell them “Sure!” and then think “How do I do that?” It is just the reverse process of the import and in fact, it is even easier than the import.
Exercise 3: Export the address book 1. Press the Office Button from the menu 2. Select Save As then Other Formats 3. In the Save as type box, select the type of delimiter that you want to use for the file. In our case, we want to use CSV (Comma delimited). 4. You will need to save it under a different file name so change the information in the File Name box to my exported file. 5. The end result is a text file that has the records from our address book spreadsheet and each field is separated by commas.
Intermediate Excel
- 10/6/2008
Page 4 of 14
Cell Referencing Before we can continue, you need to know some things about cell referencing. Cell referencing is the method in which Excel names a cell. This becomes important when we use formulas or functions because we need to tell Excel what we want to calculate. For example, if we want to sum of 2 numbers in 2 different cells (cells C3 and C6), we need to tell Excel how to add those numbers together sum(c3,c6). This is a formula that will do the addition.
Exercise 4: Add cells C3 and C6 1. Select any blank cell on the Sum worksheet 2. Type in this blank cell sum(c3,c6) 3. Hit Enter 4. You should see the word sum(c3,c6) in the cell instead of a total. Why? Whenever you are writing a formula, you need start the cell with an equal sign, followed by the formula. 5. To correct this exercise, select another blank cell and type in =sum(c3,c6) and hit Enter. 6. Now you should have a total.
Intermediate Excel
- 10/6/2008
Page 5 of 14
Another important thing is that there are two kinds of cell referencing – absolute and relative. The absolute cell referencing means that you want to pinpoint only 1 cell, selection, column, or row for a formula and when a formula is copied to other cells the reference does not change. Absolute cell referencing is marked with a “$”. For example $A$20 means my formula will always use the value in cell A20 for the calculation no matter where I copy the formula. Relative cell referencing will change when you copy the formula to a different cell and the cell reference will automatically change to match the number of rows or columns the formula was copied to. This is the default cell referencing. For example, a formula in C3 states =sum(A3,B3) and we copy the formula to D3. The formula in D3 is =sum(b3,c3) because we “moved” the formula to a cell that is 1 column over. If we had copied the formula to C4, the formula would be =sum(a4,b4). We will see this in the exercises.
Exercise 5: Absolute and Relative cell referencing 1. Select the worksheet cell referencing 2. You should see a bunch of numbers. 3. We want to add the numbers in columns A and B and place the answer in column C. Select cell C1. 4. Enter =sum($A$1,$B$1) and hit Enter 5. You should see the sum of 5 and 14 which is 19 in cell C1 6. Select cell D1 7. Enter =sum(A1,B1) and hit Enter 8. You should see the same answer as Step 5 9. Absolute referencing: select cell C1 and copy (use the copy button in the toolbar or hold the CTRL-c) 10. STOP! Write down on the side of this sheet what you think the answer will be in G15 when you paste. 11. Select cell G15 12. Paste the formula in G15 (use the clipboard icon in the toolbar or hold the CTRL-v) 13. Did your answer match the result in G15? 14. STOP! Write down on the side of this sheet what you think the answer for G12 will be when you paste.
Intermediate Excel
- 10/6/2008
Page 6 of 14
15. Now select D1 and copy this cell 16. Select G12 and paste the information into the cell 17. Did your answer match?
You may copy the formulas into different cells and see what happens. Did you notice that no matter where you copied the absolute cell reference formula it displayed the same result? You should also try pasting the formula into a different worksheet and see what happens.
Exercise 6: Absolute referencing a step further 1. Select cell C1 again 2. Change the formula so that the $ is only in front of the row number. =sum(A$1,B$1) 3. You will still have the same value of 19 4. Copy cell C1 5. Select C2 and paste the information. What do you see? Please write it down here.
6. Select C3 and paste the information. What do you see? Please write it down here.
7. Select D2 and paste the information. What do you see? Please write it down here.
8. Select D3 and paste the information. What do you see? Please write it down here.
You should see a pattern here. No matter where we copy the formula in the C column, we will see the sum of A1 and B1. What happened when we copied the formula to cell D2 and D3? The column reference changed to B and to C. Our total is now 14 + 19 = 33 rather than 5 + 14 = 19. So what does this mean? When using absolute cell referencing, you need to know what you are homing in on. For example, the formula in Exercise 7 should be used if we want to always reference row 1 in any column.
Exercise 7: Absolute referencing a step further 1. Select cell C1 again 2. This time change the formula so that the $ is in front of the column names only. =sum($A1,$B1)
Intermediate Excel
- 10/6/2008
Page 7 of 14
3. You should still see 19 as the result 4. Copy the cell C1 5. Select C2 and paste the information. What do you see? Please write it down here.
6. Select C3 and paste the information. What do you see? Please write it down here.
7. Select D2 and paste the information. What do you see? Please write it down here.
8. Select D3 and paste the information. What do you see? Please write it down here.
9. Select E2 and paste the information. What do you see? Please write it down here.
10. Select E3 and paste the information. What do you see? Please write it down here.
You should see a pattern here too. No matter where we copy the formula in the D or E columns, we will see the sum of A and B for the appropriate row number such as row 3. We see the total for D3 is 10 + 43 = 53. We also see that the total for cell E3 is also 10 + 43 = 53. What happened when we copied the formula to column C? The row references changed to the row that you have selected. Our total is now 6 + 52 = 58 for cell C2 and cell C3 is 10 + 43 = 53 rather than 5 + 14 = 19. So what does this mean? The formula in Exercise 8 should be used if we want to always reference a column(s) in any row.
Rearranging data Let’s take a look at the imported spreadsheet again. We notice that the titles “Dr.” and “Count” are in the first name field and the first name is in the last name field and so on. We notice that either we need to move the title field to another column or we need to shift the other records one column over so that the first field in all records is the person’s title. There is no correct way. You need to look at what you want to do and how you will enter the information. So do whatever is convenient for you. Shifting cells are covered in the Introduction class. Please see me after class if you would like the handout for the Introduction to Excel class for the how shift cells.
This is what we have. You will have this spreadsheet in Sheet2.
Intermediate Excel
- 10/6/2008
Page 8 of 14
We are going to use the address book. We find out that we need to separate the buildings and room numbers out from the address line and we need to join the City, State, and Zip fields into a single field. This would not be a problem if we only had 4 records. What if we had hundreds of records? Deep breath! Excel to the rescue! Let’s look at the separating of the building and room from the address field.
Exercise 8: Separating values out The key to this exercise is to be observant and tricky. We look at the data in the Address field. We see that only one of the records have a building and room number. Yes, it would be easy to just remove the “Keller 201” from the address field and re-type it in the cell H2. Some times you will have to do it that way but we are going to do it the hard way. 1. We are going to use a number of columns as temporary storage areas. So we are going to use the columns after H. Select column D. 2. Select Home tab from the menu 3. Select 4. Select column H
5. Select Paste 6. Select Data tab from the menu 7. Select Text to Columns 8. The wizard should be familiar. Yes, it is the same wizard from the importing a file. You will follow the same steps as importing a file so I will not go over it again. We want to separate building and room number from the address. Are there any delimiters? Is it a fixed width? Please write your answer down.
9. The only delimiter we can see is space. What is the problem with space as a delimiter? Please write your answer here. 10. Select Delimited. 11. Select space. We will have the street number in the first column, the street name in the second column, etc.
Intermediate Excel
- 10/6/2008
Page 9 of 14
12. Select Next and then Finish
We now have the information separated out to each column. This would be okay if we wanted the street number separated from the street name. Unfortunately, this is not useful to us. Is it? Let’s remember our task of separating the building and room numbers from the address data. We did that but it is still not in a form that we want or can use. We now need to join the pieces of data that needs to be together into the appropriate cell. We need to join column H and column I and column J together. We also need column K and L joined together. Please note that this will only work if the data is uniform. If the street name had more spaces in it, the general formula will not work.
Exercise 9: Joining the data 1. Select cell M2 2. Enter this formula =concatenate(H2,I2,J2) 3. Hit Enter 4. What do you see? The concatenate function is an Excel function that joins strings or data together. You should see “2565McCarthyMall” 5. There are no spaces. We can fix that. Select M2 again 6. Place the cursor between the value of H2 and the comma. 7. Enter ,” “
(Put a space between the quotation marks)
8. Hit Enter 9. Look at M2 again. There should be a space between the 2565 and McCarthyMall. 10. Select M2 again 11. Place the cursor between the value of I2 and the comma. 12. Enter ,” “
(Put a space between the quotation marks)
13. Hit Enter 14. You should see a space between McCarthy and Mall 15. Select N2
Intermediate Excel
- 10/6/2008
Page 10 of 14
16. Repeat the steps to join columns K and L
Using simple formulas Excel has a wide variety of simple functions that you can use within your worksheets. We will not have enough time to cover all of them.
Exercise 10: Use the formula wizard 1. Select any blank cell on the Sum worksheet 2. Look in the toolbar for the function icon
and press it
3. All the formulas available to Excel are in this window. You can find out more information about each of the functions by selecting them in the top windows and reading the information at the bottom of the window. 4. Select Statistical and select Average 5. Type in C3:C8 and select OK. 6. Excel will give you the average for the selection.
Let’s say that you have a typical balance sheet for your checkbook. You will have date, description, check number, amount for transaction, and the running balance. If you look at the sample worksheet called checkbook, you have no running balance and you do not know which ones are debits or credits. Look at the dates it is all messed up and in different formats.
How to set up your balance sheet Exercise 11: Format the date column
Intermediate Excel
- 10/6/2008
Page 11 of 14
1. Select column A 2. Select Home tab in the menu 3. In Cells select Format and then Format Cells… 4. Select the Number Tab 5. Select Date 6. Select the date format that you like. I like the mm/dd/yy format. 7. Select OK.
Exercise 12: Format the money columns 1. Select columns D and E 2. In Cells select Format and then Format Cells… 3. Select the Number Tab 4. Select Currency 5. Select the number format you like. I like to show the negative numbers in () and in red. At this point, you need to go back and put minus signs in front of the debit numbers in Column D.
Intermediate Excel
- 10/6/2008
Page 12 of 14
Exercise 13: Add the formulas for a running total 1. Select cell E2 2. Type in =D2 and hit Enter. This will just copy the amount that was carried over from the last checkbook. 3. Select cell E3 4. Type in =sum(E2,D3) and hit Enter. This will add the numbers because you have negative amounts in column D. It is the same as taking the previous total and subtracting the debit amount. 5. Once you have made sure the result from the previous step is correct, select E3 again. Look at the black highlight around the cell. There is a black square in the lower right corner of the cell. Click and drag that square downward.
Intermediate Excel
- 10/6/2008
Page 13 of 14
6. You are creating a series for the formula. You will notice that the formula will increase the row numbers as you drag the square downward. You have used relative cell referencing. Relative cell referencing will depend on some type of relationship with another cell or reference. When we drag the black square, it increments the row and column references.
You have passed Intermediate Excel 2002.
Intermediate Excel
- 10/6/2008
Page 14 of 14
