1 / 35

Exploring Microsoft Excel

Learn how to create and manage lists, import data from other applications, use database functions, and apply filters in Microsoft Excel.

lorrainescott
Download Presentation

Exploring Microsoft Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exploring Microsoft Excel Chapter 7 List and Data Management: Converting Data to Information By Robert T. Grauer Maryann Barber Exploring Microsoft Excel 2002 Chapter 7

  2. Objectives (1 of 2) • Create a list • Add, edit and delete records in an existing list • Use Text Import Wizard to import data from other applications • Describe the TODAY function and use date arithmetic • Use the Sort command Exploring Microsoft Excel 2002 Chapter 7

  3. Objectives (2 of 2) • Use the database functions: DSUM, DAVERAGE, DMAX, DMIN, and DCOUNT • Use AutoFilter and Advanced Filter • Use the Subtotals command • Use a pivot table and pivot chart; save a pivot table as a web page Exploring Microsoft Excel 2002 Chapter 7

  4. Overview • Fundamentals of list management • Display selected records • Sort the list • Use database functions, criteria range, and arithmetic • Import data from other applications • Use Excel’s data analysis tools Exploring Microsoft Excel 2002 Chapter 7

  5. List and Data Management • Data management based on lists in Excel • a list is an area of a worksheet that contains similar rows of data • Need valid input to produce valid output • verify spelling of field names and records (use the Spell Check) • remember: Garbage In, Garbage Out • Edit the list through Insert Row and Columns command and Delete command Exploring Microsoft Excel 2002 Chapter 7

  6. Database Concepts • Lists can be used as simple databases • Record is the individual information contained in a row • Field is unique information contained in a column for a record • Primary key is a unique field or combination like social security number Exploring Microsoft Excel 2002 Chapter 7

  7. Lists and Data commands • Data Form Command provides easy way to add, edit and delete records • Sort command arranges lists according to value in fields • can sort on text fields or numeric fields • can sort in ascending or descending order • can sort on up to three fields • Date Arithmetic is a powerful tool for formulas • Today() function always returns the current data Exploring Microsoft Excel 2002 Chapter 7

  8. Data Form Command Exploring Microsoft Excel 2002 Chapter 7

  9. Sort Command Sort Descending Sort Ascending Exploring Microsoft Excel 2002 Chapter 7

  10. Text Import Wizard • Converts an ASCII (text) file into an Excel workbook • Two file formats: fixed width and delimited • fixed width: each field uses the same number of positions in each record • delimited: fields are separated by a specific character, such as a comma or tab • Use the Open command • select All Files in the Files of Type list box to open the Text Import Wizard • you can also use the Data menu (Get External Data, Import Text File) Exploring Microsoft Excel 2002 Chapter 7

  11. Text Import Wizard Exploring Microsoft Excel 2002 Chapter 7

  12. Text Import Wizard Exploring Microsoft Excel 2002 Chapter 7

  13. Hands-On Exercise 1 • Objective: To use the Text Import Wizard; to add, edit, and delete records in an employee list • Text Import Wizard • Add new records • The Spell Check • Sort the list • Delete a record • Enter the hire dates • Format the hire dates Exploring Microsoft Excel 2002 Chapter 7

  14. Information versus Data • Data is simply facts • Information is data arranged in a useful format • Decisions in an organization are based on information • Database commands, functions and reports help turn data into information Exploring Microsoft Excel 2002 Chapter 7

  15. Filter commands • AutoFilter is a subset of records which meet a set of criteria • Advanced Filter allows for complex criterion and storing records in a separate worksheet area • Criteria range specifies the values to search for in records Exploring Microsoft Excel 2002 Chapter 7

  16. AutoFilter • Set criteria for fields using the drop-down list for the field • only rows meeting the criteria are displayed • other rows are hidden, not deleted • You may set criteria on multiple fields • if you set criteria on multiple fields, a row must meet all the criteria to be displayed Exploring Microsoft Excel 2002 Chapter 7

  17. Auto Filter Exploring Microsoft Excel 2002 Chapter 7

  18. Advanced Filter • Extends AutoFilter in two important ways: • create more complex criteria, such as the ability to set OR conditions (display rows that meet one of the criteria rather than all of them) • copy the rows to another section on the worksheet, leaving the original list intact Exploring Microsoft Excel 2002 Chapter 7

  19. Advanced Filter Exploring Microsoft Excel 2002 Chapter 7

  20. Understanding Criteria Ranges • Must contain at least two rows--field names and a second row of values • Same row entries imply an AND condition • Values entered in different rows meet the OR condition • Empty rows return all records • Criteria are case-insensitive • Text entries are treated as though they were followed by a wildcard (*) Exploring Microsoft Excel 2002 Chapter 7

  21. Understanding Criteria Ranges • Relational operators can be used to find a designated range • i.e. >40000 returns rows where the value is greater than 40,000 • Upper and Lower Boundaries can be established • use the same field twice in the criteria range and relational operators to set the boundaries • Equal and unequal signs select empty and nonempty records Exploring Microsoft Excel 2002 Chapter 7

  22. Criteria Ranges (1 of 2) Criteria ranges using same row imply AND Criteria Ranges using different rows imply OR Exploring Microsoft Excel 2002 Chapter 7

  23. Criteria Ranges (2 of 2) Establish upper and lower boundaries Exploring Microsoft Excel 2002 Chapter 7

  24. Database Functions • Parallel arithmetic operations of the same statistical functions • performs the operations only on rows that meet certain criteria • DSUM • DAVERAGE • DMAX • DMIN • DCOUNT Exploring Microsoft Excel 2002 Chapter 7

  25. Using Database Functions Exploring Microsoft Excel 2002 Chapter 7

  26. Subtotals Commands • Subtotals command in the Data menu computes subtotals based on data groups • list needs to be sorted on the field(s) you want to base subtotals on • Uses a summary function like SUM or AVERAGE • Outline format allows for several views of data Exploring Microsoft Excel 2002 Chapter 7

  27. Subtotals Dialog Box Exploring Microsoft Excel 2002 Chapter 7

  28. Hands-On Exercise 2 (1 of 2) • Objective: to sort a list on multiple keys; to demonstrate the AutoFilter and Advanced Filter commands; to define a named range; to use database functions • Calculate the years of service • The AutoFilter command • The Custom AutoFilter command • The Advanced Filter command • The Insert Name command • Database functions Exploring Microsoft Excel 2002 Chapter 7

  29. Hands-On Exercise 2 (2 of 2) • The DAVERAGE function • The DMAX, DMIN, DSUM & DCOUNT functions • Change the criteria • Create the subtotals • Collapse and expand the subtotals Exploring Microsoft Excel 2002 Chapter 7

  30. Pivot Tables • Pivot tables extends the capability of database functions by presenting the data in summary form • divides the records in a list into categories and computes summary statistics for those categories • can be refreshed when cells in the underlying list are added, deleted, or edited • Use PivotTable Wizard in the Data menu • Displays a Pivot Table toolbar • Can also display Pivot charts • Can be saved as Web pages with full interactivity Exploring Microsoft Excel 2002 Chapter 7

  31. Pivot Tables Dialog Box Exploring Microsoft Excel 2002 Chapter 7

  32. Pivot Chart Exploring Microsoft Excel 2002 Chapter 7

  33. Hands-On Exercise 3 • Objective: to create a pivot table and pivot chart; to create a Web page based on the pivot table • Start the Pivot Table Wizard • Complete the pivot table • Modify the list • Modify the pivot table • Change the chart type • Complete the chart • Save the pivot table as a Web page • Pivot the Web page • Change the underlying data Exploring Microsoft Excel 2002 Chapter 7

  34. Summary (1 of 2) • List contains records of data • Information is data arranged in a useful format • Dates can be used for calculations • Importing data from other applications • Filtered list displays only a subset of records Exploring Microsoft Excel 2002 Chapter 7

  35. Summary (2 of 2) • Subtotals can be inserted into a list • Database functions: DSUM, DAVERAGE, DMAX, DMIN and DCOUNT • Pivot table presents data in summary form • Pivot tables can be saved as interactive web pages Exploring Microsoft Excel 2002 Chapter 7

More Related