{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Ch5ClassNotes_Part1_sp10

# Ch5ClassNotes_Part1_sp10 - C hapter 5"List Management...

This preview shows pages 1–4. Sign up to view the full content.

Chapter 5: "List Management" (Managing Data in Excel) Part 1 of 2 (The material on 3-D formulas that was presented March 16 is included in this part.) Chapter 5, Part 1 Skills Understand and apply the critical guidelines for list development that enable the automatic Excel list features to work correctly. Write formulas that involve multiple worksheets and/or multiple workbooks. Import delimited data into Excel. Use formulas and functions to "clean" or "scrub" imported data. Move or copy worksheets from one workbook to another. Use Web queries to import dynamic Web data into Excel. Import static Web data into Excel. Chapter Vocabulary Excel "list" (of data) a record (row) an attribute (column) field (2 meanings) delimited file formats and common delimiters "3-D" formulas "cleaning" or "scrubbing" imported data Web Query Web table literal (text or numeric) value Introduction to List Management Critical List Guidelines: 1

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
1. column labels should be in the first row of the list and should be formatted differently to distinguish it from the data rows of the list 2. there should be now blank columns or rows within the list 3. there should be a blank row and column around the list unless the list boundary is the edge of the worksheet Chapter Demonstration 2: Importing Delimited Formats into Excel A. Import a comma-delimited file into Excel. 1. Open the file Animal.csv file in Notepad (Right-click on the file Open With Notepad). Note that commas are separating the fields of each row of data. A carriage return indicates the end of a row of data. Close the Notepad file. 2. Open the Animal.csv file in Excel in one of the following ways: a. Open Excel, then open the file from inside Excel. b. Right click on the file, choose Open with Microsoft Office Excel c. Double click on the file. (When Excel is available, it is the default application for opening a .csv file.) 3. Improve the Animal list in Excel by doing the following: a. Make sure the values in the DateBorn column are recognized by Excel as dates (they may already be). If needed, select the column and apply a date format. Re-format that column header to General or Text format. b. Format the entire column header row to distinguish it from the data rows. c. Auto-size all the columns at once to fit the data. 2
4. If the worksheet name does not appear as Animal , rename the worksheet to that name. 5. Save the workbook with an Excel file name of your choice. Use the default Excel file extension since this file does not need to support macros. B. Importing special number fields as text data type 1. Open the Customer.csv file in Excel as you did the previous file. What happened to the zip codes? 2. Without saving, close the Customer workbook you just imported. Now try opening the file using the Text Import Wizard in Excel to import it into the same workbook as the Animal data from the previous exercise.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern